CODEWITHSUNDEEP
phpmysqljsonmysqli
Kavin-K
Kavin-K

Reputation: 2117

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
      }
    }
  ]
}

Upvotes: 0

Views: 34

Answers (2)

AymDev
AymDev

Reputation: 7539

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

Upvotes: 1

Andrea Olivato
Andrea Olivato

Reputation: 2545

Two solutions

  1. Make a JOIN like @AymDev suggested in the first comment to your question. This might be the preferred solution if your tables are relatively small and you don't have any performance issues

  2. Double query

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

Upvotes: 0

Related Questions