dongqiong8021
2018-10-24 14:00
浏览 217

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

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

?>
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • donglu8334 2018-10-24 14:03
    已采纳

    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.

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题