How can I make sure that two rows selected at random are different from one another?

Question

I have a table photos with many photos in it and I need to select two at random:

In getnew.php

$result = mysqli_query($conn,"SELECT * FROM photos ORDER BY rand() LIMIT 1");
$result2 = mysqli_query($conn,"SELECT * FROM photos ORDER BY rand() LIMIT 1");

  $row = $result->fetch_assoc();
  $img1link = $row['link'];
  // more stuff from $row

  $row2 = $result2->fetch_assoc();
  $img2link = $row2['link'];
  // more stuff from $row2

However I need to prevent it from selecting the same photo twice (the selected photos must be different), i.e. $img1link should not = $img2link. I then need to retrieve the data using $.getJSON in another file, using an array at the end of getnew.php.

The array at the end of getnew.php:

echo json_encode(array('img1'=>$img1link,'img2'=>$img2link, ...(etc)... ));

How can I make sure the selected photos are different by the time the variable is stored in the array? I tried to create an if/else statement but didn't really understand what I was doing.

I have a poor understanding of PHP/MySQL as I'm a beginner, many of the codes I'm using are copied from StackOverflow answers and adapted to my own, but I haven't gotten a full understanding of them yet. — frosty, May 08 '15 at 10:48
@frosty I found http://www.mysqltutorial.org/, and the official documentation http://dev.mysql.com/doc/ to be most helpful for me, along with reading as much as I could on Stack Overflow / books. Good luck. — Eilidh, May 08 '15 at 12:12

score 3 · Accepted Answer · edited May 23 '17 at 11:59

3

You can just execute once but get two instead so that you'll never pick the same row:

$result = mysqli_query($conn,"SELECT * FROM photos ORDER BY rand() LIMIT 2");
$row = $result->fetch_assoc();
$row2 = $result->fetch_assoc();
// invoke `->fetch` twice to get the first and second row
$img1link = $row['link'];
$img2link = $row2['link'];

Sidenote: Be careful of that ORDER BY rand() clause since it'll be slow on large data sets. You can use an alternative with @Bill Karwin's great answer

edited May 23 '17 at 11:59

Community

1
1

answered May 08 '15 at 10:38

Kevin

41,694
12
53
70

Just curious, as I didn't realise ORDER BY rand() would cause problems with large tables, how can I adapt his answer to my own problem? It looks a bit difficult to understand. Thank you – frosty May 08 '15 at 11:00
@frosty glad this helped, actually you can just add on that to this answer, quite straightforward to apply, get the count first, then apply it on the current query. – Kevin May 08 '15 at 11:02
I'm new to coding so I apologise, could you edit your answer with his method and relate it to mine? It also might help people if they come across my question since ORDER BY rand() often shouldn't be used – frosty May 08 '15 at 11:08

Sougata Bose · Answer 2 · 2015-05-08T10:44:01.683

1

Run a single query -

 $result1 = mysqli_query($conn,"SELECT * FROM photos ORDER BY rand() LIMIT 2");

  while($row = $result->fetch_assoc()) {
      $imglink[] = $row['link'];
  }

You will get the links in $imglink[] array.

edited May 08 '15 at 10:44

answered May 08 '15 at 10:37

Sougata Bose

31,517
8
49
87

ow, got it, tnx .. :) – May 08 '15 at 10:52

How can I make sure that two rows selected at random are different from one another?

2 Answers2

Linked