php or mysql solution for ordering results at random but retaining sequence

[JKG]

the title sounds quite ridiculous but i dont know how else to put it...

 

i want to return results, but my site has got quite popular, quicker than i would have thought. i want to paginate the results.

 

but i want the results to appear at random.

 

but the results should not appear twice...

 

can this even be done? any questions...?

 

thanks!

[AbraCadaver]

Well, I've never done this and ORDER BY RAND() seems to be a bad idea according to many, but as for the not repeating I would store the used one in the session.  First initialize the id array in the session and as you loop through and display your first set of random rows, add the id in the session.  On the next pages use that array as a list in a NOT IN clause.  This is not put together properly with pagination etc, just an example snippet.  You can combine this to use the same code and check for the $_GET['page'] or whatever that you use for pagination:

 

First results:

session_start();
$_SESSION['displayed_ids'] = array();
$result = mysql_query('SELECT * FROM table ORDER BY RAND() LIMIT 10');
while($row = mysql_fetch_assoc($result)) {
   echo $row['stuff'];
   $_SESSION['displayed_ids'][] = $row['id'];
}

Next results:

$displayed_ids = implode(',',  $_SESSION['displayed_ids']);
$result = mysql_query('SELECT * FROM table WHERE id NOT IN (' .$displayed_ids .') ORDER BY RAND() LIMIT 10');
while($row = mysql_fetch_assoc($result)) {
   echo $row['stuff'];
   $_SESSION['displayed_ids'][] = $row['id'];
}

This might be really slow on large tables.

 

 

[cs.punk]

Yes it can be done (like most things).

 

You can just add 'ORDER BY RAND()' in your mysql query... But it's very resource hungry with larger tables.

Here's an more efficient way though: http://www.electrictoolbox.com/msyql-alternative-order-by-rand/

 

However this only gives you one 'result'. Perhaps if you added each random result's ID and then did 'WHERE id != x or x or x or x or x or x...'

 

 

Hope that helps a bit.

[JKG]

using a combination of the above, it would be pretty easy then, instead of a pagination method, to just have a button which says '10 more' and load another ten results not already displayed...

 

thanks for your help guys.