Date comparison from the database with the current date

Question

I have stored my date in database in d/m/y format. How can I compare that with current date? here is my code. But its not working properly.

<!DOCTYPE html>
<html>
<body>
 <?php
 session_start();
include_once 'dbconnect.php';

date_default_timezone_set("Asia/Dhaka");
$t=time();
 $d=date("d/m/Y");
 $bar=date("l");
 $time=date("h:i:sa");

 $result=mysql_query("SELECT * FROM seminar WHERE date >= '$d'");
 $cnt=mysql_query("SELECT COUNT(*) FROM seminar WHERE date >= '$d'");
 $find=mysql_fetch_row($cnt);
 $num=$find[0];
 while($uRow=mysql_fetch_assoc($result))
{
   echo "A Seminar on ".$uRow['about']." will be held in ".$uRow['place'] ."      at".$uRow['time'].",".$uRow['date']."<br><br>";
}
 //mysql_query("DLETE FROM seminar WHERE date < '$d'");
  ?>
  </body>
  </html>

Hello and welcome to Stack Overflow! Please add more details - that something "is not working" isn't very helpful information. Does it give any errors? What does it do? What should it do? — Anders, Sep 08 '15 at 17:20
can you provide a stored date in DB that we can see its format, you wrote DB form d/m/y while comparing d/m/Y * capital Y — alamnaryab, Sep 08 '15 at 17:21
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Sep 08 '15 at 17:34

Anders · Accepted Answer · 2015-09-08T17:30:11.053

You do not need to get the date in PHP, you can just use SQL:

SELECT * FROM seminar WHERE date >= CURDATE()

Read more here.

If your dates are stored as strings (as your question seems to imply) and not as dates, you should consider storing them as dates instead. If that for some reason is not an option you could do this:

SELECT * FROM seminar WHERE STR_TO_DATE(date, '%d/%m/%Y') >= CURDATE()

Also, you should not use the mysql_* functions as they are deprecated and vulnerable to SQL injection attacks. Use MySQLi or PDO instead.

Also, there is no need for an extra query to get the count. Just check how many rows that were affected by the first query. If you use MySQLi, use mysqli_affected_rows().

score 1 · Answer 2 · answered Sep 08 '15 at 21:27

 <!DOCTYPE html>
    <html>
    <body>
     <?php
     session_start();
    include_once 'dbconnect.php';

    date_default_timezone_set("Asia/Dhaka");
    $t=time();
     $d=date("d/m/Y");
     $bar=date("l");
     $time=date("h:i:sa");

     $result=mysql_query("SELECT * FROM seminar WHERE DATE_FORMAT( `date` , '%d-%m-%Y' ) <= '$d'");
     $cnt=mysql_query("SELECT COUNT(*) FROM seminar WHERE DATE_FORMAT( `date` , '%d-%m-%Y' ) >= '$d'");
     $find=mysql_fetch_row($cnt);
     $num=$find[0];
     while($uRow=mysql_fetch_assoc($result))
    {
       echo "A Seminar on ".$uRow['about']." will be held in ".$uRow['place'] ."      at".$uRow['time'].",".$uRow['date']."<br><br>";
    }
     //mysql_query("DLETE FROM seminar WHERE date < '$d'");
      ?>
    </body>
    </html>

score 0 · Answer 3 · answered Sep 08 '15 at 17:23

Its because that date format ('01/01/2015' smaller than '31/01/1970')

I cant check now, but this must work:

$d=date("Y-m-d");
$result=mysql_query("SELECT * FROM seminar WHERE DATE_FORMAT(date,'%Y-%m-%d') >= '$d'");
$cnt=mysql_query("SELECT COUNT(*) FROM seminar WHERE DATE_FORMAT(date,'%Y-%m-%d') >= '$d'");

Date comparison from the database with the current date

3 Answers3