comparing sql date to php date

I had a similar problem and these are the steps I took to resolve:

1. Change the SQL field type to datetime.
2. In php use $date = date('Y-m-d H:i:s); to get the current date and time
3. update your query as SELECT food FROM fridge WHERE date_purchased <= '$date' (change your double qoutes to single qoute).

Try this:

SELECT * FROM tbl1 WHERE user_id = "User1" and date_purchased < "2016-01-01";

If you have a php datetime variable, use format to create a string:

$date->format('Y-m-d');

In a context this could look like this:

$date_str = $date->format('Y-m-d');
$sql_pattern = 'SELECT * FROM tbl1 WHERE user_id = "%s" and date_purchased < "%s";';
$sql = sprintf($sql_pattern, $user_name, $date_str);

Everything seems to be fine having in mind only the information you gave.

We would have to see the entire code to find the error. Maybe it's a MySQL field type problem, or maybe it's about the way you are sending the query to MySQL.

I have a working example here with a minimal version of your table. I hope it helps!

Fridge table fields:

mysql> desc fridge;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| id             | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| food           | varchar(20)      | YES  |     | NULL    |                |
| date_purchased | date             | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+

(Note the date type in the date_purchased field).

Edit: this example works if you use either date or datetime type for that field.

Fridge table content:

mysql> select * from fridge;
+----+-----------+----------------+
| id | food      | date_purchased |
+----+-----------+----------------+
|  1 | hamburger | 2016-04-28     |
|  2 | pizza     | 2016-04-12     |
|  3 | salad     | 2016-05-10     |  <-- future date
|  4 | fruit     | 2016-05-04     |  <-- future date
+----+-----------+----------------+

PHP code (using statements to query the database):

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// prepare and bind
$stmt = $conn->prepare("select food from fridge where date_purchased < ?");
$stmt->bind_param("s", $date);

// set parameters and execute
$date = date("Y-m-d");  // NOTE: $date stores the current date
echo "<p>Food with date < $date</p>";
$stmt->execute();

// bind variables to prepared statement
$stmt->bind_result($food);

// fetch values (here you can do whatever you want with results)
while ($stmt->fetch()) {
    echo "<p>$food</p>";
}

$stmt->close();
$conn->close();

Results (we don't want healthy food right now...):

Food with date < 2016-05-01

hamburger

pizza

I had the same problem and what seemed to work for me is the above (PHP code):

$cur_dt = date('Y-m-d');

          $sql = "SELECT date, text FROM rantevou WHERE user_id = '$id' ";
          $result = mysqli_query($link, $sql);

          if (mysqli_num_rows($result) > 0) {

            while($row = mysqli_fetch_assoc($result)){
              $date = $row["date"];
              if ($date <= $cur_dt){
                
              }
              
            }
          }

I also tried using convert function in sql query with no result.