# 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:

```text
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:

```text
import_json.php
```

Add the following code:

```php
<?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:

```text
http://localhost/json-import/import_json.php
```

You should see:

```text
Database connection successful.
```


## Read the JSON File

Add the following code underneath the database connection:

```php
$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.

[![](https://mr.napper.au/uploads/images/gallery/2026-06/scaled-1680-/image-1780973965623.png)](https://mr.napper.au/uploads/images/gallery/2026-06/image-1780973965623.png)

## Convert the JSON into a PHP Array

Replace:

```php
echo $json;
```

with:

```php
$data = json_decode(
    $json,
    true
);

print_r($data);
```

Refresh the page.

You should now see a PHP array structure.

[![](https://mr.napper.au/uploads/images/gallery/2026-06/scaled-1680-/image-1780973996586.png)](https://mr.napper.au/uploads/images/gallery/2026-06/image-1780973996586.png)

## Loop Through the Devices

Replace:

```php
print_r($data);
```

with:

```php
foreach ($data["devices"] as $device) {

    echo "<h3>";
    echo $device["deviceId"];
    echo "</h3>";

}
```

Refresh the page.

You should see:

```text
CAM-01

LOCK-02
```

## Loop Through the Events

Replace the previous code with:

```php
foreach ($data["devices"] as $device) {

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

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

    }

}
```

Refresh the page.

You should see:

```text
motion_detected
remote_access_attempt
unlock_attempt
```



## Prepare the INSERT Statement

Add the following code before the loops:

```php
$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:

```php
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:

```php
echo "Import completed successfully.";
```

after the loops.

The completed import should now run automatically when the page loads.

---

## Run the Import

Open:

```text
http://localhost/json-import/import_json.php
```

You should see:

```text
Import completed successfully.
```


## Verify the Imported Data

Open phpMyAdmin.

Run:

```sql
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
<?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**.