JSON到mysql只插入最后一个记录

Question

我试图插入JSON MySQL数据库，我已经成功地访问JSON的每一个元素，但我的代码只插入JSON的最后一个记录，这是我的PHP代码：

<?php 

$link = mysqli_connect("localhost", "root", "", "Store"); 

if($link === false){ 
    die("ERROR: Could not connect. " . mysqli_connect_error()); 
} 

    $filename = "employee_data.json"; 
$json = file_get_contents($filename); 
$obj = json_decode($json,true); 


    foreach($obj as $item) { 
$sql = "INSERT INTO product (barcodeNumber,name,catName) VALUES ('".$item['name']."', '".$item['designation']."', '".$item['gender']."') "; 

echo $item['name']; 
    } 

if(mysqli_query($link, $sql)){ 
    echo "Records added successfully."; 
} else{ 
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); 
} 

mysqli_close($link); 
?> 

正如你在我的代码中看到的，我可以打印json的每个名字，但它只插入最后一个！

而且JSON文件 employee_data.json：

[ 
    { 
    "name": "Michael Bruce", 
    "gender": "Male", 
    "designation": "System Architect" 
    }, 
    { 
    "name": "Jennifer Winters", 
    "gender": "Female", 
    "designation": "Senior Programmer" 
    }, 
    { 
    "name": "Donna Fox", 
    "gender": "Female", 
    "designation": "Office Manager" 
    }, 
    { 
    "name": "Howard Hatfield", 
    "gender": "Male", 
    "designation": "Customer Support" 
    }, 
    { 
    "name": "Kevin Sanders", 
    "gender": "Male", 
    "designation": "Food service worker" 
    }, 
    { 
    "name": "Fay K. Whitney", 
    "gender": "Female", 
    "designation": "Bookbinder" 
    }, 
    { 
    "name": "Heather Hernandez", 
    "gender": "Female", 
    "designation": "Corporate recruiter" 

    }, 
    { 
    "name": "Rhonda Okelley", 
    "gender": "Female", 
    "designation": "Image designer" 
    }, 
    { 
    "name": "Sandy Jacobsen", 
    "gender": "Male", 
    "designation": "Publicity agent" 
    }, 
    { 
    "name": "Marisa Williams", 
    "gender": "Female", 
    "designation": "Molder" 
    }, 
    { 
    "name": "Neil Simmons", 
    "gender": "Male", 
    "designation": "Engine and other machine assembler" 
    }, 
    { 
    "name": "Raymond Lara", 
    "gender": "Male", 
    "designation": "Clerical assistant" 
    }, 
    { 
    "name": "William Carpenter", 
    "gender": "Male", 
    "designation": "Broker" 
    }, 
    { 
    "name": "Ted Baer", 
    "gender": "Male", 
    "designation": "Conservation worker" 
    } 
] 

它插入只有最后一条记录：

{ 
     "name": "Ted Baer", 
     "gender": "Male", 
     "designation": "Conservation worker" 
     } 

任何帮助吗？

Answer 1

因为您在循环之后插入记录。试试这个代码：

foreach($obj as $item) { 
    $sql = "INSERT INTO product (barcodeNumber,name,catName) VALUES ('".$item['name']."', '".$item['designation']."', '".$item['gender']."') "; 

    if(mysqli_query($link, $sql)){ 
     echo "Records added successfully."; 
    } else{ 
     echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); 
    } 
} 

UPDATE

更好的方法是不是插入的每个记录在罗布泊使用批量插入：

$query = "INSERT INTO product (barcodeNumber,name,catName) VALUES " 

foreach($obj as $item) { 
    $query_values[] = "('".$item["name"]."', '".$item["designation"]."', '".$item["gender"]."')"; 
} 

// Implode the query values array with a coma and execute the query. 
$db->query($query . implode(',',$query_values));