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
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'];
}
You could put them all in an array in PHP, then randomize the order of that array with shuffle(), or make the query to select the most recent 12 a sub query, then randomize the results with the outer query. Just store the items with $items[] = $row;, then shuffle($items); and iterate over it. You wont get the $photo_id in $key, but it will still be in $item['photo_id']
PHP's shuffle() function removes any existing keys from your array:
Note: This function assigns new keys to the elements in array. It will remove any existing keys that may have been assigned, rather than just reordering the keys.
This function is best used with numerically indexed arrays. A quick approach would be to just write your own shuffle function that works on associative arrays. I found this one on a previous Stack Overflow post:
function shuffle_assoc($list) {
if (!is_array($list)) return $list;
$keys = array_keys($list);
shuffle($keys);
$random = array();
foreach ($keys as $key) {
$random[] = $list[$key];
}
return $random;
}
Link to the original: