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 */ ] ]; }