Using PHP to insert array into MySQL database?

Question

Here's my current code:

// connect to database  
$con = mysqli_connect("localhost","username","password","database");
if (!$con) {
    die('Could not connect: ' . mysqli_error());
}

// get the json file
$jsondata = file_get_contents('http://www.example.com');

// convert json to php array
$data = json_decode($jsondata, true);

// assign each of the variables
$id = $data['0']['id'];
$name = $data['0']['name'];
$status = $data['0']['status'];

$insertstatement = mysqli_query($con,"INSERT INTO `table` (`id`, `name`, `status`) VALUES ('".$id."', '".$name."', '".$status."');");

Technically this code is working, but it's only adding the first item.

For example, here's the original json that's being imported:

[
   {
      "id":"19839",
      "status":"active",
      "name":"First Name",
   },
   {
      "id":"19840",
      "status":"active",
      "name":"Second Name",
   },
   {
      "id":"19841",
      "status":"active",
      "name":"Another Name",
   },
   {
      "id":"19842",
      "status":"active",
      "name":"Last Name",
   }
]

My code would only be inserting this into the database:

{
 "id":"19839",
 "status":"active",
 "name":"First Name",
}

How do I make it loop through all of them and insert all of the rows? Also is there, a way to insert them in reverse order (starting from the last one, ending at the first one)?

score 8 · Accepted Answer · answered Oct 02 '16 at 15:39

To iterate over array you have to use foreach operator.
To perform multiple inserts you have to use prepared statements

So despite what in all other hastily written answers said, the code should be

$stmt = $con->prepare("INSERT INTO `table` (`id`, `name`, `status`) VALUES (?,?,?)");
$stmt->bind_param("sss", $id, $name, $status);
foreach ($data as $row)
{
    $id = $row['id'];
    $name = $row['name'];
    $status = $row['status'];
    $stmt->execute();
}

score 1 · Answer 2 · edited Feb 14 '22 at 13:46

In order to insert multiple rows of data or array of the data you have to iterate upon the array variable using the foreach() so that it will loop you by single row so that you can perform the needed operation that you want to perform.

You can insert the data using mysqli.* or PDO or by using prepared statements. And it is up to you who can decide of under which scenario you are going to insert the data.

MYSQLI with Prepared Statements:

<?php
$query = $con->prepare("INSERT INTO `table` (`id`, `name`, `status`) VALUES (?,?,?)");
$query->bind_param("sss", $id, $name, $status);
foreach ($data as $row)
{
    $id = $row['id'];
    $name = $row['name'];
    $status = $row['status'];
    $result->execute();// This will execute the statement. 
}
?>

PDO with Prepared Statements:

<?php
$link = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbusername,$dbpassword);
$statement = $link->prepare("INSERT INTO testtable(name, lastname, age)
    VALUES(:id, :name, :status)");
foreach($data as $row)
{
$statement->execute(array(
    "id" => $row['id'],
    "name" => $row['name'],
    "status" => $row['status']
));
}
?>

Hope so my explanations would be clear for better understanding of all the three type of statements.

score -2 · Answer 3 · edited May 23 '17 at 10:27

With a foreach loop:

foreach($data as $index => $row){
    $insertstatement = mysqli_query($con,"INSERT INTO `table` (`id`, `name`, `status`) VALUES ('". $row['id'] ."', '". $row['name'] ."', '". $row['status'] ."');");
}

However please note that you should be careful when inserting any data from untrusted sources, since it leaves you vulnerable to SQL injections. Learn read more about SQL injection here: How can I prevent SQL injection in PHP?.

score -2 · Answer 4 · answered Oct 02 '16 at 15:30

You should use the MySql multiple row inserts.

INSERT INTO tbl_name (col1,col2) VALUES(1,2), (3,4);

MySql insert reference: http://dev.mysql.com/doc/refman/5.7/en/insert.html

In php, you would do something like (modified your code)

// connect to database  
$con = mysqli_connect("localhost","username","password","database");
if (!$con) {
    die('Could not connect: ' . mysqli_error());
}

// get the json file
$jsondata = file_get_contents('http://www.example.com');

// convert json to php array
$data = json_decode($jsondata, true);

$query = "INSERT INTO `table` (`id`, `name`, `status`) VALUES ";

foreach($data as $d) {
$query .= "('".$d['id']."', '".$d['name']."', '".$d['status']."'),";
}
// Query. Note the trim to remove the last ,
$insertstatement = mysqli_query($con, trim($query,',');

You can also continue reading from ON DUPLICATE KEY... & other useful additions to the above. You should also make sure your data is correctly escaped / from a 100% safe source. If status contains something like "'; TRUNCATE TABLE..." unescaped, you're potentially in trouble.

EDIT: The two other solutions also work and insert all rows. My version however inserts all rows in one query. Depending on how large your data is, it might not be feasible to run - say - 1000 queries instead of one.

you forgot to format your SQL properly. Which will lead to wide range of undesirable results - from syntax errors to sql injection. However "truncate table" query in particular will never cause no trouble. — Your Common Sense, Oct 02 '16 at 15:48

Using PHP to insert array into MySQL database?

4 Answers4