Random order List

[federicog]

I have to create a listing in random order from a few mysql tables.

I can't use ORDER BY RAND() for a few reasons:

- The random order has to change only every 24hs, ie: today it will show rows number 4 2 3 1 and tomorrow 2 3 1 4, but it should be the same all day long.

- I'm dividing the results in pages using LIMIT x, y.

I suppose I would have to save the random order in a mysql table, but how can I order the results based on that table?

Any ideas?

[kovik]

The only way to (consistently) order randomly in MySQL is ORDER BY RAND().

For your situation, you'll want to random select the rows, then save them for that particular day. For example:

create table `random` (`date` date, `id` int, primary key(`date`));

$query = "SELECT `column` FROM `table` LEFT JOIN (`random`) ON (`random`.`id` = `table`.`id`) WHERE `date` = '" . date('Y-m-d') . "';";
$result = mysql_query($query);
 
if(!mysql_num_rows($result))
{
    mysql_query("DELETE FROM `random`;");
    $result = mysql_query("SELECT `id` FROM `table` ORDER BY `rand` LIMIT 10;");
    while($data = mysql_fetch_object($result))
    {
        mysql_query("INSERT INTO `random` SET `id` = " . (int)$data->id . ", `date` = '" . date('Y-m-d') . "';");
    }
 
    $result = mysql_query($query);
}

This isn't tested at all, but in theory, it should be fine. It's not exactly the kind of code that I'd use because I like to condense these basic queries into much more complex queries, but for readability's sake, I've simplified it.

[federicog]

Thanks a lot!

That's what I was thinking of doing, but I thought there may be some way to do it without storing it in another table.

I'll test it next week and come back if anything goes wrong :P

Thanks again!