Selecting 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.

[benphelps]

I'm 100% sure there is a better way to do this, I just don't know how.

 

$array = shuffle(mysql_fetch_array(mysql_query("SELECT * FROM table")));
$save = serialize($array);
$open = unserialize($save);

 

You could save the array in a database and tag it to the session for the user, that way the user has the same, but random, results. 

 

You can treat the array you get from $open just like you would if it was never changed.  The only thing you loose the the "LIMIT" from the SQL, which is how most pagination scripts work if I'm not mistaken.

[sasa]

you can use mysql function RAND() with seed in ORDER BY part

[roopurt18]

If there are many rows in the database, then benphelps approach will be inefficient and essentially defeat the purpose of pagination.  Also, his syntax is wrong.

 

I believe sasa is saying to use the same seed every time you run the query, which will produce the same random values.  You just have to track it in the user's session then and that may work.

 

I don't know if MySQL supports them, but you may be able to accomplish this with a cursor.

[erme]

Hmm, could be more hassle then its worth