Skip to content
Advertisement

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

Need to perform couple of mysqli queries and add one result into existing result array, currently I have implemented first query,

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

The result is like,

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

Now I want to perform another query,

“SELECT * FROM directors_table WHERE director_id = $dir_id”

and add the result into json response as director object,

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

Advertisement

Answer

Use a JOIN in your query:

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

And build the array structure in your loop:

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 */
        ]
    ];
}
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement