inserting data from nested arrays into mysql using php

sauutmhj 于 2022-10-22 发布在 Mysql

关注(0)|答案(3)|浏览(99)

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

mysql

来源：https://stackoverflow.com/questions/39835853/inserting-data-from-nested-arrays-into-mysql-using-php

3条答案

按热度按时间

q43xntqr1#

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

赞(0）回复(0）举报 2022-10-22

xytpbqjk2#

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']) ."');";

赞(0）回复(0）举报 2022-10-22

ergxz8rk3#

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 ).

赞(0）回复(0）举报 2022-10-22

我来回答

inserting data from nested arrays into mysql using php

3条答案

相关问题

热门标签

最新问答