将同一项目id的多行MY SQL查询结果合并为json响应的单行

Question

我是PHP和Wordpress的新手。我正试图在Wordpress上建立一个自定义的API。我有一个My Sql查询，它使用INNER JOIN连接2个表，并为同一项Id返回多行。然后我试图将结果集转换为JSON响应。

问题是，即使Id相同，我也会为每个项目Id获取一个新的JSON对象。

请参阅下面的My SQL查询：

SELECT id, title, answer from wp_tb1 wp1 INNER JOIN wp_tb2 wp2 ON wp1.belongs_id = wp2.id

Php代码：

$data=array();
$count=0;
foreach($list as $l){
$data[$count]=array(
"id" =>$l->id,
"title"=>$l->title,
"answer"=> array($l->title),
);
++$count;
}

JSON结果看起来像：

"[{"id":"1","title":"Title 1","answer":"True"},{"id":"1","title":"Title 1","answer":"False"}]"

正如您所看到的，id值是重复的，Title也是。我希望响应类似

"[{"id":"1","title":"Title 1","answer":{"True","False"}}]"

查询或Php代码级别的任何帮助都将非常有用。

Answer 1

将循环更改为：

$data = [];
foreach ($list as $l) {
$data[$l->id]['id'] = $l->id;
$data[$l->id]['title'] = $l->title;
$data[$l->id]['answer'][] = $l->answer;
}
$data = array_values($data);

这将按照id和title对嵌套数组进行分组(我假设标题对于相同的id是相同的(。

$data现在将包含

[{"id":"1","title":"Title 1","answer":["True","False"]}]

参见演示

Answer 2

这里有一个如何创建正确json:的示例

方法1:

try {
// Try Connect to the DB with new MySqli object - Params {hostname, userid, password, dbname}
$mysqli = new mysqli("localhost", "root", "", "mysqli_examples");

$statement = $mysqli->prepare("select id, title, answer from table limit 10");

$statement->execute(); // Execute the statement.
$result = $statement->get_result(); // Binds the last executed statement as a result.
echo json_encode(($result->fetch_assoc())); // Parse to JSON and print.
} catch (mysqli_sql_exception $e) { // Failed to connect? Lets see the exception details..
echo "MySQLi Error Code: " . $e->getCode() . "<br />";
echo "Exception Msg: " . $e->getMessage();
exit(); // exit and close connection.
}
$mysqli->close(); // finally, close the connection

输出：

{
"id": "1",
"title": "Yes pHP",
"answer": "True",
}

方法2:

$return_arr = array();
$fetch = $db->query("SELECT * FROM table"); 
while ($row = $fetch->fetch_array()) {
$row_array['id'] = $row['id'];
$row_array['col1'] = $row['col1'];
$row_array['col2'] = $row['col2'];
array_push($return_arr,$row_array);
}
echo json_encode($return_arr);

输出：

[{"id":"1","col1":"col1_value","col2":"col2_value"},{"id":"2","col1":"col1_value","col2":"col2_value"}]

Answer 3

如果您的selection总是按ID排序，并且主要目标是收集带有答案的邻居记录，那么您可以使用下一个if statement:

$data = json_decode($data);
$data2=array();
$count=0;
foreach($data as $l){ 
if ($count % 2 == 0){ 
$data2[$count]=array(
"id" =>$l->id,
"title"=>$l->title,
);
$data2[$count]['answer'] = [];
$data2[$count]['answer'][] = $l->answer;
} else {
$data2[$count-1]['answer'][] = $l->answer;
}
$count++;  
}
print_r(json_encode($data2,JSON_PRETTY_PRINT));

演示

输出为：

[
{
"id": "1",
"title": "Title 1",
"answer": [
"True",
"False"
]
}
]

如果没有订购ID，那么您应该使用另一个代码。