inserting data from nested arrays into mysql using php

Question

i have this nested array of json data and i am trying to insert specific data into the MYSQL database. However im getting an error and i simply don't know what is wrong with my code. Sorry still new to php/mysql. Any help is appreciated

Here is the json array:

[
{
"title": "â˜… (Blackstar)",
"artist": "David Bowie",
"year": "2016",
"genre": "Jazz",
"media": [
{
"totalDiscs": "1",
"position": "1",
"tracks": [
{
"title": "â˜… (Blackstar)",
"number": "1",
"artists": []
},
{
"title": "'Tis A Pity She Was A Whore",
"number": "2",
"artists": []
},
{
"title": "Lazarus",
"number": "3",
"artists": []
},
{
"title": "Sue (Or In A Season Of Crime)",
"number": "4",
"artists": []
},
{
"title": "Girl Loves Me",
"number": "5",
"artists": []
},
{
"title": "Dollar Days",
"number": "6",
"artists": []
},
{
"title": "I Can't Give Everything Away",
"number": "7",
"artists": []
}
]
}
],
"score": 1
}
]

Here is my code:

$json = json_decode($result, true);

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "4tracks";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} else {
    //echo "connected <br/>";
}   



$sql = "INSERT INTO tracks (artist_name)
VALUES ('".$json[0]['artist']."')";

    if (array_key_exists('genre',$json[0])){
        $sql = "INSERT INTO tracks (track_genre)
            VALUES ('".$json[0]['genre']."')";

    }

    foreach($json[0]['media'] as $key => $values){


        foreach($values['tracks'] as $key1 => $values1) {
            $sql .= "INSERT INTO tracks (track_name)
                VALUES ('".$values1['title']."')";


        }
}



if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

Here is the output when i run the .php on wamp:

Error: INSERT INTO tracks (artist_name) VALUES ('David Bowie'); INSERT INTO tracks (track_genre) VALUES ('Jazz');

INSERT INTO tracks (track_name) VALUES ('★ (Blackstar)');

INSERT INTO tracks (track_name) VALUES (''Tis A Pity She Was A Whore');

INSERT INTO tracks (track_name) VALUES ('Lazarus');

INSERT INTO tracks (track_name) VALUES ('Sue (Or In A Season Of Crime)');

INSERT INTO tracks (track_name) VALUES ('Girl Loves Me');

INSERT INTO tracks (track_name) VALUES ('Dollar Days');

INSERT INTO tracks (track_name) VALUES ('I Can't Give Everything Away');

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO tracks (track_genre) VALUES ('Jazz');INSERT INTO tracks (track_n' at line 2---

Where are you executing? Are you using a function that supports multi query execution? You should also use parameterized queries, you never know if a song/artist will have a quote in it. — chris85, Oct 03 '16 at 16:19
DB Scheme note, I'd think you'd want a reference to the artist in `track_name`. — chris85, Oct 03 '16 at 16:30
As @chris85 says, your query is actually a bunch of queries strung together as one. Notice it is spitting at the start of the second query. Also, you may consider normalising your data structure in the db. — , Oct 03 '16 at 16:56
Where are you converting the JSONString into a PHP data structure? i.e. json_decode()? [Minimal, Complete and Verifiable example](http://stackoverflow.com/help/mcve) — RiggsFolly, Oct 03 '16 at 23:06
@RiggsFolly I have edited my post. Hopefully my question is much clearer now — David Teh, Oct 04 '16 at 07:19
I have written an answer now. There is quite a lot to digest, I hope it is not all too much — RiggsFolly, Oct 04 '16 at 09:54

RiggsFolly · Answer 1 · 2022-10-22T10:59:57.217

The most obvious issue is that you are building one string with multiple queries in it. While executing multiple queries is possible using mysqli_ it is not done using the ->query() method, and would be simpler to execute each query independantly.

Also you are writing one query per column in the tracks table, when you can INSERT multiple columns to a table at the same time in one query.

Then you will need to loop around your JSONdata structure using a number of loops, the foreach loop is best for this purpose.

Also if you use parameterized queries, the issues of quotes in a string like "title": "'Tis A Pity She Was A Whore" will automatically be taken care of for your.

So I suggest this as a solution

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "4tracks";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    echo "Connection failed: " . $conn->connect_error;
    exit;
}

$j = file_get_contents('tst.json');

$json = json_decode($j);
if (json_last_error() != 0) {
    echo json_last_error_msg();
}

// Notice we prepare the query ONCE, but later execute it many times
// with different data in the parameters

$sql = "INSERT INTO tracks (artist_name, track_genre, track_name) VALUES (?,?,?)";
$stmt = $conn->prepare($sql);
// check the prepare worked, if not report errors and exit
if (! $stmt) {
    echo $conn->error;
    exit;
}
// bind the variables names to the ? place holders
// the variables at this point do not have to exists, or have data in them
$stmt->bind_param('sss', $artist, $genre, $title);


foreach($json as $cd) {

    foreach($cd->media as $media) {

        foreach($media->tracks as $track){

            // load the bound variables with the data for this insert execution
            $artist = $cd->artist;
            $genre = $cd->genre;
            $title = $track->title;

            $result = $stmt->execute();
            // check the insert worked, if not report error
            if (!$result) {
                echo $conn->error;
                exit;
            }
        }
    }
}

The code worked after a few minor adjustment ! Learned so much from this. Thank you sir. Cheers ! — David Teh, Oct 05 '16 at 04:42

score -2 · Answer 2 · answered Oct 04 '16 at 00:46

-2

INSERT INTO tracks (track_name) VALUES (''Tis A Pity She Was A Whore');

The 'Tis - you need to escape that single quote.

$sql = "INSERT INTO tracks (artist_name)
VALUES ('". addslashes ($json[0]['artist']) ."');";

answered Oct 04 '16 at 00:46

Sonam Gurung

117
1
3

Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Oct 04 '16 at 08:14
Please add some explanation to your answer such that others can learn from it. How does escaping resolve the initial problem? – Nico Haase Oct 22 '22 at 11:07

score -2 · Answer 3 · edited May 23 '17 at 11:48

-2

Escaping will make sure no special symbols (like the " symbols in JSON) will be parsed by MySQL.

To escape your query, use $mysqli->real_escape_string($my_json);.

Always escape whatever you try to insert into a database. Or even better - use parameterized or prepared statements (read more here).

edited May 23 '17 at 11:48

Community

1
1

answered Oct 04 '16 at 07:17

Jared

2,978
4
26
45

Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Oct 04 '16 at 08:13
The advise is not good and you missed so many of the errors the OP was making – RiggsFolly Oct 04 '16 at 09:17
Please add some explanation to your answer such that others can learn from it. How does escaping resolve the initial problem? – Nico Haase Oct 22 '22 at 11:07

inserting data from nested arrays into mysql using php

3 Answers3