从嵌套数组插入数据到MySQL使用PHP

Question

我有这个JSON数据的嵌套数组，我试图插入特定的数据到MYSQL数据库。但即时通讯出现错误，我根本不知道我的代码有什么问题。对不起，对PHP/MySQL仍然陌生。任何帮助表示赞赏

这里是JSON数组：

[ 
{ 
"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 
} 
] 

这里是我的代码：

$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(); 

这里是输出当我运行WAMP .php为：

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

Answer 1

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

'Tis - 你需要逃脱t帽子单引号。

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

Answer 2

逃逸将确保没有特殊的符号（如在JSON的“符号）将MySQL的解析。

逃避你的查询，使用$mysqli->real_escape_string($my_json);。

始终逃脱不管你尝试插入到数据库中，甚至更好 - 使用参数化或准备好的语句（阅读更多here）

Answer 3

最明显的问题是您正在构建一个包含多个查询的字符串。 eries有可能使用mysqli_它没有使用​​方法完成，并且可以更简单地独立执行每个查询。

此外，您可以在tracks表中为每列写入一个查询，此时您可以在一个查询中同时将多个列插入到表中。

然后，您将需要使用多个循环来循环您的JSON数据结构，foreach循环最适合此目的。

另外你也可以使用参数化查询，像"title": "'Tis A Pity She Was A Whore"这样的字符串中的引号问题会自动处理好。

所以我的建议是作为解决

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