JSON数组三个PHP中的mysql表

检查下面的代码块。从//$response["infos"] = array();移除到echo json_encode($info);，并粘贴以下代码。

    $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);

在纯SQL中很难做到这一点。使用PHP可以做到这一点。您需要根据需要从mysql结果集构建数组。

    /* 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>';

PHP代码

<?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转储

--
-- 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;