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.