Loke Loke - 7 months ago 6
PHP Question

JSON array three mysql tables in PHP

I am trying to build a json output from Three related MySQL tables. I have a table of
"terms", "term_relationships" and table "posts" each item in terms table has several relative
items in the "term_relationships" table which are referenced by "term_taxonomy_id". Each object_id is a
primary key in the posts table as ID.

terms

+---------+------------+-----------+-------------+
| term_id | name | slug | term_group |
+---------+------------+-----------+-------------+
| 12 | jewellery | jewellery | 0 |
| 13 | water | water | 0 |
+---------+------------+-----------+-------------+


term_relationships

+-----------+------------------+-----------+
| object_id | term_taxonomy_id | term_order|
+-----------+------------------+-----------+
| 59 | 12 | 0 |
| 57 | 12 | 0 |
| 61 | 12 | 0 |
| 62 | 13 | 0 |
| 63 | 13 | 0 |
+-----------+------------------+-----------+


posts

+---------+--------------+-----------------------+------------------------+
| ID | post_author | post_date | post_title |
+---------+--------------+-----------------------+------------------------+
| 59 | 8 | 2015.11.30 9.33.05 | Strerding silver |
| 57 | 8 | 2015.11.30 9.34.45 | London Blue |
| 61 | 8 | 2015.11.30 9.39.34 | Strerding silver Ame |
| 62 | 9 | 2015.11.30 9.50.15 | Clean water |
| 63 | 9 | 2015.11.30 9.55.55 | 5 Liter water |
+---------+--------------+-----------------------+------------------------+


I would like to create a JSON output from those to tables to look like this:

{
"category": [{
"term_id": "12",
"name": "jewellery",
"slug": "jewellery",
"products": [{
"ID": "59",
"post_title": "Strerding silver",
"post_date": "2015.11.30 9.33.05",
"post_author": "8"
}, {
"ID": "57",
"post_title": "London Blue",
"post_date": "2015.11.30 9.34.45",
"post_author": "8"
}]
}, {

"term_id": "13",
"name": "water",
"slug": "water",
"products": [{
"ID": "62",
"post_title": "Clean water",
"post_date": "2015.11.30 9.50.15",
"post_author": "9"
}, {
"ID": "63",
"post_title": "5 Liter water",
"post_date": "2015.11.30 9.55.55",
"post_author": "9"
}]
}]
}


I am using PHP and mysql_query methods to figure out the logic, Here is the code I've tried so far.

<?php
$username = "root";
$password = "";
$hostname = "localhost";

$response = array();

$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");

$selected = mysql_select_db("look4com_lk", $dbhandle)
or die("Could not select look4com_lk");

//execute the SQL query and return records
$result = mysql_query("select *
from l4wlk_terms
INNER JOIN l4wlk_term_relationships
ON l4wlk_term_relationships.term_taxonomy_id = l4wlk_terms.term_id
INNER JOIN l4wlk_posts
ON l4wlk_term_relationships.object_id = l4wlk_posts.ID
ORDER BY l4wlk_terms.name");

//$response["infos"] = array();
$info["categorylist"] = array();

while ($row = mysql_fetch_assoc($result)) {
$arr = array();
$arr["name"] = $row["name"];
$arr["term_id"] = $row["term_id"];
$arr["post_date"] = $row["post_date"];
$arr["post_title"] = $row["post_title"];
$info[] = $arr;
}
echo json_encode($info);
//close the connection
mysql_close($dbhandle);
?>

Answer

Check the below code block. remove from //$response["infos"] = array(); to echo json_encode($info); and paste the below code.

    $values = array();
    while ($row = mysql_fetch_assoc($result)) {
        // if term_id info not saved in the array, save it.
        if (!isset($values[$row["term_id"]])) {
            $values[$row["term_id"]] = array(
                'term_id' => $row["term_id"], 
                'name' => $row["name"], 
                'slug' => $row["slug"]);
        }
        // save products under term_id.
        $values[$row["term_id"]]['products'][] = 
            array('ID' => $row["ID"], 'post_title' => $row["post_title"], 
                'post_date' => $row["post_date"], 'post_author' => $row["post_author"]);
    }
    // removing term_ids and adding all values to a array called 'category'
    $return_array['category'] = array_values($values);

    echo json_encode($return_array);