php - How to make a controlled "shuffle" order? - Stack Overflow

admin管理员组

文章数量:1415644

I have a set of quiz game questions in a sql database (javascript and sqlite actually). The questions all have a difficulty level from 1 to 5, 5 being hardest. Here is a simplified visualization of the data...

+---------+--------------+  
|   id    | difficulty   |   
+---------+--------------+  
| 1       |      1       |    
| 2       |      5       |    
| 3       |      2       |    
| 4       |      3       |    
| 5       |      2       | 
| 6       |      2       |    
| 7       |      4       |    
| 8       |      1       |    
| 9       |      5       |    
| 10      |      3       |      
+---------+--------------+   

Now I can shuffle these fine in sql or code so they are in a random order with no repeats but I also want to have control over the way the difficulty field is ordered.

So for instance I could have a shuffled set of question where the difficulty level order looks like this...

1,1,5,2,3,3,2,2,2,4

This has several 'clumps' of difficulty, that's not what I want. The user playing the game would get several groups of the similarly difficult questions. An order like this would be better...

1,2,3,2,5,4,1,2,3,2

I want to ensure the questions are shuffled but without difficulty clumping. An even spread of difficulty where there are few, if any 'clumps'. Any help on the MySQL/javascript (or PHP) would be great.

I have a set of quiz game questions in a sql database (javascript and sqlite actually). The questions all have a difficulty level from 1 to 5, 5 being hardest. Here is a simplified visualization of the data...

+---------+--------------+  
|   id    | difficulty   |   
+---------+--------------+  
| 1       |      1       |    
| 2       |      5       |    
| 3       |      2       |    
| 4       |      3       |    
| 5       |      2       | 
| 6       |      2       |    
| 7       |      4       |    
| 8       |      1       |    
| 9       |      5       |    
| 10      |      3       |      
+---------+--------------+   

Now I can shuffle these fine in sql or code so they are in a random order with no repeats but I also want to have control over the way the difficulty field is ordered.

So for instance I could have a shuffled set of question where the difficulty level order looks like this...

1,1,5,2,3,3,2,2,2,4

This has several 'clumps' of difficulty, that's not what I want. The user playing the game would get several groups of the similarly difficult questions. An order like this would be better...

1,2,3,2,5,4,1,2,3,2

I want to ensure the questions are shuffled but without difficulty clumping. An even spread of difficulty where there are few, if any 'clumps'. Any help on the MySQL/javascript (or PHP) would be great.

• php
• javascript
• mysql
• algorithm
• shuffle

Share Improve this question Follow edited Sep 23, 2011 at 18:49 jfountain asked Jul 22, 2010 at 23:00 jfountainjfountain 3,83522 gold badges2525 silver badges2222 bronze badges 4

• I'm not sure what you mean by 'clumps'. Do you mean you want random questions without contiguous repeated difficulty? Can it start with any difficulty value or it should always start with 1? – Vinko Vrsalovic Commented Jul 22, 2010 at 23:09
• It can start with any difficulty and yes I mean without contiguous difficulty. Clumps would be contiguous difficulty. – jfountain Commented Jul 22, 2010 at 23:17
• 1 I think the word you're looking for is "runs", not "clumps". Is the size of the input fixed? E.g. do you have to take N questions and reorder them so there are no runs? Or do you want to extract N questions out of a set of M, where N<M? – nicolaskruchten Commented Jul 22, 2010 at 23:18
• The size of the input is fixed. I'll be reordering all of the set. Also the difficulty is weighted to the middle (2s,3s and 4s) which is ok. – jfountain Commented Jul 22, 2010 at 23:25

Add a ment  | 

5 Answers 5

Sorted by: Reset to default 5

Instead of grouping all the ids together why don't you group them by difficulty randomize each section and then pull them out one by one. Or once they are randomly sorted you could pull them from a random difficulty, then remove that difficulty level until you have a question from each.

This is what I was thinking about in answer to sje397, so I'll add it to my answer.

As long as all the other choices add up to the largest group minus one you will have no clumping (assuming your algorithm is correct). However, the algorithm would basically take the form of pick from A (group with greatest number of choices), pick from another group, pick from A etc. until A is equal to the size of the other groups. So the best algorithm would check to find the largest group and pick from it. It would then pick from another group, then check to see what group is the largest, then choose from it unless it is the previously chosen one etc.

How about the following strategy, in code: (the following was a bulleted list, but I couldn't get code appearing after a bulleted list to display correctly - I thoroughly detest this "markdown" garbage this site uses)

order the questions by difficulty

split the questions halfway into two lists, an "easy" list and a "hard" list

take questions one by one from the easy and hard lists, alternating between the two. (This would mean that you would have a slight trend from easy to difficult over the sequence of questions, which you might or might not be OK with.)

Primitive implementation:

$resultset = your_preferred_query_function('SELECT id FROM question ORDER BY difficulty');
$questions_temp = array();
while ( $row = mysqli_fetch_assoc() ) {
    $questions_temp[] = $row['id'];
}
if ( count($questions) % 2 ) {
    $loop_limit = (count($questions) - 1) / 2;
    $halfway = (count($questions) + 1) / 2;
    $questions[0] = $questions_temp[$loop_limit];
} else {
    $loop_limit = count($questions) / 2;
    $halfway = count($questions) / 2;
    $questions = array();
}
for ($i=0; $i<$loop_limit; $i++) {
    $questions[] = $questions_temp[$i];
    $questions[] = $questions_temp[$halfway+$i];
}

Now $questions is an array containing questions ordered as I suggested.

Well in a truly random sample 'clumps' do naturally appear. So if you want to remove these you have to enforce something manually, e.g. specify a pattern of difficulty and choosing a random question matching each difficulty level

Iterate through a randomly-shuffled input array and whenever you hit an element with the same difficulty level as the one before it, swap with the next element that doesn't have the same difficulty level. Just in my head, I think that this would turn your initial input into: 1,5,1,2,3,2,3,2,4,2

Depending on the input, this approach might cause clumping at the end but might be good enough...

If your input is bigger than what you need, you could also just remove any element that has the same difficulty as the one before it.

A very simple solution (it's not very efficient though) would be to do:

<?php

        define('MAX_QUESTIONS',10);

        $dbh = new PDO("mysql:dbname=so;host=127.0.0.1","","");
        $sql = "SELECT * FROM q group by difficulty order by rand()";
        $data = $dbh->query($sql);
        $rows = $data->fetchAll();
        $ids = getIds($rows);
        while (count($rows) < MAX_QUESTIONS ) {
                $sql = "SELECT * FROM q where id not in ".
                       "(".join(",",$ids).") group by difficulty order by rand()";
                $data = $dbh->query($sql);
                $more_rows = $data->fetchAll();
                $rows = array_merge($rows,$more_rows);
                $ids = getIds($rows);
        }
        print_r($rows);

        function getIds($data) {
                $ids = array();
                foreach ($data as $v) {
                        $ids[] = $v['id'];
                }
                return $ids;
        }

?>

This is needed because MySQL's group by always return the same ids, regardless of if you've ordered previously (even in a subquery.)

The good thing about this is that it guarantees no 'clumps' (at the potential cost of returning empty for the final question that would create a 'clump', you could special case that, though)

The bad thing is that you need more than a single query, and that ordering by rand() is awfully inefficient, but if your table is small it probably won't actually matter.

本文标签： phpHow to make a controlled quotshufflequot orderStack Overflow