Query returns wrong rows
Hi everybody, my name is James. Long time lurker, first time poster, I try to visit the site daily and have learned lots from it...
Anyway, I have a php script that querys a MySQL database and returns a list of current events, I'm trying to only return events that are > or = to the current date. But it returns rows that have a previous date.
Here are the relevent parts of the code.
Thanks for any help you can provide.
$events = @mysql_query("SELECT * FROM Events, Authors WHERE $curdate<=Events.Event_Date AND Events.Author_ID=Authors.AID ORDER BY Events.Event_Date ASC LIMIT 10");
Hi, James. Glad to see you decided to emerge from lurkdom. :D
I'm sure you whole problem is with the way you're querying the date. The question is, what kind of date format is being stored in your database? That will dictate just how to setup your query and get the kind of results you're seeking.
Hope this helps.
Right now the Event_Date column is MySQL date type, and the date is inserted in the format of Y-m-d.
For some reason when I output the results of the query showing an unmodified date (change of format to d/m/Y) it shows me seconds (like the time function will). I don't know if this has anything to do with it, I'm still pretty new to PHP and MySQL.
Hi James. Rather than assigning a variable in PHP to the current time, you could just use the MySQL functions NOW() or CURDATE(). For more info see section 13.5 of the MySQL manual.
Your query could be changed to:
SELECT * FROM Events, Authors WHERE Events.Event_Date >= NOW() AND Events.Author_ID=Authors.AID ORDER BY Events.Event_Date ASC LIMIT 10
Hope this helps.
would this work?
$curdate = date('Y-m-d');
$sql = 'SELECT * FROM Events, Authors WHERE ( ( Events.Event_Date <= "' . $curdate . '" ) AND ( Events.Author_ID = Authors.AID ) ) ORDER BY Events.Event_date ASC LIMIT 0,10';
$result = @mysql_query($sql, $conn_id);
Thanks for all your quick responses guys!
I used djones's suggestion of the NOW() function from MySQL and it worked.
Again, thanks much ya'll.