如何执行两个mysqli查询并将一个结果添加到现有的结果数组中

Question

需要执行两个mysqli查询，并将一个结果添加到现有的结果数组中，目前我已经实现了第一个查询，

$dataQuery = "SELECT * FROM movies_table";
$sth = mysqli_query($conn, $dataQuery);
$rows = array();
while($r = mysqli_fetch_assoc($sth)) {
$rows[] = $r;
}
$respObj->status = 'success';
$respObj->movies = $rows;

$respJSON = json_encode($respObj);
print $respJSON;

结果是，

{
"status": "success",
"movies": [
{
"id": "8",
"image": "image-url-here",
"language": "english",
"title": "avengers",
"year": "2005",
"dir_id": "152"
}
]
}

现在我想执行另一个查询，

"SELECT*FROM directors_table WHERE director_id＝$dir_id">

，并将结果添加到json响应中作为director对象，

{
"status": "success",
"movies": [
{
"id": "8",
"image": "image-url-here",
"language": "english",
"title": "avengers",
"year": "2005",
"director": {
"id": "152",
"name": "director",
"age": 50
}
}
]
}

Answer 1

在查询中使用JOIN：

SELECT * 
FROM movies_table m 
INNER JOIN directors_table d ON d.director_id = m.dir_id

并在循环中构建阵列结构：

while($r = mysqli_fetch_assoc($sth)) {
$rows[] = [
'id' => $r['id'],
'image' => $r['image'],
/* other movie keys you need */
'director' => [
'id' => $r['director_id'],
/* other director keys you need */
]
];
}

Answer 2

两种解决方案

1. 像@AymDev在你的问题的第一条评论中建议的那样加入。如果您的表相对较小并且没有任何性能问题，这可能是首选解决方案

2. 双重查询

// First retrieve all the directors and keep an array with their info. The Key of the array is the director ID
$dataQuery = "SELECT * FROM directors_table";
$sth = mysqli_query($conn, $dataQuery);
$directors = array();
while($r = mysqli_fetch_assoc($sth)) {
$directors[$r['id']] = $r;
}
$dataQuery = "SELECT * FROM movies_table";
$sth = mysqli_query($conn, $dataQuery);
$rows = array();

while($r = mysqli_fetch_assoc($sth)) {
// Retrieve the director info from the previous array
$r['director'] = $directors[$r['dir_id']];
$rows[] = $r;
}

$respObj->status = 'success';
$respObj->movies = $rows;


$respJSON = json_encode($respObj);

print $respJSON;