1

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);
?>
Improve this question
1
  • This is very hard to achieve in pure SQL, since the mysql driver of php doesn't support arrays and such. in order to achieve what you described above, you'd need to pre-aggregate the result of some intermediate steps, where you then could join concatenated strings to a certain result set (in your case group) and so on. Commented May 4, 2016 at 7:10

3 Answers 3

Reset to default
2

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Now will show like this error :........ Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in C:\wamp\www\youtubewebservice\shop-categorylist-product.php on line 15
It's a warning. It is saying that instead of using mysql_* functions, better to use mysqli_* functions. Mysqli is newer and better way to connect and interact with MySQL. Most of the time you can use it same as mysql_* functions. php.net/manual/en/book.mysqli.php
0

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>';
Improve this answer

Comments

0

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;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.