Pick 3 random images from a database. without repeating one

[londrum]

hi. i've got a thing on my site which has to pull three random images from a database. There are about 100 images in total. I've got it working okay and everything is hunky-dory, but every once in a while it will pull the same image twice.

i was wondering if anyone knows a way of amending the code below so it is guaranteed to pull 3 different images without duplicating one.

for ( $count = 1; $count <= 3; $count += 1) {
$query = "SELECT * FROM blah ORDER BY RAND()";
$return = mysql_query($query);
$result = mysql_fetch_array($return);
echo'<img src="'.$result[1].'.jpg">'; 
}

any help appreciated!

[cameraman]

If your images are numerically indexed:
$ary = range(1,100);
shuffle($ary);

Then pull them from the table by their index, like maybe
WHERE recID IN ({$ary[0]},{$ary[1]},{$ary[2]})

[dreamcatcher]

Lose the for loop:

$query = "SELECT * FROM blah ORDER BY RAND() LIMIT 3";
$return = mysql_query($query);
while ($result = mysql_fetch_array($return)) {
echo'<img src="'.$result[1].'.jpg">';
}

dc

[londrum]

hi dreamcatcher, i like that LIMIT thing, i've stuck that in. but it still doesn't stop the duplicates appearing. it just carries on picking three random ones.

unfortunately the images aren't numbered, so i can't use cameraman's idea.

[OutdoorWebcams]

Maybe this could help:

$query = "SELECT DISTINCT * FROM blah ORDER BY RAND() LIMIT 3";

[dreamcatcher]

Yep, sorry. DISTINCT should work just fine.

dc

[sonjay]

What am I missing? I don't see any reason you would need "DISTINCT" in this query. I've never had MySQL select the same record more than once in a query like this, and I don't believe it ever does. This query should select all records, put them in random order, then take just the first 3. How could one ever be duplicated in this query?

$query = "SELECT * FROM blah ORDER BY RAND() LIMIT 3";
$return = mysql_query($query);
while ($result = mysql_fetch_array($return)) {
echo'<img src="'.$result[1].'.jpg">';
}

[OutdoorWebcams]

At first I thought the same and I wasn't able to produce duplicate records with that query. But londrum says 'it still doesn't stop the duplicates appearing.'

What could be the reason for that? Just wondering...

[sonjay]

I can make a guess at the reason. He's still using his original code, just sticking DISTINCT in there:

for ( $count = 1; $count <= 3; $count += 1) {
$query = "SELECT DISTINCT * FROM blah ORDER BY RAND()";
$return = mysql_query($query);
$result = mysql_fetch_array($return);
echo'<img src="'.$result[1].'.jpg">';
} 

That would result in 3 separate calls to the database, unrelated to each other. Of course there's a possibility that the same record will be pulled twice.

[londrum]

yup that's what it was... i took the for loop out of one page, but started testing on another page with it still on... dopey idiot! the code works fine as dreamcatcher said it, cheers.

[sonjay]

Oh, good. You had me worried for a moment, wondering if my understanding of a basic query has been wrong all this time!