Optimize database query

Question

I have the following database structure.

question table.

id  question               desc
1   What was john's age?   About john

option table

id  question_id  option_value    correct_ans
1      1             20              0
2      1             18              0
3      1             28              1
4      1             60              0

Now i want to get the record from both the tables with all questions with their options. I want the following structure. I did the code and get the result but it took two sql queries. first to get all the questions from question table then create an array and then get the options for all questions and put in the array. Is it possible to get such type of data using a single query?

      array([0]=array('question_id'=>1,
                      'question'=>'what was john's age',
                      'desc'=>'About john',
                      'options'=>array([0]=>array('option_id'=>'1',
                                                  'option_value'=>20,
                                                  'correct_ans'=>0),
                                       [1]=>array('option_id'=>'2',
                                                  'option_value'=>18,
                                                  'correct_ans'=>0),
                                       [2]=>array('option_id'=>'3',
                                                  'option_value'=>28,
                                                  'correct_ans'=>1),
                                       [3]=>array('option_id'=>'4',
                                                  'option_value'=>60,
                                                  'correct_ans'=>0)
                                       )
                       ),
             [1]=array('question_id'=>2,
                        etc...............

Answer 1

Use a join:

SELECT q.id question_id, question, desc, o.id option_id, option_value, correct_ans
FROM question q
LEFT JOIN option o
ON q.id = o.question_id
ORDER BY question_id, option_id

This uses a LEFT JOIN so you'll get information about the question even if no options appear in the options table (the columns from the options table will be NULL in this case). If that's not allowed, you can use an INNER JOIN instead.

The results of this query will repeat the question information for each row. Your loop that creates the array from the results will have to check whether the question_id has changed from the previous row. If it has, it starts a new element of the top-level array; if the question_id is the same, it adds to the options element of the current array element.