Importing JSON into MySQL with PHP

In the previous tutorial, you created a 
security_events table. In this tutorial, you will read data from a JSON file and import it into MySQL using PHP.
The JSON file contains devices and events. Each event will be inserted into the 
security_events table as a separate database record.
Project Structure
Your project should contain:
json-import
├── import_json.php
└── smart_security_data.json
Copy the JSON file into your project folder.
Create the Import Script
Create a new file called:
import_json.php
Add the following code:
<?php
$conn = new mysqli(
    "localhost",
    "root",
    "",
    "project_db"
);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Database connection successful.";
?>
Test the Database Connection
Open:
http://localhost/json-import/import_json.php
You should see:
Database connection successful.
Read the JSON File
Add the following code underneath the database connection:
$json = file_get_contents(
    "smart_security_data.json"
);
echo $json;
Refresh the page.
The contents of the JSON file should be displayed in the browser.
Convert the JSON into a PHP Array
Replace:
echo $json;
with:
$data = json_decode(
    $json,
    true
);
print_r($data);
Refresh the page.
You should now see a PHP array structure.
Loop Through the Devices
Replace:
print_r($data);
with:
foreach ($data["devices"] as $device) {
    echo "<h3>";
    echo $device["deviceId"];
    echo "</h3>";
}
Refresh the page.
You should see:
CAM-01
LOCK-02
Loop Through the Events
Replace the previous code with:
foreach ($data["devices"] as $device) {
    foreach ($device["events"] as $event) {
        echo $event["eventType"];
        echo "<br>";
    }
}
Refresh the page.
You should see:
motion_detected
remote_access_attempt
unlock_attempt
Prepare the INSERT Statement
Add the following code before the loops:
$stmt = $conn->prepare(
    "INSERT INTO security_events (
        device_id,
        device_type,
        room,
        event_timestamp,
        event_type,
        data_transmitted,
        severity,
        access_result
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
);
This statement will be reused for every event.
Insert the Events
Replace the existing loops with:
foreach ($data["devices"] as $device) {
    foreach ($device["events"] as $event) {
        $stmt->bind_param(
            "ssssssss",
            $device["deviceId"],
            $device["deviceType"],
            $device["room"],
            $event["timestamp"],
            $event["eventType"],
            $event["dataTransmitted"],
            $event["severity"],
            $event["accessResult"]
        );
        $stmt->execute();
    }
}
Display a Success Message
Add:
echo "Import completed successfully.";
after the loops.
The completed import should now run automatically when the page loads.
Run the Import
Open:
http://localhost/json-import/import_json.php
You should see:
Import completed successfully.
Verify the Imported Data
Open phpMyAdmin.
Run:
SELECT * FROM security_events;
You should now see three imported events from the JSON file.
Example:
event_id
device_id
event_type
severity
1
CAM-01
motion_detected
high
2
CAM-01
remote_access_attempt
critical
3
LOCK-02
unlock_attempt
medium
Complete import_json.php File
<?php
$conn = new mysqli(
    "localhost",
    "root",
    "",
    "project_db"
);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$json = file_get_contents(
    "smart_security_data.json"
);
$data = json_decode(
    $json,
    true
);
$stmt = $conn->prepare(
    "INSERT INTO security_events (
        device_id,
        device_type,
        room,
        event_timestamp,
        event_type,
        data_transmitted,
        severity,
        access_result
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
);
foreach ($data["devices"] as $device) {
    foreach ($device["events"] as $event) {
        $stmt->bind_param(
            "ssssssss",
            $device["deviceId"],
            $device["deviceType"],
            $device["room"],
            $event["timestamp"],
            $event["eventType"],
            $event["dataTransmitted"],
            $event["severity"],
            $event["accessResult"]
        );
        $stmt->execute();
    }
}
echo "Import completed successfully.";
?>
You have successfully imported JSON data into MySQL using PHP.
Next tutorial: Querying Imported Data with SQL.