script for using limit with a random search result

phpbuilderfan

does anyone have a script example, or seen a script example where one can draw random images from a mysql database using code:

select * from table order by rand()

Then after the result set is created, use limit to page through the results.

The rub is, when you limit the results, you want to keep each block for each page the same. I find when I do this, it orders by rand() each time, and it doesnt bring up a set result set.

I was thinking of putting it in an array or using session handling to remember the first block of id's created.

Does someone have a better idea, and can show me what they would do?

Many thanks in advance

AstroTeg

Rand() is a function that returns a numeric value between 0 and 1.0. It doesn't make sense using it in the ORBER BY clause. The ORDER BY clause is looking for a field name to sort on. I doubt your fields are decimal values.

I'm trying to think of an easy way to do this... I'll make another post with some thoughts.

AstroTeg

Um... One thought would be to:

Use shuffle() (PHP function). Pull all records out and use shuffle() to mix their order up. Trick is you'll need to remember this order so you can flip pages.
Temp table approach. I've heard of someone describing this approach. Basically, you dump all your results in a temp table and then randomly assign a number to an additional column (or randomly assign it as you dump the results to the temp table). Then ORDER BY this column of random numbers.

I haven't used the shuffle() function so I'm not sure all the tweaking that would be required to make it work. I think I've done the temp table approach before and its pretty easy to code and manage.

You're trick is you're going to have to remember the order these got sorted in as you go to the next page. Otherwise its possible you'll show duplicates as you keep randomizing the results (you just need to randomize the order once).

leatherback

A thought..

Once you have shuffles the record_id's you could consider storing it in an session: Implode the number array with a | or something, and then store it in the session?

AstroTeg

I was thinking session too...