Php doesn't INSERT or ON DUPLICATE KEY UPDATE at once from JSon to MySQL

Question

I want to insert the new records from Json file to the database or update the existing one with new values. I have the MySQL table(tbl_sales) as follows where id is primarykey:

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| sale_item       | varchar(20) | NO   |     | NULL    |       |
| sale_qty        | int(11)     | NO   |     | NULL    |       |
| row_inserted_on | datetime    | NO   |     | NULL    |       |
| last_edited_on  | datetime    | YES  |     | NULL    |       |
| id              | int(11)     | NO   | PRI | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

I have the data in JSon file which to be inserted or updated to this table using php

php code :

<?php
    $connect = mysqli_connect("localhost", "root", "", "mytest");
    $query = '';
    $table_data = '';
    $filename = "path.json";

    $data = file_get_contents($filename);
    $array = json_decode($data, true); 

    foreach($array as $row) 
    {
      $query = "INSERT INTO tbl_sales(sale_item, sale_qty, row_inserted_on, last_edited_on, id) VALUES ('".$row["sale_item"]."', '".$row["sale_qty"]."', '".$row["row_inserted_on"]."', '".$row["last_edited_on"]."', '".$row["id"]."'); ON DUPLICATE KEY UPDATE sale_item=VALUES('".$row["sale_item"]."'), sale_qty=VALUES('".$row["sale_qty"]."'), row_inserted_on=VALUES('".$row["row_inserted_on"]."'), last_edited_on=VALUES('".$row["last_edited_on"]."')";     

      mysqli_multi_query($connect, $query);       
    }
   echo "<h1>All data appeded </h1>";
?>

Json file :

[
{
    "sale_item":"Sugar",
    "sale_qty":"3",
    "row_inserted_on":"2018-05-06 05:25:30",
    "last_edited_on":"2018-05-09 05:26:26",
    "id":"0"
},
{
    "sale_item":"Doll",
    "sale_qty":"300",
    "row_inserted_on":"2018-05-08 07:32:12",
    "last_edited_on":"2018-05-10 06:29:18",
    "id":"1"
},
{
    "sale_item":"Chocolate",
    "sale_qty":"4",
    "row_inserted_on":"2018-05-09 11:31:19",
    "last_edited_on":"2018-05-09 11:31:19",
    "id":"2"
},
{
    "sale_item":"Biscuit",
    "sale_qty":"5",
    "row_inserted_on":"2018-05-10 10:17:10",
    "last_edited_on":"2018-05-10 10:17:10",
    "id":"3"}
]

The problem is, When I run the php file it does not insert or update all the data in JSon file at once. Every record from the JSon file is inserted to the database in each run time.

score 0 · Answer 1 · answered May 16 '18 at 10:49

There are some changes you need to do with the logic, refer the following changes made :

<?php

try{
    $connect = mysqli_connect("localhost", "root", "", "mytest");    
    $query = '';
    $table_data = '';
    $filename = "path.json";

    $data = file_get_contents($filename);
    $array = json_decode($data, true); 

    foreach($array as $row) 
    {
      $query .= "INSERT INTO tbl_sales(sale_item, sale_qty, row_inserted_on, last_edited_on, id) VALUES ('".$row["sale_item"]."', '".$row["sale_qty"]."', '".$row["row_inserted_on"]."', '".$row["last_edited_on"]."', '".$row["id"]."') ON DUPLICATE KEY UPDATE sale_item='".$row["sale_item"]."', sale_qty='".$row["sale_qty"]."', row_inserted_on='".$row["row_inserted_on"]."', last_edited_on='".$row["last_edited_on"]."';";
    }

    mysqli_multi_query($connect, $query);  

   echo "<h1>All data appeded </h1>";
} catch(Exception $e){

echo $e->getMessage();    
}
?>

`@Kawaljeet Singh` Thank you for your reply. I got the followings : **1.** Added try catch block **2.** Concatenation assignment **3.** Removed semi-colon and `VALUES` keyword **4.** Changed `mysqli_multi_connect` — S.Sakthybaalan, May 16 '18 at 15:37

Php doesn't INSERT or ON DUPLICATE KEY UPDATE at once from JSon to MySQL

1 Answers1