php - Mysql查询加入条件

Question

这是我的数据库表，我如何查询，以便我可以获得如下所需的输出。我可以使用单个查询语句查询数据库以获得 json 输出吗？？？

 Table name: rowManager
    +-------------+-----------------+----------------+
    | rowid       |      rowname    | showid         |
    +-------------+-----------------+----------------+
    |           1 | first           |              0 |
    |          17 | second          |              2 |
    |          18 | third           |              0 |
    |          20 | forth           |              0 |
    +-------------+-----------------+----------------+
    Table name: row_vid
    +-------------+----------------+-----------------+-----------------+
    | rowid       | name           |     description | submission_date |
    +-------------+----------------+-----------------+-----------------+
    |           1 | Learn PHP      | abavavaav       | 2007-05-24      |
    |          17 | Learn MySQL    | sdasdsa         | 2007-05-24      |
    |          20 | JAVA Script    | Sanjay          | 2007-05-06      |
    |           1 | JAVA           | Sanjay          | 2007-05-06      |
    |          18 | Android        | Sanjay          | 2007-05-06      |
    |          17 | ios            | Sanjay          | 2007-05-06      |
    |           1 | python         | Sanjay          | 2007-05-06      |
    |          18 | c++            | Sanjay          | 2007-05-06      |
    |          18 | c#             | Sanjay          | 2007-05-06      |
    |          17 | ruby           | Sanjay          | 2007-05-06      |
    |          18 | JQuery         | Sanjay          | 2007-05-06      |
    |          17 | objective c    | Sanjay          | 2007-05-06      |
    |           1 | JAVA Tutorial  | Sanjay          | 2007-05-06      |
    +-------------+----------------+-----------------+-----------------+



$rowQuery='SELECT * from row_vid,rowManagr  where rowManagr.showid=0';

我正在尝试查询这些表，以便它给我如下输出:

{
   "responses":{
      "First":[
         {
            "name":"Learn PHP"

         },
         {
            "name":"JAVA"

         },
         {
            "name":"python"

         },
         {
            "name":"JAVA Tutorial"
         }
      ],
      "Other Show":[
         {
            "name":"Learn MySQL"
         },
         {
            "name":"ios"
         },
         {
            "name":"ruby",

         },
         {
            "name":"objective c"
         }
      ],
      "Videos":[
         {
            "name":"Android"
         },
         {
            "name":"c++ "
         },
         {
            "name":"c#",

         },
         {
            "name":"JQuery"
         }
      ]
   }
}

谢谢

Answer 1

首先，我相信这是您正在寻找的查询:

SELECT   rowManager.rowname as category, row_vid.name as name 
FROM     rowManager, row_vid
WHERE    rowManager.rowid=row_vid.rowid
ORDER BY rowManager.rowid, row_vid.name

在 PHP 中，您可以这样做:

$sql = "SELECT rowManager.rowname as category, row_vid.name as name FROM rowManager, row_vid WHERE rowManager.rowid=row_vid.rowid ORDER BY rowManager.rowid, row_vid.name"

$query_result = mysqli_query($sql);

// Piece together the $responses array, which will have [category]
// as key, while value is an array of 'name':[name]' pairs.
$responses = array();
while ($row = mysqli_fetch_assoc($query_result)) {
  if (!array_key_exists($row['category'], $responses)) {
    $responses[$row['category']] = array();
  }
  array_push($responses[$row['category']], array('name' => $row['name']));
}

// Entire $response array is the value of 'responses' key in the
// final $results array.
$results = array('responses' => $responses);

// Convert $results_array to JSON and print.
print(json_encode($results));