New records since last query in MySQL View

Question

I am looking for a way to create a View that when queried will automatically only retrieve new records since the last query. My tables have a timestamp field for all entries, so for a simple example I can

SELECT * WHERE timestamp >= 'blah'

but I don't know how to determine what blah should be from the last query. So if the View was queried at 11:00 and then again at 12:00, the query at 12:00 should only return records added since 11:00. And so on... This all needs to be accomplished in the View, the end user should simply be able to query the View and get the results.

Is this possible?

Answer 1

There are two ways:

1. Store last access date time in database per user persistent session table, if you have one. On next view call to database, use the previous latest access time in the session to filter rows starting from.

2. Store last access date time in user virtual session at client environment. On every call to server, send last access date time as well. So that server uses it to filter rows starting from.

I prefer to use second option that process won't write any data in database tables.

Answer 2

As there may be an unread record that slips through undetected (say it came less than a second since the last one accessed, so it has the same timestamp), set a column to auto increment (typically labelled id) and check for entries using it e.g. in PHP save the last accessed record in a $lastId variable, and use:

$sql="SELECT * WHERE `id` > '$lastId'";