Importing JSON into MySQL with PHP

InThis thistutorial tutorial, you will read data fromimports a fictional JSON file and import itdataset into MySQL using PHP.PHP, PDO, validation and a prepared statement.

Use fictional data only. Do not import real student, staff, account, device or school-security information.

Project files

Create this structure inside your XAMPP project:

project/
├── includes/
│   └── database.php
├── smart_security_data.json
└── import_json.php

includes/database.php should create the shared $pdo connection used throughout the project. Keep connection details in that one file rather than repeating them on every page.

Understand the expected data

The fictional JSON file contains homes, devices and nested events. Each event will bebecome insertedone intorow thein security_events.

table

Before asimporting, identify the fields your code expects. A typical event needs:

a separatedevice databaseidentifier record.and

---

type; a room; an event timestamp and type; the data transmitted; severity; and the access result.

ProjectRead Structure

and

Your project should contain:

json-import
├── import_json.php
└── smart_security_data.json

Copydecode the JSON file into your project folder.

Create the Import Script

Create a new file called:

import_json.php

Add the following code:

<?php
declare(strict_types=1);

require __DIR__ . '/includes/database.php';

$connjsonPath = new__DIR__ mysqli(. "localhost",
    "root",
    "",
    "project_db"
)'/smart_security_data.json';

if (!is_readable($conn->connect_error)jsonPath)) {
    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.

exit('The contents of thefictional JSON file shouldcould not be displayedread.'); in} thetry browser.

{

Convert the JSON into a PHP Array

Replace:

echo $json;

with:

$data = json_decode(
        file_get_contents($json,jsonPath),
        truetrue,
        512,
        JSON_THROW_ON_ERROR
    );
print_r($data);} 

Refresh the page.

You should now see a PHP array structure.

Loop Through the Devices

Replace:

print_r($data);

with:

foreachcatch ($data["devices"] asJsonException $device)error) {
    echoexit('The "<h3>";JSON echostructure $device["deviceId"];is echo "</h3>"invalid.');
}

RefreshJSON_THROW_ON_ERROR theprevents page.

malformed

YouJSON shouldfrom see:

being

CAM-01

LOCK-02

Loop Through the Events

Replace the previous code with:

foreach ($data["devices"]treated as $device)valid {

    foreach ($device["events"] as $event) {

        echo $event["eventType"];
        echo "<br>";

    }

}

Refresh the page.data.

You should see:

motion_detected
remote_access_attempt
unlock_attempt

Prepare theone INSERTreusable Statementinsert

Add the following code before the loops:

$stmtinsert = $conn-pdo->prepare(
    "'INSERT INTO security_events (
        device_id,
        device_type,
        room,
        event_timestamp,
        event_type,
        data_transmitted,
        severity,
        access_result
    ) VALUES (?,
        ?,:device_id,
        ?,:device_type,
        ?,:room,
        ?,:event_timestamp,
        ?,:event_type,
        ?,:data_transmitted,
        ?:severity,
        :access_result
    )"'
);

ThisPreparing the statement willonce be reused for every event.

---

Insertseparates the EventsSQL instructions from each row's values.

Validate and import each event

Replace the existing loops with:

$allowedSeverities = ['low', 'medium', 'high', 'critical'];
$allowedResults = ['authorised', 'denied', 'blocked'];
$inserted = 0;
$rejected = 0;

$pdo->beginTransaction();

try {
    foreach (($data["devices"'devices'] ?? []) as $device) {
        foreach (($device["events"'events'] ?? []) as $event) {
            $stmt->bind_param(deviceId "ssssssss",= trim((string) ($device["deviceId"],
            $device["deviceType"],
            $device["room"],

            $event["timestamp"],
            $event["eventType"],
            $event["dataTransmitted"],
            $event["severity"],
            $event["accessResult"'deviceId'] ?? ''));
            $stmt-deviceType = trim((string) ($device['deviceType'] ?? ''));
            $room = trim((string) ($device['room'] ?? ''));
            $timestamp = trim((string) ($event['timestamp'] ?? ''));
            $eventType = trim((string) ($event['eventType'] ?? ''));
            $transmitted = trim((string) ($event['dataTransmitted'] ?? ''));
            $severity = strtolower(trim((string) ($event['severity'] ?? '')));
            $accessResult = strtolower(trim((string) ($event['accessResult'] ?? '')));

            $validTimestamp = DateTime::createFromFormat(
                'Y-m-d H:i:s',
                $timestamp
            ) !== false;

            if (
                $deviceId === '' ||
                $deviceType === '' ||
                $room === '' ||
                $eventType === '' ||
                !$validTimestamp ||
                !in_array($severity, $allowedSeverities, true) ||
                !in_array($accessResult, $allowedResults, true)
            ) {
                $rejected++;
                continue;
            }

            $insert->execute([
                'device_id' => $deviceId,
                'device_type' => $deviceType,
                'room' => $room,
                'event_timestamp' => $timestamp,
                'event_type' => $eventType,
                'data_transmitted' => $transmitted,
                'severity' => $severity,
                'access_result' => $accessResult
            ]);

            $inserted++;
        }
    }

    $pdo->commit();
} catch (Throwable $error) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    exit('The import could not be completed.');
}

---

The

Displaytransaction prevents a Successdatabase Messageerror from leaving an uncertain partial import. Row validation rejects unsuitable values before storage.

Report the result

Add:

echo "Import'<p>' completed. successfully."$inserted . ' fictional events imported.</p>';
echo '<p>' . $rejected . ' rows rejected.</p>';

afterFor a classroom prototype, report useful totals without exposing database errors or connection details to the loops.user.

The completed import should now run automatically when

Check the pageimport

loads.

Confirm that:

---

Run

valid events are stored once; invalid values are rejected; the prepared statement is reused; a failed transaction is rolled back; the result totals match the Importdatabase;

Open:

and

http://localhost/json-import/import_json.phponly 

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 JSONfictional data intowas MySQLprocessed. using PHP.