Grab all items from MYSQL Table expired 1 day ago

MYSQL Table trial_list structure as follows...

id | product_id | expiry_date(date) | by_user | curr_datentime(timestamp)

we are able to extend any trial, and if we do that it simply another row with new expiry_date. Now we would like to get rows got expired yesterday, we are currently using following sql query.....

Sample MYSQL DATASET

+----+-------------+-------------+-------------+----------+---------------------+
| id | product_id  | comment     | expiry_date | by_user  | dnt                 |
+----+-------------+-------------+-------------+----------+---------------------+
| 2  | 50          | testing     | 2011-02-18  | tester   | 2011-02-17 23:36:12 |
+----+-------------+-------------+-------------+----------+---------------------+
| 3  | 50          | again       | 2011-02-20  | tester   | 2011-02-19 20:36:12 |
+----+-------------+-------------+-------------+----------+---开发者_运维知识库------------------+
| 4  | 50          | extend      | 2011-02-23  | tester   | 2011-02-21 22:36:12 |
+----+-------------+-------------+-------------+----------+---------------------+


$sql =   'SELECT id, product_id, expiry_date, by_user, curr_datentime FROM trial_list WHERE expiry_date < CURDATE() ORDER BY expiry_date DESC';

We believe this is not correct as its getting all rows which date is older than yesterday not updated expiry_date, suppose we have given some user expiry date 1st feb 2011 and then we change again with 12th feb 2011, so it selects 1st feb 2011 entry. I think it makes sense.

What you have to do first is get the latest item per product_id. After that you can further filter it down to those which are expired. Something like:

SELECT a.* FROM
trial_list AS a
LEFT JOIN trial_list AS b ON a.product_id = b.product_id AND a.id < b.id
WHERE b.product_id IS NULL
AND a.expiry_date < curdate()

See http://dev.mysql.com/doc/refman/5.5/en/example-maximum-row.html

Try using NOW() instead of CURDATE(), you are comparing a Date to a Timestamp, NOW() will compare timestamps.