Paginating random select query results

[baldeagle]

Hi all, hope someone out there has a solution or some ideas on how I can resolve this problem.

 

I am just starting with PHP/MySQL and using Dreamweaver MX 2004.

I am selecting a subset of records from a table, then ordering them using RAND() and then displaying 6 at a time on a page. So far so good but there is one major problem, namely, when you move from page to page viewing the results the query is rerun for each page which means that you end up duplicating results. What I want to be able to do is run the query once and then page through the returned results without duplication. Also to be able to go back & forth between pages and see the same set of results.

 

Here's a simplified example: Suppose I have 12 records (call them A - L).

The query : SELECT * FROM Table WHERE Criteria = 1 ORDER BY RAND()

Which returns: D F K A L F J H G C B I

So my pages should show the following:

Page 1: D F K A L F

Page 2: J H G C B I

And I should be able to switch back and forth between these pages and always see exactly the same records each time.

However what I see at the moment is:

Page 1: D F K A L F

Page 2: F B C A E D (Note duplicates from Page 1 because query has been re-run)

Page 1: C B F A K L (Note duplicates again plus results not the same as previous visit to Page 1)

 

Grateful for any ideas/help with this one. All my searches have been unsuccessful so maybe you are the guys to help.

Thanks in advance.