将JSON数组插入MySQL数据库不能正确循环

Question

I am trying to insert some data from a JSON file into a SQL database.

I have written this script to reads the JSON, decodes it and inserts it, however I am only getting the first JSON line as I don't think it is looping properly.

I am also getting a duplicate error, which I believe is caused as the code is trying to loop the same line, not the next line in the JSON! (Only the first JSON item inserts)

How do I get my script to loop each line in my JSON and insert it?

Thanks!

Code I am working with:

<?php
error_reporting(E_ALL);
$root = $_SERVER['DOCUMENT_ROOT'];
require ($root."/config.php");

$json = file_get_contents('propertiesA.json');
$obj = json_decode($json,true);

foreach($obj as $item) {
    $query =  "INSERT INTO unverified_list (
         title,
         street_address,
         locality
       ) VALUES (
         '".$item['title']."',
         '".$item['street_address']."',
         '".$item['locality']."'

       )"; 
}

$result = $link->query($query);

if ($link->query($query) === TRUE) {
    echo "Property Added";
} else  {
  echo "Error: " . $query . "<br>" . $link->error;
}

mysqli_close($link);

?>

Answer 1

The problem is that you only execute the query after the loop has finished, you need to run it on each line...

foreach($obj as $item) {
       $query =  "INSERT INTO unverified_list (
         title,
         street_address,
         locality
       ) VALUES (
         '".$item['title']."',
         '".$item['street_address']."',
         '".$item['locality']."'

       )"; // Remove } from here to after next bit of code

       $result = $link->query($query);

       if ($link->query($query) === TRUE) {
           echo "Property Added";
       } else  {
           echo "Error: " . $query . "<br>" . $link->error;
       }
}

Although this will now give you a message for each row inserted.

You should also look into using prepared statements which will protect you from all sorts of potential issues.