Non repeating, random entry MySql Querys ?!?!?

[PHP Learner]

Hello coders,

 

I'm using this code to grab random results from a table and it works well but I need it to be a non-repeating event.

 

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `jobs_board` ");
$offset_row = mysql_fetch_object( $offset_result );
$offset = $offset_row->offset;

 

I'm using this query followed by the html which is a set of divs with inline echo statements for the various variables I've localised from the table. Then I'm repeating that process a number of times to create a small list (I wanted to use a single while loop but cant figure it out at the moment).  Anways, how can I make it so each query is random but also not repeated? At the moment they are indeed random but sometimes one same entry will appear a number of times consecutively...

 

Nice one guys, L-plate

[voip03]

You only have to look at the total number of records in the table, and them do a rand() between 0 and the resulting number of records.

So if you add more records, the rand() funtion will include them, and so on.

 

// $Limit =  total number of records ;
SELECT * FROM table_name ORDER BY RAND() LIMIT $Limit ;

[voip03]

For your reading

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

[PHP Learner]

Thanks very much, but, the guy that wrote the above code saysabout the rand()..

 

"The problem with this method is that it is very slow. The reason for it being so slow is that MySQL creates a temporary table with all the result rows and assigns each one of them a random sorting index."

 

My table is gonna become massive, with thousands of entries so I chose this method to overcome snags in the future. Not sure if this is correct but others who commented on the page seem to agree.  Is there anyway of adapting the code to counter repeating entries? Or something you can do with PHP?

 

Cheers, pLate

[voip03]

You'll need to use some basic offset / limit with a random number type scheme.

[PHP Learner]

Sounds great Voip mate!... erm.. How do you do that?

[xyph]

If efficiency is key here, you're going to end up in a complex mess of a query, with possible sub-tables for sorting and triggers to keep it all organized

 

http://jan.kneschke.de/projects/mysql/order-by-rand/

[xyph]

Here's another good read that is a little slower, but offers solutions fast enough for your implementations

 

http://www.warpconduit.net/2011/03/23/selecting-a-random-record-using-mysql-benchmark-results/