Need to get the mysql query to show the next row every day.

Question

I've set up a query to return notes for every day. now only the first row (id=1) shows. I'd like visitors to the site to see the next row on the next day, and so forth.

Answer 1

You should add a note_date column. Then select on that

SELECT note, note_date FROM notes WHERE note_date = CURDATE();

Note that CURDATE() is the date of the OS. So, if your server is on IST time, you'll be wondering...

Answer 2

Assuming the first time they'll always see row 1, and then the next day row 2, and then 3, etc, I would do something like this:

In your Users table have lastIdSeen and lastIdSeenTime (terrible names, but you can change them to whatever). Then have lastIdSeen default to 0 and lastIdSeenTime default to CURRENT_TIMESTAMP.

Then when a user views a page:

if(lastIdSeenTime is the previous day)
{
    Increment lastIdSeen
    Update lastIdSeenTime
}
Get row based on lastIdSeen and display