MySQL result with multiple occurance of an element depending on importance

Can anyone tell me if it's possible to fetch a list of mysql entries depending on the importance of each row. Lets say I have 4 entries in a table (id, name, importance). 0 = highest importance.

id | file      | importance
---|-----------|-----------
1  | movie.mp4 | 0
2  | image.jpg | 1
3  | movie1.mp4| 2
4  | movie2.mp4| 2

want to create a array with 20 enteries, which are populized with result开发者_如何学运维 from the 4 elements (example 5*movie.mp4, 3*image.jpg, 2*..., 2*...), but the trick is the elements must be randomly placed in the array and not after each other.

$file[] = movie.mp4;
$file[] = movie2.mp4;
$file[] = image.jpg;
$file[] = movie.mp4;
$file[] = image.jpg;
$file[] = movie1.mp4;
$file[] = movie.mp4;
$file[] = image.jpg;
$file[] = movie.mp4;
$file[] = movie1.mp4;
$file[] = movie.mp4;
$file[] = movie2.jpg;

Hope you get the point, or else please ask :)

SELECT draw_bucket.file
FROM (
    SELECT row,
    FLOOR( RAND() * ( SELECT SUM( POW( 2, 2 - importance ) ) FROM files ) ) AS rnd
    FROM ( 
       (SELECT 1 AS row) UNION (SELECT 2) UNION (SELECT 3) UNION (SELECT 4) UNION
       (SELECT 5) UNION (SELECT 6) UNION (SELECT 7) UNION (SELECT 8) UNION
       (SELECT 9) UNION (SELECT 10) UNION (SELECT 11) UNION (SELECT 12) UNION
       (SELECT 13) UNION (SELECT 14) UNION (SELECT 15) UNION (SELECT 16) UNION
       (SELECT 17) UNION (SELECT 18) UNION (SELECT 19) UNION (SELECT 20)
    ) AS row_nums
) AS rows
INNER JOIN (
    SELECT @row := @row + 1 AS row, file
    FROM ( 
       (SELECT 1 AS cnt) UNION (SELECT 2) UNION (SELECT 3) UNION (SELECT 4)
    ) AS cnt
    INNER JOIN files
    ON( cnt.cnt <= POW( 2, 2 - files.importance ) )
    INNER JOIN ( SELECT @row := -1 ) AS INIT
) AS draw_bucket
ON( rows.rnd = draw_bucket.row )

number of (SELECT 1), (SELECT 2), ... in the row_nums subquery should equal the number of entries you want and in cnt subquery should equal 2 ^ minimumPriority.

You can simplify this by doing most of the job in PHP (for example). Do a SELECT * and fill an array like this:

$items = array(
    array( 'file' => 'movie.mp4', 'importance' => 0 ),
    array( 'file' => 'image.jpg', 'importance' => 1 ),
    array( 'file' => 'movie1.mp4', 'importance' => 2 ),
    array( 'file' => 'movie2.mp4', 'importance' => 2 )
);

Then do the drawing in PHP:

$minImportance = 2;

$drawBucket = array();
foreach( $items as $index => $item ) {
    for( $i = 0; $i < pow( 2, $minImportance - $item['importance'] ); ++$i ) {
        $drawBucket[] = $index;
    }
}

$output = array();
for( $i = 0; $i < 20; ++$i ) {
    $randomIndex = mt_rand( 0, count($drawBucket) - 1 );
    $output[] = $items[ $drawBucket[$randomIndex] ][ 'file' ];
}

var_dump( $output );

We can do it simple with php:

<?php
function cmpf(a,b)
{
    return a['importance'] - b['importance'];
}
$arr = array();
$res = mysql_query("SELECT * from tbname ORDER BY importance;");
$k = 0;
while ($row = mysql_fetch_assoc($res))
{
  $arr[$k] = $row;
  $k++
}
shuffle($arr);
usort($arr, "cmpf");
?>