How do you make sure two rows selected at random are different from one another?

Question

I asked this question yesterday and received an easy solution, but it was about ORDER BY rand(), which I was told was inefficient for large tables. I searched the web and found this a much more efficient method:

Get the total number of rows in the table: $rows
Use mt_rand to set $row1_id as a random number between 1 and the total number of rows: $row1_id = mt_rand(1,$rows)
Use mt_rand again to set $row2_id as a random number between 1 and the total number of rows: $row2_id = mt_rand(1,$rows)
Run queries to select random rows, i.e.

mysqli_query($conn,"SELECT * FROM photos WHERE photo_id=$row1_id") mysqli_query($conn,"SELECT * FROM photos WHERE photo_id=$row2_id")

However, I need to make sure that $row1_id != $row2_id (the randomly generated numbers must be different from each other). I tried using an if statement but it only lessened the chances of the numbers being the same, but it was still possible.

Any easy solution to this one?

The `photo_id` should be different? – Sougata Bose May 09 '15 at 05:49 — Sougata Bose, May 09 '15 at 05:49
Yes, so $row1_id shouldn't equal $row2_id. – frosty May 09 '15 at 05:50 — frosty, May 09 '15 at 05:50

pala_ · Accepted Answer · 2015-05-10T01:25:35.903

1

Just generate your second random number inside a loop to make sure it isn't equal to the first one. In all likelihood this loop will only ever execute once.

$num1 = mt_rand(...);
$num2 = 0;

do {
  $num2 = mt_rand(...);
} while($num2 == $num1);

// $num1 and $num2 are guaranteed to be different

This method will work for you, so long as your row id's are contiguous, with no gaps

If there are gaps, you'll need to generate new numbers up until both result in database hits. Something like this.

$photo1 = null;
$photo2 = null;

do {
  $num = mt_rand(...);
  $photo1 = mysql_query(...);
} while(mysqli_num_rows($photo1) == 0);

$photo1 = mysqli_fetch_assoc($photo1);

do {
  $num = 0;
  do {
    $num = mt_rand(...);
  } while($num == $photo1['id']);
  $photo2 = mysql_query(...);
} while(mysqli_num_rows($photo2) == 0);

$photo2 = mysqli_fetch_assoc($photo2);]

Up to you to compare these methods against the order by rand() options and see which is more performant.

edited May 10 '15 at 01:25

answered May 09 '15 at 05:53

pala_

8,901
1
15
32

Since people can delete their own photos (delete the row from the table), there will be gaps in the row IDs. Is that what you are asking? – frosty May 09 '15 at 05:55
yes - if there are gaps in the series, then generating the random numbers at application level like this is a bit pointless - you would need to generate a new number if the query result was empty – pala_ May 09 '15 at 05:56
@frosty i've updated with a second method that will take care of database gaps. Up to you now to test out performance against the database only methods. – pala_ May 09 '15 at 06:04
It's confusing me a bit, could you relate it to my own problem? i.e. what to put for the (...) – frosty May 09 '15 at 15:02
the exact same as you have in your question. – pala_ May 09 '15 at 15:14
for some reason it's not working, I've probably done something wrong: http://puu.sh/hH5TJ/833acbf7f5.png – frosty May 09 '15 at 15:17
Instead of choosing different images it chooses the same one each time – frosty May 09 '15 at 15:19
what value does `$rows` have? – pala_ May 09 '15 at 15:25
The total number of rows in the table – frosty May 09 '15 at 15:27
print out the query string just before you execute it both times - what does it say? – pala_ May 09 '15 at 16:09
I printed $num after each. For the first $num it was equal to 1. Then the second $num was "222221" - So it's generating a new random number until it's equal to the first num, it seems – frosty May 09 '15 at 21:41
Funny, all I had to do was change != to ==. Since it needs to generate a new number while it's $num == the first one, not when it's not equal! – frosty May 09 '15 at 21:47
The only issue with this is if I auto_increment the photo_id, the total number of rows will be less than some photo_id's. Example: photo_ids are 1,2,3,4,5 ... number 3 is deleted so that you have 1,2,4,5. Then 5 will never be selected because the random number generated is between 1 and the total number of rows (4). – frosty May 09 '15 at 21:57
ah yeah woops about the == - i had it right in the first one. also, just check for max(photo_id), intead of the row count. that should sort your last issue – pala_ May 10 '15 at 01:25
Yep, it's completely solved now. I don't really understand why it ignores gaps though. For example I have photo_id's 1, 2, 21, and it ignores 3-20? – frosty May 10 '15 at 07:21

Sougata Bose · Answer 2 · 2015-05-09T05:53:04.753

0

Just a little modification to yesterday's query. Try with this query -

SELECT * FROM photos GROUP BY photo_id ORDER BY rand() LIMIT 2

edited May 09 '15 at 05:53

answered May 09 '15 at 05:51

Sougata Bose

31,517
8
49
87

That will likely give you the same pairing over and over, not a random pairing that differs each time – pala_ May 09 '15 at 05:52
The number of rows could eventually be in the millions – frosty May 09 '15 at 05:54
@pala_ didnot get you. – Sougata Bose May 09 '15 at 05:55

score 0 · Answer 3 · edited May 09 '15 at 07:18

0

You have to try where clause and IN function.

SELECT * FROM photos WHERE photo_id IN ($row1_id, $row2_id);

edited May 09 '15 at 07:18

Rasclatt

12,498
3
25
33

answered May 09 '15 at 05:55

Smila

1,130
8
16

How do you make sure two rows selected at random are different from one another?

3 Answers3

Linked