Jump to content


Photo

ORDER BY RAND() pagination issue


  • Please log in to reply
13 replies to this topic

#1 1internet

1internet

    Advanced Member

  • Members
  • PipPipPip
  • 137 posts

Posted 03 February 2013 - 05:12 PM

So I want to show my results in random order, I have used the ORDER BY RAND() mysql function, but the issue is, when you have pagination and go to another page, every page is also random results, i.e. there could be the same results as the previous page, and by clicking through all pages, you will never see all results.
My query is
SELECT * FROM `businesses` ORDER BY RAND() LIMIT $startResults, $perPage

So how do I make all the results random, but not sure duplicate results on the other pages?

#2 Christian F.

Christian F.

    Advanced Member

  • Staff Alumni
  • 3,106 posts
  • LocationNorway

Posted 03 February 2013 - 05:18 PM

You have to save the results of the entire query to another (temporary) location, and use that for the basis of the pagination.
The simplest way you could do this, is to just serialize () the array with the results from the query (without the LIMIT clause), and then save it to a file or temporary table. However you control access to that file/table, say one per user, for instance, or how long it should be available, is completely up to you.

There are also other, more complicated, methods to solve this. Methods which may be more suited to the task if we're talking about a lot of data, but generally this should serve pretty well.

Edited by Christian F., 03 February 2013 - 05:19 PM.

Keeping it simple.

#3 requinix

requinix

    Hopeless Member

  • Moderators
  • 5,920 posts
  • LocationWA

Posted 03 February 2013 - 07:46 PM

RAND() allows you to seed the generator. Pick a random number, save that somewhere, and use it every time.
$query = "SELECT * FROM `businesses` ORDER BY RAND($random) LIMIT $startResults, $perPage";

[edit] This is fine if you don't have too many results; this query is bad and will take a relatively long time so if not then storing the results in a table (with some identifying information so you know whose results they are) is better.

Edited by requinix, 03 February 2013 - 07:47 PM.


#4 P5system

P5system

    Advanced Member

  • Members
  • PipPipPip
  • 34 posts
  • LocationUSA

Posted 05 February 2013 - 01:07 AM

Take the results in an array. An use array pagination it will work

If you like my reply please click on https://plus.google....w.p5systems.com to approve it.


Thanks,
P5 Systems
http://www.p5systems.com/


#5 requinix

requinix

    Hopeless Member

  • Moderators
  • 5,920 posts
  • LocationWA

Posted 05 February 2013 - 03:30 AM

Take the results in an array. An use array pagination it will work

Unless there are 1M rows to consider. Or even 10K.

[edit] Shuffling the array, which you didn't say but would be a fundamental requirement to the solution, doesn't help. It's the same problem: it'll be random every single time. srand() might affect shuffle(), I don't know, but if so that would be a solution to that... except if you're seeding the RNG you might as well do it in MySQL where it'll perform better.

Edited by requinix, 05 February 2013 - 03:32 AM.


#6 1internet

1internet

    Advanced Member

  • Members
  • PipPipPip
  • 137 posts

Posted 05 February 2013 - 03:05 PM

requinx - if I use the $random, does that mean this variable is a random number, that is then stored within the session?

#7 Christian F.

Christian F.

    Advanced Member

  • Staff Alumni
  • 3,106 posts
  • LocationNorway

Posted 05 February 2013 - 03:10 PM

While I'm not requinix: Yes, that's what it means. If it's not set in the session variable, then generate it, save it, and then send it to the query.
Keeping it simple.

#8 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,723 posts
  • LocationCanada

Posted 05 February 2013 - 06:17 PM

This is really more complicated than you think. If the pagination order is supposed to be random how would it handle new/removed records?

So, I'm on page 2 and someone removes a record that would have been on page 3. What happens when I navigate to Page 3? Depending on the method chosen above, the result could be all over the place. Page 3 has 1 less record than it should, the entire record set could be completely re-randomized (even with a seed value), empty row int he grid, etc. etc. And the same problems could arise if someone adds a record.
The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#9 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 05 February 2013 - 06:23 PM

Who would bother to look past page 1 of a result set with 10k rows in random order, without applying a logical order?
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#10 Christian F.

Christian F.

    Advanced Member

  • Staff Alumni
  • 3,106 posts
  • LocationNorway

Posted 05 February 2013 - 06:33 PM

