Mysql order by rand PHP

Question

I am using

$nameimg = mysql_query("SELECT * 
                        FROM images 
                        WHERE id='".$row['imgID']."' 
                        ORDER BY RAND()    
                        LIMIT 10");

To show random images by said user, but it's not showing random images. It's just showing via time submitted.

How many images are there - what does SELECT COUNT(*) FROM images WHERE id='".$row['imgID']." return? — Adrian Cornish, Aug 06 '12 at 22:15
It looks like you are just selecting one row, or isn't the id colun the primary key column? — arnoudhgz, Aug 06 '12 at 22:34

score 0 · Answer 1 · answered Aug 06 '12 at 22:14

0

WHERE conditions checking for an ID usually reduce the result set to one row, so the whole ORDER BY clause becomes useless.

You should think about your schema and redesign the query.

answered Aug 06 '12 at 22:14

CodeZombie

5,367
3
30
37

I think it is a user_id rather than an image_id - but I suspect there is only one row too – Adrian Cornish Aug 06 '12 at 22:16
2

@AdrianCornish: You might be right, but assuming the variables/indexes have a meaningful name I might be right aswell ;-) – CodeZombie Aug 06 '12 at 22:19

Jill-Jênn Vie · Answer 2 · 2012-08-06T22:50:43.353

0

You should rather use PHP to randomly shuffle the array. If the field you are interested in is called url:

$req = mysql_query("SELECT url FROM images WHERE user_id = '{$row['userID']}'");
$images = array();
while($image = mysql_fetch_array($req)) {
    $images[] = $image['url'];
}
shuffle($images);
$tenImages = array_slice($images, 0, 10);

Edit. And please consider using PDO for prepared statements.

Update. Why not picking the ten images one at a time?

$images = array();
while(count($images) < 10) {
    $req = mysql_query("SELECT url FROM images WHERE user_id = '{$row['userID']}' LIMIT " . rand(1, 10000) . ", 1");
    $image = mysql_result($req, 0, 0);
    if(!in_array($image)) {
        $images[] = $image;
    }
}

Funnier (and faster) way. Provided here.

$ids = array();
for($i = 0 ; $i < 1000 ; $i++) {
    $ids[] = rand(1, 10000);
}
$req = mysql_query("SELECT url FROM images WHERE user_id = '{$row['userID']}' AND id IN (" . implode(',', $ids) . ") LIMIT 10");

edited Aug 06 '12 at 22:50

answered Aug 06 '12 at 22:21

Jill-Jênn Vie

1,849
19
22

2

In my opinion SQL is the right way to do it. You don't want to retrieve a lot of images just to build an array, get the random image and immediately release the allocated memory. That does not make sense when thinking about network traffic and memory allocation. – CodeZombie Aug 06 '12 at 22:23
I agree with ZombieHunter, why do it the hard way (less optimized, with PHP) when you can do it the easy way with SQL – arnoudhgz Aug 06 '12 at 22:33
Apparently, if the table contains N images, the SQL server will generate N random numbers while you only need 10 images. This link explains a trade-off: http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/ – Jill-Jênn Vie Aug 06 '12 at 22:34
1

Your concern about `RAND()` is apt, but there are other SQL solutions http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function – Mike B Aug 06 '12 at 22:36
1

@Jill-JênnVie: I agree with the statement about RAND(), but that's still no reason to use PHP in a scenario where SQL is best suited. – CodeZombie Aug 06 '12 at 22:38

Mysql order by rand PHP

2 Answers2