CODEWITHSUNDEEP
phpmysqljson
Justin
Justin

Reputation: 568

PHP Join MySQL Tables, Output Formatted JSON

I'm having a difficult time trying to join two MySQL tables in my PHP Script, and then format it to JSON to be interpreted by javascript in my private app. The app is only for our art team to make edits to various products our company makes.

The purpose of this process is to create a listing of products that need attention, and a checklist of "tasks" within each product that can be checked off as they are completed (hence the "check" field).

The tables are formatted as such:

products table:

+------+-----------+-----------+---------+
|  id  |  task_id  |  product  |  notes  |
+------+-----------+-----------+---------+
|  10  |     1     |   Item 1  |         |
+------+-----------+-----------+---------+
|  11  |     2     |   Item 2  |         |
+------+-----------+-----------+---------+

tasks table:

+------+-----------+---------------+---------+
|  id  |  task_id  |      task     |  check  |
+------+-----------+---------------+---------+
|   1  |     1     |  Fix Box Art  |     0   |
+------+-----------+---------------+---------+
|   2  |     1     | Add Copyright |     1   |
+------+-----------+---------------+---------+
|   3  |     2     |  Update Text  |     0   |
+------+-----------+---------------+---------+

I've been able to join the tables in PHP with the following code:

SELECT 
   products.*, 
   GROUP_CONCAT(tasks.task, tasks.check) AS steps 
FROM products
LEFT JOIN task_steps ON products.task_id = task.task_id

Within a while loop, I am building an array like such:

$output[] = 
      array (
      "id" => $row['id'],
      "task_id" => $row['task_id'],
      "product" => $row['product'],
      "notes" => $row['notes'],
      "tasks" => $row['steps']
    );

I then output the array as JSON:

echo json_encode($output);

Here is where I am stuck. I've been able to output some JSON but nothing close to what I am looking for. The format I am looking for would look something like this:

[  
   {  
      "id":"10",
      "task_id":"1",
      "product":"Item 1",
      "notes":"",
      "tasks":[  
         {  
            "task":"Fix Box Art",
            "check":"0"
         },
         {  
            "task":"Add Copyright",
            "check":"1"
         }
      ]
   }
]

Can anyone nudge me in the right direction in how I should be formatting the table data into the mentioned JSON format, or perhaps suggest an alternative method if I am heading in the wrong direction all together?

Upvotes: 0

Views: 875

Answers (1)

dynamic
dynamic

Reputation: 48091

Push_back each task inside your loop:

while(...fetching) {

  if (!isset($output[$row['id']])) {
    $output[$row['id']] = array (
      "id" => $row['id'],
      "task_id" => $row['task_id'],
      "product" => $row['product'],
      "notes" => $row['notes'],
      "tasks" => array()
   );
  }

  $output[$row['id']]['tasks'][] = array(
    'task' => $row['task'],
    'check' => $row['check']
  );
}

In this way tasks is effectively an array

You will need to retrive the other table cols too

SELECT 
   products.*,
   tasks.*      //<<--

Upvotes: 1

Related Questions