Php inserts first record only to MySQL from JSon file

Question

I created the JSon file using the c# to export the selected records from the local MySQL database. The php code is used to insert the JSon file recods to the MySQL remote database. But the problem is, when I run the php code, it only inserts the first record from the JSon to the database rather than all records in the JSon file.

But I want to insert all the records to the database.

table : tbl_sales
the id field is AutoIncrement

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

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

    $data = file_get_contents($filename); // 2.
    $array = json_decode($data, true); // 3. 

    foreach($array as $row) // 4. 
    {
      $query .= "INSERT INTO tbl_sales(sale_item, sale_qty, local_row_added_on, last_edited_on) VALUES ('".$row["sale_item"]."', '".$row["sale_qty"]."', '".$row["local_row_added_on"]."', '".$row["last_edited_on"]."'); ";  // 5. 
      mysqli_query($connect, $query); // 6.         
    }

    echo "<h1>Successfully Imported JSON Data</h1>"; 
    // 1. Connect PHP to MySQL Database
    // 2. Read the JSON file in PHP
    // 3. Convert JSON String into PHP Array
    // 4. Extract the Array Values by using Foreach Loop
    // 5. Make Multiple Insert Query 
    // 6. Run Mutliple Insert Query
?>

JSon file records(path.json) :

[
{
    "sale_item":"Sugar",
    "sale_qty":"5",
    "local_row_added_on":"2018-05-08 10:10:24",
    "last_edited_on":"2018-05-08 10:10:24",
    "id":"1"
},
{
    "sale_item":"Keyboard",
    "sale_qty":"2",
    "local_row_added_on":"2018-05-07 08:14:41",
    "last_edited_on":"2018-05-07 06:14:53",
    "id":"2"
},
{
    "sale_item":"Biscuit",
    "sale_qty":"3",
    "local_row_added_on":"2018-05-06 12:15:17",
    "last_edited_on":"2018-05-06 12:15:35",
    "id":"3"
},
{
    "sale_item":"Pen",
    "sale_qty":"25",
    "local_row_added_on":"2018-05-14 03:20:22",
    "last_edited_on":"2018-05-14 03:20:25",
    "id":"4"
},
{
    "sale_item":"Snacks",
    "sale_qty":"6",
    "local_row_added_on":"2018-05-07 05:30:40",
    "last_edited_on":"2018-05-16 05:30:40",
    "id":"5"}
]

Try echoing any value in your loop and check how many times the loop is running. — 5eeker
– 5eeker, Commented May 16, 2018 at 6:11
please can use var_dump($array); and give us the output of that ? — Kishen Nagaraju
– Kishen Nagaraju, Commented May 16, 2018 at 6:12
You're appending all queries to $query so it includes multiple queries. mysqli_query() only executes one query, so only the first is used. Instead of running the query on each iteration, you could build one multi-insert query: INSERT INTO table (col1, col2) VALUES (row1val1, row1val2), (row2val1, row2val2), ... and so on. — M. Eriksson
– M. Eriksson, Commented May 16, 2018 at 6:15
And you should really use Prepared Statements instead of concatenating your queries like that, since it can lead to all sorts of escaping issues. — M. Eriksson
– M. Eriksson, Commented May 16, 2018 at 6:19
@Magnus Eriksson: Thank you. I changed the mysqli_connect to mysqli_multi_connect. — S.Sakthybaalan
– S.Sakthybaalan, Commented May 16, 2018 at 6:21

Nigel Ren · Accepted Answer · 2018-05-16 06:14:42Z

2

When building your query, you are using

$query .=

This adds the content onto the previous value. The first time this is OK as there is no previous content. Second time it adds a new insert onto the old one and will fail. Remove the . to just set a new statement.

$query =

You should also look into prepared statements and bind variables, this helps protect from SQL injection and can aid in other ways.

answered May 16, 2018 at 6:14

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Domenik Reitzner Over a year ago

I would also recommend unsing PDO instead of MYSQLI, but either way. PLEASE use a prepare statement for your own sake :)

Nigel Ren Over a year ago

@DomenikReitzner, prepared statements is the main point, the choice of API is not as important (IMHO).

Domenik Reitzner Over a year ago

Yes I do agree an that. PDO might help to migrate later on to a different type of db (maybe mssql or sqlite...)

Omar Abu Omar · Accepted Answer · 2018-05-16 06:32:10Z

1

Please try this, Your query is :

INSERT INTO tbl_sales(sale_item, sale_qty, local_row_added_on, 
last_edited_on) VALUES ('Sugar', '5', '2018-05-08 10:10:24', '2018-05-08 
10:10:24'); 
INSERT INTO tbl_sales(sale_item, sale_qty, local_row_added_on, 
last_edited_on) VALUES ('Keyboard', '2', '2018-05-07 08:14:41', '2018-05-07 
06:14:53'); 
INSERT INTO tbl_sales(sale_item, sale_qty, local_row_added_on, 
last_edited_on) VALUES ('Biscuit', '3', '2018-05-06 12:15:17', '2018-05-06 
12:15:35'); 
INSERT INTO tbl_sales(sale_item, sale_qty, local_row_added_on, 
last_edited_on) VALUES ('Pen', '25', '2018-05-14 03:20:22', '2018-05-14 
03:20:25'); 
INSERT INTO tbl_sales(sale_item, sale_qty, local_row_added_on, 
last_edited_on) VALUES ('Snacks', '6', '2018-05-07 05:30:40', '2018-05-16 
05:30:40');

and you should execute your query after the foreach loop:

$query='';
foreach($array as $row) // 4. 
{
  $query .= "INSERT INTO tbl_sales(sale_item, sale_qty, local_row_added_on, last_edited_on) VALUES ('".$row["sale_item"]."', '".$row["sale_qty"]."', '".$row["local_row_added_on"]."', '".$row["last_edited_on"]."'); ";  // 5. 

}
$result = mysqli_multi_query($connect,$query ) or die("Unable to insert: //6".mysql_error());

edited May 16, 2018 at 6:32

answered May 16, 2018 at 6:23

Omar Abu Omar

605 bronze badges

3 Comments

M. Eriksson Over a year ago

mysqli_query() doesn't execute multiple queries. You need to use mysqli_multi_query() for that. But as I pointed out in my comments to the original question, there's no need for a multi query since you can insert multiple rows in one single query.

Omar Abu Omar Over a year ago

Thank you for your feedback I have edited the code, you're totally right :).

S.Sakthybaalan Over a year ago

@OmarAbuOmar Abu Omar Thank you so much

Collectives™ on Stack Overflow

Php inserts first record only to MySQL from JSon file

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related