MySQL queries to Multi-Dimensional

Question

I have a requirement where I need to create JSON files from MySQL queries.

The queries are as follows:

1. SELECT category_id, category, question, answer FROM table1;

2. SELECT keywords FROM table2 WHERE category_id = table1.category_id;

I know json_encode will convert from array to JSON, but I am stuck about how to push data from query to the following multi-dimensional array structure given below.

<?php
    array (
           0 => (array(
                      'Category' => 'Category 1',
                      'question' => 'Question 1',
                      'answer' => 'Answer 1',
                      'keywords' => array (
                              0 => 'tag 1',
                              1 => 'tag 2',
                              2 => 'tag 3',
                             ),
                      )
                 ),
           1 => (array(
                      'Category' => 'Category 2',
                      'question' => 'Question 2',
                      'answer' => 'Answer 2',
                      'keywords' => array (
                              0 => 'tag 4',
                              1 => 'tag 5',
                              2 => 'tag 6',
                              3 => 'tag 7',
                             ),
                      )
                 ),
  );
?>

Similar questions on StackOverflow are given below, but none solve my problem.

1. mysql-queries-to-multi-dimensional-php-array

2. php-sorting-mysql-result-to-multi-dimensional-array

Answer 1

Try like this

 $sql = "SELECT t1.category_id, t1.category, t1.question, t1.answer, GROUP_CONCAT(t2.keywords ORDER BY t2.keywords ASC) AS key_words
FROM table1 t1
JOIN table2 t2 ON t2.category_id = t1.category_id";

$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
  $final_array['Category'] = $row['category'];
  $final_array['question'] = $row['question'];
  $final_array['answer'] = $row['answer'];
  $final_array['keywords'] = explode(',',$row['key_words']);
  $output[] = final_array;
}
print_r($output);

incase you are not using the mysqli class,

use mysqli_query() instead of $mysqli->query();

and mysqli_fetch_assoc() for $result->fetch_assoc();

Answer 2

You have to use JOIN

SELECT t.category_id, t.category, t.question, t.answer 
FROM table1 t
LEFT JOIN keywords k 
ON k.category_id=t.category_id

after this, use mysql_fetch_assoc()

and then Json_encode();