Select random records and holding for pagination

[erme]

Hi,

 

I'm building a business directory. When a user searches for a business I want the results to display randomly, as it would be unfair to order by date or alphabetically.

 

This is simple enough. Except I need to be able to save the random selection for pagination. This way there is no repeat of records and the user can view all the records available.

 

Is this possible? And if so, can anyone help with some strategies?

 

Thanks.

[premiso]

The only ways I could think of this being possible is to

 

A: create a table for searches which holds the id's of that particular search in order they were selected. So basically when the search is initially done a new record is inserted into that table (the searchid can be appended using GET or session) then just access that table for the next set of ID's to query.

 

B: Store the id's in session instead of in a table.

 

The advantage of storing them in a search table, is you can say "recently searched" and display those results for SEO stuff. The upside to the session, a lot less work as all you need is one session variable with searchedids stored in it and just access them.

 

At least thats the theory behind them, good luck.

[sasa]

you can order by random with seed

see mysql manual http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

[erme]

This is the solution I got working for anyone wanting to know:

 

Generate a random number in PHP, store it in a session variable, and finally use it in the MySQL query inside the RAND parenthesis. Therefore the random number used every time is the same for the visitor session, and the global order will stay the same in the different pages.

 

The PHP code to generate and store the random number:

 

$rand = $_SESSION['rand'];
if (empty($rand)) {
  srand((float)microtime()*1000000);
  $rand = rand();
  $_SESSION['rand'] = $rand;
}

 

Open the session with session_start() at the top of the PHP script.

 

Finally the MySQL query becomes something like this:

 

SELECT * FROM user LIMIT 20,10 ORDER BY RAND($rand)

[sasa]

bravo