Loke
Reputation: 63
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);
?>
Upvotes: 1
Views: 102
Answers (3)
Mohammedshafeek C S
Reputation: 1943
PHP CODE
<?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");
$result = mysql_query("select *
from l4wlk_terms
INNER JOIN l4wlk_term_relationships
ON l4wlk_term_relationships.term_taxonomy_id = l4wlk_terms.term_id GROUP BY l4wlk_terms.term_id
ORDER BY l4wlk_terms.name");
while ($row = mysql_fetch_assoc($result)) {
$arr = array();
$arr["term_id"] = $row["term_id"];
$arr["name"] = $row["name"];
$arr["slug"] = $row["slug"];
$result1 = mysql_query("select *
from l4wlk_term_relationships
INNER JOIN l4wlk_posts
ON l4wlk_term_relationships.object_id = l4wlk_posts.ID WHERE l4wlk_term_relationships.term_taxonomy_id = '".$arr["term_id"]."'");
while ($row1 = mysql_fetch_assoc($result1)) {
$arr1 = array();
$arr1["ID"] = $row1["ID"];
$arr1["post_title"] = $row1["post_title"];
$arr1["post_date"] = $row1["post_date"];
$arr1["post_author"] = $row1["post_author"];
$arr["products"][] = $arr1;
}
$info["category"][] = $arr;
}
echo json_encode($info);
//close the connection
mysql_close($dbhandle);
?>
SQL DUMP
--
-- Database: `look4com_lk`
--
--
-- Table structure for table `l4wlk_posts`
--
CREATE TABLE IF NOT EXISTS `l4wlk_posts` (
`ID` int(11) unsigned NOT NULL,
`post_author` int(11) unsigned NOT NULL,
`post_date` datetime(6) NOT NULL,
`post_title` varchar(250) NOT NULL,
UNIQUE KEY `ID_2` (`ID`),
KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `l4wlk_posts`
--
INSERT INTO `l4wlk_posts` (`ID`, `post_author`, `post_date`, `post_title`) VALUES
(57, 8, '2015-11-30 09:34:45.000000', 'London Blue'),
(59, 8, '2015-11-30 09:33:05.000000', 'Strerding silver'),
(61, 8, '2015-11-30 09:39:34.000000', 'Strerding silver Ame'),
(62, 9, '2015-11-30 09:50:15.000000', 'Clean water'),
(63, 9, '2015-11-30 09:55:55.000000', '5 Liter water');
-- --------------------------------------------------------
--
-- Table structure for table `l4wlk_terms`
--
CREATE TABLE IF NOT EXISTS `l4wlk_terms` (
`term_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL,
`slug` varchar(250) NOT NULL,
`term_group` varchar(250) NOT NULL,
PRIMARY KEY (`term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
--
-- Dumping data for table `l4wlk_terms`
--
INSERT INTO `l4wlk_terms` (`term_id`, `name`, `slug`, `term_group`) VALUES
(12, 'jewellery', 'jewellery', '0'),
(13, 'water', 'water', '0');
-- --------------------------------------------------------
--
-- Table structure for table `l4wlk_term_relationships`
--
CREATE TABLE IF NOT EXISTS `l4wlk_term_relationships` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`object_id` int(11) unsigned NOT NULL,
`term_taxonomy_id` int(11) unsigned NOT NULL,
`term_order` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `object_id` (`object_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
--
-- Dumping data for table `l4wlk_term_relationships`
--
INSERT INTO `l4wlk_term_relationships` (`id`, `object_id`, `term_taxonomy_id`, `term_order`) VALUES
(1, 59, 12, 0),
(2, 57, 12, 0),
(3, 61, 12, 0),
(4, 62, 13, 0),
(5, 63, 13, 0);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `l4wlk_term_relationships`
--
ALTER TABLE `l4wlk_term_relationships`
ADD CONSTRAINT `l4wlk_term_relationships_c1` FOREIGN KEY (`object_id`) REFERENCES `l4wlk_posts` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `l4wlk_term_relationships_c2` FOREIGN KEY (`term_taxonomy_id`) REFERENCES `l4wlk_terms` (`term_id`) ON DELETE CASCADE ON UPDATE CASCADE;
Upvotes: 0
DarkKnight
Reputation: 661
In pure SQL it will be very hard to do this. Using PHP you can do this. You need to build the array from the mysql resultset according to your need.
/* Building PreResult Set, form here we build the required result set */
$info["categorylist"] = array();
while ($row = mysql_fetch_assoc($result)) {
$info["categorylist"][$row["term_id"]]["name"] = $row["name"];
$info["categorylist"][$row["term_id"]]["term_id"] = $row["term_id"];
$info["categorylist"][$row["term_id"]]["slug"] = $row["slug"];
$post = array();
$post["id"] = $row["id"];
$post["post_date"] = $row["post_date"];
$post["post_title"] = $row["post_title"];
$post["post_author"] = $row["post_author"];
$info["categorylist"][$row["term_id"]]["products"][] = $post;
}
/* Building Actual Result Set from PreResult Set, This step required to remove all associative key is used before in PreSet to remove redundant data*/
$json_data = array();
foreach($info["categorylist"] as $key => $value){
$products_array = array();
foreach($value["products"] as $product_keys => $products){
$products_array[] = $products;
}
$category = array();
$category["name"] = $value["name"];
$category["term_id"] = $row["term_id"];
$category["slug"] = $row["slug"];
$category["products"] = $products;
$json_data["categorylist"][] = $category;
}
echo '<pre>'.print_r($json_data,1).'</pre>';
echo '<pre>'.json_encode($json_data).'</pre>';
Upvotes: 0
John Fonseka
Reputation: 795
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);
Upvotes: 2
Related Questions
- How do I check if an array includes a value in JavaScript?
- Loop (for each) over an array in JavaScript
- How can I remove a specific item from an array in JavaScript?
- Can comments be used in JSON?
- Deleting an element from an array in PHP
- Reference Guide: What does this symbol mean in PHP? (PHP Syntax)
- Why does Google prepend while(1); to their JSON responses?
- How to insert an item into an array at a specific index?
- Which JSON content type do I use?
- Create ArrayList from array