Jessica Jessica - 2 months ago 8
MySQL Question

Using PHP to insert array into MySQL database?

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

Any help would be greatly appreciated :)

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