Using PHP to insert array into MySQL database? [duplicate]

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

Answer 1

• 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();
}

Answer 2

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.