Not quite seeing the problem you're describing, Psycho. Either method described would pull the full number of results on page 3, the only difference would be whether or not you'd see the deleted record as well. Even if he went for the temporary storage solution, he could still write code that deleted the record from there as well.
The ordering of the results aren't of any consequence for the number of rows the query returns, after all. Not unless you save the page number for each record on the first query. Which, I agree, would be quite silly. ;)
Keeping it simple.

#11 requinix

requinix

    Hopeless Member

  • Moderators
  • 5,920 posts
  • LocationWA

Posted 05 February 2013 - 08:26 PM

Not quite seeing the problem you're describing, Psycho. Either method described would pull the full number of results on page 3, the only difference would be whether or not you'd see the deleted record as well.

If you do the seeded-RAND() I mentioned then the order will change a bit: rows located earlier that the deleted record will sort the same relative to each other, but rows after the deleted record will appear randomly interspersed. At least in MySQL 5.5.
mysql> create table test (a int);
Query OK, 0 rows affected (0.12 sec)
 
mysql> insert into test values (1),(2),(3),(4),(5),(6),(7),(<img src='http://forums.phpfreaks.com/public/style_emoticons/<#EMO_DIR#>/cool.gif' class='bbc_emoticon' alt='8)' />,(9),(10);
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0
 
mysql> select * from test order by rand(123);
+------+
| a    |
+------+
|    6 |
|    4 |
|    9 |
|    8 |
|    2 |
|   10 |
|    3 |
|    5 |
|    1 |
|    7 |
+------+
10 rows in set (0.06 sec)
 
mysql> delete from test where a=10;
Query OK, 1 row affected (0.03 sec)
 
mysql> select * from test order by rand(123);
+------+
| a    |
+------+
|    6 |
|    4 |
|    9 |
|    8 |
|    2 |
|    3 |
|    5 |
|    1 |
|    7 |
+------+
9 rows in set (0.03 sec)
 
mysql> delete from test where a=5;
Query OK, 1 row affected (0.04 sec)
 
mysql> select * from test order by rand(123);
+------+
| a    |
+------+
|    7 |
|    4 |
|    9 |
|    2 |
|    3 |
|    6 |
|    1 |
|    8 |
+------+
8 rows in set (0.04 sec)


#12 Psycho

Psycho

    Advanced Member

  • Gurus
  • 10,723 posts
  • LocationCanada

Posted 07 February 2013 - 10:44 AM

If you do the seeded-RAND() I mentioned then the order will change a bit: rows located earlier that the deleted record will sort the same relative to each other, but rows after the deleted record will appear randomly interspersed. At least in MySQL 5.5.


Right. What I was getting at is that if the order is "random" there is no logical way to handle adds/deletions to the source data as you navigate from page to page. So, if using the seed method, a record was deleted from page 2, then all the records on page 3, 4, etc. would change.

You could build some custom functionality such as getting a random list and storing it is session/cookie data. Then, if a record is deleted, all the records after that record would move up in position. And, if records are added, the only logical thing to do would be to add them to the end of the list (which really isn't random). If you are going to create some fixed list to start such as this, you definitely need to account for add/deletes otherwise you may get less records than intended on a page and new records won't display in the data.

Believe me, I have had to deal with many different issues regarding pagination. I've had product managers and designers come up with plenty of ideas that sound good on paper but cannot logically be implemented (e.g. subtotals). When these issues are identified the typical response is to add more complexity to handle those issues - which leads to more bugs. Some may argue that add/deletes while a user is paginating through the records is an edge case scenario and does not need to have a lot of additional work to make it "perfect". However, my position is that whatever feature is being implemented should "just work". A randomized pagination doesn't "just work". And, personally, as a user, my first instinct would be to sort the grid in some logical order - but that my be due to the fact that I have a slight OCD tendency.
The quality of the responses received is directly proportional to the quality of the question asked.

I do not always test the code I provide, so there may be some syntax errors. In 99% of all cases I found the solution to your problem here: http://www.php.net

#13 fenway

fenway

    MySQL Si-Fu / PHP Resident Alien

  • Moderators
  • 16,186 posts
  • LocationToronto, ON

Posted 08 February 2013 - 11:27 PM

Not that I understand why you need this -- but depending on what you're actually doing, you can use some sort of function to apply, seeded for each user/timestamp/whatever, which you apply to the PK.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#14 1internet

1internet

    Advanced Member

  • Members
  • PipPipPip
  • 137 posts

Posted 12 February 2013 - 04:28 AM

making the argument for rand() a random number that was set in the session seemed to work.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com