CODEWITHSUNDEEP
phpmysql
user1022772
user1022772

Reputation: 651

shuffle random MYSQL results

The following code displays random database images as well as one specific image (which is what I want). How can I shuffle these results after database query as image ID 11 is always displayed first? I want image ID 11 displayed randomly amongst the others.

Can I use SHUFFLE(). If so where exactly do I put it as at the moment?

Any help would be appreciated as still learning PHP

Thanks.

<?php 

mysql_connect("", "", "") or die(mysql_error()) ; 
mysql_select_db("images") or die(mysql_error()) ;


$photo=mysql_query("SELECT * FROM `profile_images` ORDER BY (ID = 11) DESC, RAND()      
LIMIT 7");


while($get_photo=mysql_fetch_array($photo)){ ?>

<div style="width:300px;">


<img src="<? echo $get_photo['url']; ?>">


</div>

<? } ?>

Upvotes: 8

Views: 22620

Answers (4)

MattClaff
MattClaff

Reputation: 685

Its doesnt need to be that complicated if you just put for example 

SELECT * FROM tbl_table ORDER BY RAND()

This will randomise your data. if you want to limit out how many images you show then just put:

SELECT * FROM tbl_table ORDER BY RAND() LIMIT 6

Upvotes: 10

gintas
gintas

Reputation: 2178

You can shuffle them after they are retrieved to php.

$photos = array();
while ($get_photo = mysql_fetch_array($photo))
    $photos[] = $get_photo;

shuffle($photos);

Or you can do it with subqueries:

SELECT A.* FROM (
    SELECT * FROM `profile_images` 
    ORDER BY (ID = 11) DESC, RAND()      
    LIMIT 7
) as A
ORDER BY RAND()

Upvotes: 14

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57690

  1. First select the specific image then union other 6 images collected by random.
  2. When you sort something by rand() all the rows get unique random value to sort so the subgroups aren't sorted. So using more columns in order by clause does not work if there is rand() present. Hence I have used alias the result and sort it again.

See the query

(SELECT * 
 FROM   `profile_images` 
 WHERE  id = 11 
 LIMIT  1) 
UNION 
(SELECT * 
 FROM   (SELECT * 
         FROM   `profile_images` 
         WHERE  id != 11 
         ORDER  BY Rand() 
         LIMIT  6) `t` 
 ORDER  BY id DESC)

Upvotes: 4

Peter Kiss
Peter Kiss

Reputation: 9329

First select the image with ID 11 and than query the rest with UNION keyword

SELECT *
FROM Images
Where ID = 11
UNION
SELECT *
FROM Images
WHERE ID != 11

Upvotes: 1

Related Questions