473,287 Members | 1,880 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Random order List

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?
Jul 27 '07 #1
2 2547
kovik
1,044 Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1. create table `random` (`date` date, `id` int, primary key(`date`));
Expand|Select|Wrap|Line Numbers
  1. $query = "SELECT `column` FROM `table` LEFT JOIN (`random`) ON (`random`.`id` = `table`.`id`) WHERE `date` = '" . date('Y-m-d') . "';";
  2. $result = mysql_query($query);
  3.  
  4. if(!mysql_num_rows($result))
  5. {
  6.     mysql_query("DELETE FROM `random`;");
  7.     $result = mysql_query("SELECT `id` FROM `table` ORDER BY `rand` LIMIT 10;");
  8.     while($data = mysql_fetch_object($result))
  9.     {
  10.         mysql_query("INSERT INTO `random` SET `id` = " . (int)$data->id . ", `date` = '" . date('Y-m-d') . "';");
  11.     }
  12.  
  13.     $result = mysql_query($query);
  14. }
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.
Jul 27 '07 #2
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!
Jul 27 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Rahul Anand | last post by:
Hi all, I am trying to fetch 5 records, randomly picked and in random order from a MySQL table (MySQL version > 3.23). I wrote my SQL Query as SELECT name,id FROM tablename WHERE active =...
4
by: Frank & Janny Plaza | last post by:
I am trying to write a program in which the users will each enter their names and when all names have been entered, I want to randomly sort this list of names several times and then show the order in...
4
by: Bart Nessux | last post by:
New to Python... trying to figure out how to count the objects in a list and then map the count to the objects or convert the list to a dict... I think the latter would be better as I need a number...
7
by: Jean-Francois.Doyon | last post by:
Hello, I'm trying to retrieve a limited number of random rows, and order them by a column, and am not having any luck with that last part: SELECT * FROM tablename ORDER BY random(), id LIMIT...
22
by: Nhmiller | last post by:
Is there a way to do this? Thanks. Neil Cat Paintings At Carol Wilson Gallery http://www.carolwilsongallery.com
70
by: Ben Pfaff | last post by:
One issue that comes up fairly often around here is the poor quality of the pseudo-random number generators supplied with many C implementations. As a result, we have to recommend things like...
9
by: gl | last post by:
How do I take an array or arraylist, and sort it randomly? Like suppose the items in it are (1,2,3,4,5) and I want to get it to be in a random order (2,3,1,4,5). How do you do that? I think it's a...
2
by: Arnau Rebassa | last post by:
Hi everybody, I'm doing the following query: select * from messages order by random() limit 1; in the table messages I have more than 200 messages and a lot of times, the message retrieved...
19
by: Boris Borcic | last post by:
does x.sort(cmp = lambda x,y : cmp(random.random(),0.5)) pick a random shuffle of x with uniform distribution ? Intuitively, assuming list.sort() does a minimal number of comparisons to ...
6
by: badcrusher10 | last post by:
Hello. I'm having trouble figuring out what to do and how to do.. could someone explain to me what I need to do in order to work? THIS IS WHAT I NEED TO DO: Professor Snoop wants a program...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.