mysql命令由photo_id desc和shuffle之后

Question

I want to select the most recent 12 rows from a table, but then shuffle the order.

So i cant use ORDER BY RAND() becouse that would just randomly pick some rows and not the most recent 12 rows.

I was thinking something like this, but it didnt work out as planned:

    $artig_photos = mysql_query("

    SELECT photo_id, photo_name
    FROM `migo_artig_photos`
    WHERE (
        photo_deleted=0 AND photo_type=2
    )
    ORDER BY photo_id DESC
    LIMIT 12;

");

while ($row = mysql_fetch_array($artig_photos)) {
    $artig_shuffled[$row['photo_id']] = $row['photo_name'];
}   

shuffle($artig_shuffled);

later when i do:

foreach ($artig_shuffled as $key => $value) {
}

i expected the key to be photo_id and the value to be photo_name with the correct relation between them, guess i was wrong.

Any tips about how to solve this problem? Maybe my approach isnt good at all.

Best of regards, Alexander

Answer 1

You could use a subquery:

SELECT * FROM (
  SELECT `migo_artig_photos`.`photo_id`,
         `migo_artig_photos`.`photo_name`
  FROM   `migo_artig_photos`
  WHERE  `migo_artig_photos`.`photo_deleted` = 0 AND
         `migo_artig_photos`.`photo_type` = 2
  ORDER BY photo_id DESC
  LIMIT 12) `top_12_migo_artig_photos`
ORDER BY RAND();

Alternatively, you could do this:

// To shuffle:
while ( $row = mysql_fetch_array($artig_photos) )
{
  $artig_shuffled[] = $row;
}

shuffle($artig_shuffled);

// To display:
foreach ( $artig_shuffled as $row )
{
  echo $row['photo_id'];
  echo $row['photo_name'];
}