Aritra Paul Aritra Paul - 3 years ago 107
MySQL Question

Date comparison from the database with the current date

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>

Answer Source

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().

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download