php - How to select a specific value plus extra random values in a query?

541

I am pretty new to this but I am trying to make a quiz of 5 multiple choice questions. each question has 4 choices. I have the questions and their respective answer in a database. I am trying to randomize the questions and answers so each time a user takes the quiz it will show different questions.

Here is my code:

$qquery = "SELECT question from questions order by rand() limit 1"; 
$question = getvalue($qquery);  


$aquery = "SELECT (SELECT answer from questions where question = '$question') 
as right_ans, (select answer from questions where question != '$question') 
order by rand() limit 4";

The getvalue(query) function returns the question string. I am using that in order to find the right answer on the database. The function also runs the query and displays the results. My second query isn't working. I would appreciate any input. Thanks!

516

Answer

Solution:

Try this new one, dont know, works with mysql or not.

$aquery = 
"SELECT TMP.ANSWER, TMP.RANDOM FROM (    
SELECT ANSWER AS 'ANSWER', RAND() AS 'RANDOM' FROM QUESTION WHERE QUESTION = '$question'
LIMIT 1
UNION ALL 
SELECT ANSWER AS 'ANSWER', RAND() AS 'RANDOM' FROM QUESTION WHERE QUESTION != 'question'
LIMIT 4
) AS TMP
ORDER BY TMP.RANDOM ";

Answer

Solution:

With your current schema you can try it this way with one query

Sample output:

|  QUESTION |  ANSWER | WRONG_ANSWER |
|

460

Answer

-|---------|

209

Answer

----| | question4 | answer4 | answer9 | | question4 | answer4 | answer11 | | question4 | answer4 | answer7 |

Here is SQLFiddle demo

Obviously you read the question and the right answer once (e.g. from the the first) row and grab other 3 wrong answers from{-code-2} column while you iterate over the resultset in php.

or you can go further and pack wrong answers in a delimited list

SELECT question, answer, GROUP_CONCAT({-code-2}) {-code-2}
  FROM
(
  SELECT r.question, r.answer, q.answer {-code-2} 
    FROM
  (
    SELECT question, answer 
     FROM questions 
    ORDER BY RAND() 
    LIMIT 1
  ) r JOIN questions q
      ON q.question <> r.question
   ORDER BY RAND()
   LIMIT 3
) q

Sample output:

|  QUESTION |  ANSWER |            WRONG_ANSWER |
|

Answer

-|---------|

278

Answer

-----| | question9 | answer9 | answer6,answer1,answer7 |

Here is SQLFiddle demo

Then in php you always have a single row resultset. And you can easilyexplode()wrong_answer values.

People are also looking for solutions to the problem: php - Show database entries after pressing submit button

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question