Inserting Nested JSON into mySQL using Php

Question

I'm trying to insert nested JSON data received from tmdb API into my mySQL database.

I know how to insert an nested JSON already, but since the new record that I need to create will have many to many relation (movie and movie genres) I'm assuming I have to insert multiple rows with of the same data with different Movie genres.

I've been trying to use another foreach loop for movie genre with no success. I would really appreciate some help.

<?php

$api = "https://api.themoviedb.org/3/movie/popular?api_key=<<api-key>>&language=en-US&page=1";
$jsondata = file_get_contents($api);
//now you have result as associative array
$data = json_decode($jsondata, true);
$array_data = $data['results'];
$array_genre= $data['results']['genre'];//

include "indexmoviedb.php";
$stmt=$db->prepare("INSERT INTO movie VALUES(:title,:poster_path,:backdrop_path,:rating,:overview,:genre)");

foreach($array_data as $row){
foreach ($array_genre as $genrerow) {// I'm assuming I have to do something like this

$stmt->bindParam(":title",$row['title'] );
$stmt->bindParam(":poster_path",$row['poster_path'] );
$stmt->bindParam(":backdrop_path",$row['backdrop_path'] );
$stmt->bindParam(":rating",$row['vote_average'] );
$stmt->bindParam(":overview",$row['overview'] );
$stmt->bindParam(":genre",$row['genres_ids'][] );// what goes in here?
$stmt->execute();
}
}

 ?>

this is the json file that I'm trying to insert

{
  "results": [{
    "id": 351286,
    "vote_average": 6.6,
    "title": "Jurassic World: Fallen Kingdom",
    "popularity": 228.669,
    "poster_path": "/c9XxwwhPHdaImA2f1WEfEsbhaFB.jpg",
    "genre_ids": [
      28,
      12,
      878
    ],
    "backdrop_path": "/gBmrsugfWpiXRh13Vo3j0WW55qD.jpg"
  }]
}

The square brackets in your json indicates an array. Unlike the PHP definition of an array, it is indexed by integer. So you need to change $data['results']['genre'] to $data['results'][0]['genre'] — frz3993
– frz3993, Commented Jul 21, 2018 at 16:54

Jakumi · Accepted Answer · 2018-07-21 18:33:46Z

First, your line:

$array_genre= $data['results']['genre'];//

doesn't work as expected, since this key doesn't exist. you use it again in

foreach ($array_genre as $genrerow) { //...

where instead you would probably want:

foreach ($row['genre_ids'] as $genre_id) {

    // all the other bindings
    $stmt->bindParam(":genre",$genre_id )
}

assuming, that you want to insert one row for each genre. otherwise your database schema needs some more explanation ... and it could probably mean a new question ...

the foreach($row['genre_ids'] ... iterates over the genre_ids of that particular row, which the json_decode turned into an array for you.

However, I assume your database scheme could be improved. Turning one row into multiple rows is often a bad idea, unless you have ways of dealing with it. Usual approaches are: special database fields (array, json) or a join table that links movies to genres (containing only genre_id and movie_id or something). - Both of which tackle the normalization problem, which at this point might be a minor nuisance but could get more annoying fast.

Collectives™ on Stack Overflow

Inserting Nested JSON into mySQL using Php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related