CODEWITHSUNDEEP
sqlpaginationreal-time
paganotti
paganotti

Reputation: 5661

SQL - mantain sort order for paginating data when change in real time

I'm implementing a php page that display data in pagination format. My problem is that these data change in real time, so when user request next page, I submit last id and query is executed for retrieve more 10 rows from last id order by a column that value change in real time. For example I have 20 rows:

Id 1    col_real_time 5
Id 2    col_real_time 3 
Id 3    col_real_time 11

Etc

I get data sorted by col_real_time in ascending order, so result is

id 2, id 1, id 3

Now in realtime id 2 change in col_real_time 29 before user send request for next page, user now send request for next results and because id 2 now is 29 he already see it.

How can I do?

Now in realtime id 2 change

Upvotes: 1

Views: 883

Answers (2)

Nathan
Nathan

Reputation: 2775

If you have just a few pages, you could:

  1. Save it to session and page over it, instead of going back to the database server.

  2. Save it to a JSON object list and use Jquery to read it and page over it.

  3. Save it to a temp table indicating generation timestamp, user_id and session_id, and page over it.

Upvotes: 2

monitorjbl
monitorjbl

Reputation: 4350

You basically have to take a snapshot of the data if you don't want the data to appear to change to the user. This isn't something that you can do very efficiently in SQL, so I'd recommend downloading the entire result set into a PHP session variable that can be persisted across pages. That way, you can just get rows on demand. There are Javascript widgets that will effectively do the same thing, but will send the entire result set to the client which is a bad idea if you have a lot of data.

This is not as easy to do as pure SQL pagination, as you will have to take responsibility for cleaning the stored var out when it's no longer needed. Otherwise, you'll rather quickly run out of memory.

Upvotes: 2

Related Questions