Is it possible to select a random column in one row?

Question

For example I have 1 question and 5 answers.

Question:

5+5=?

Answer:

a. 3
b. 4
c. 5
d. 9
e. 10

Here's the sample data in the database

id  |  question  |  ans1  |  asn2  |  ans3  | ans4  |  correctAns  |
1   |  5+5       |    3   |    4   |    5   |   9   |      10      |

I want to randomize the output of the ans1,ans2,ans3,ans4, and the correctAns.

is it possible? if it is please guide me.. Thanks

Answer 1

Structure notes

While this question may be interesting, use-case with answers to question and provided structure is really bad. That is because you are restricted to certain amount of answers. That means:

• All questions will have to have all answers. If some question should have only 2 answers for choosing, then - yes, there will be N-2 NULL values (here N means number of answers, in case above N=4)
• If only one question will have to have more than N answers - then, yes, ALTER awaits. And that will produce more NULL-s for all other questions.

I recommend to change your structure. Store answers in separate table with foreign key to parent questions table. That will be enough (and, besides, question about random selection would be much easier to resolve)

Question solution

However, questions that involve table structure may be interesting to deal with - and in MySQL, such questions are classically resolved via INFORMATION_SCHEMA meta-data. To solve the issue with SQL only, you'll need to use prepared statements. Here's sample table:

mysql> SELECT * FROM test;
+------+----------+------+------+------+------+------------+
| id   | question | ans1 | ans2 | ans3 | ans4 | correctAns |
+------+----------+------+------+------+------+------------+
|    1 | 5+5      |    3 |    4 |    5 |    9 |         10 |
+------+----------+------+------+------+------+------------+
1 row in set (0.00 sec)

Now, I assume columns id and question as to be included always in first and second place to result. Other columns should be randomized. To do this, you can use INFORMATION_SCHEMA.COLUMNS table:

SELECT 
  CONCAT(
    'SELECT id, question, ', 
    GROUP_CONCAT(COLUMN_NAME), 
    ' FROM test') AS s 
FROM 
  (SELECT 
    COLUMN_NAME 
  FROM 
    INFORMATION_SCHEMA.COLUMNS 
  WHERE 
    TABLE_NAME='test' 
    AND 
    TABLE_SCHEMA='test' 
    AND 
    COLUMN_NAME NOT IN ('id', 'question') 
  ORDER BY RAND()) AS randCols;

As you can see, this SQL will produce another SQL. Result will be like:

+---------------------------------------------------------------+
| s                                                             |
+---------------------------------------------------------------+
| SELECT id, question, ans1,ans2,correctAns,ans4,ans3 FROM test |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

So this you can easily use in prepared statement. Prepare variable:

mysql> set @s=(SELECT CONCAT('SELECT id, question, ', GROUP_CONCAT(COLUMN_NAME), ' FROM test') AS s FROM (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='test' AND TABLE_SCHEMA='test' AND COLUMN_NAME NOT IN ('id', 'question') ORDER BY RAND()) AS randCols);
Query OK, 0 rows affected (0.01 sec)

Use it for statement:

mysql> prepare stmt from @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

And, finally, execute it:

mysql> execute stmt;
+------+----------+------------+------+------+------+------+
| id   | question | correctAns | ans1 | ans2 | ans4 | ans3 |
+------+----------+------------+------+------+------+------+
|    1 | 5+5      |         10 |    3 |    4 |    9 |    5 |
+------+----------+------------+------+------+------+------+
1 row in set (0.00 sec)

Answer 2

It's better to first get the result and put it in an array, and then you can shuffle that easily, something like this:

$query = "SELECT ans1, asn2, ans3, ans4, correctAns FROM table_name WHERE id=1";
$res = mysql_query($query);
$answers = mysql_fetch_array($res);
shuffle($answers);

Then, iterate through $answers as per normal.