如何执行几个mysqli查询并将一个结果添加到现有结果数组中？-解网

问：

需要执行几个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"
    }
  ]
}

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

“从directors_table中选择 * director_id = $dir_id”

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

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

php mysql json mysqli

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

1赞 AymDev 5/31/2021 #2

在查询中使用 a：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 */
        ]
    ];
}

上一个：从数据库创建 JSON 文件

下一个：php 中 json 数据中的嵌套数组

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

How to perform couple of mysqli queries and add one result into existing result array?

评论