Working with Data

Tutorials for importing, processing, querying and presenting fictional data with PHP, MySQL, JavaScript and charts.

Importing JSON into MySQL with PHP

Create a MySQL structure, import fictional JSON records with PHP and query the stored data.

Importing JSON into MySQL with PHP

Creating a Security Events Table

In this tutorial, you will create a MySQL table to store security event data imported from a JSON file.

The JSON file contains homes, devices and events. Rather than storing the entire JSON structure, each event will be stored as a separate row in a database table.

This approach makes it easier to search, filter and analyse the data using SQL.

Open phpMyAdmin

Open phpMyAdmin in your browser.

Example:

http://localhost/phpmyadmin/

Select your existing database:

project_db

Create the Security Events Table

Select the SQL tab and run:

CREATE TABLE security_events (

    event_id INT AUTO_INCREMENT PRIMARY KEY,

    device_id VARCHAR(50) NOT NULL,

    device_type VARCHAR(100) NOT NULL,

    room VARCHAR(100) NOT NULL,

    event_timestamp DATETIME NOT NULL,

    event_type VARCHAR(100) NOT NULL,

    data_transmitted VARCHAR(100) NOT NULL,

    severity VARCHAR(20) NOT NULL,

    access_result VARCHAR(20) NOT NULL

);

This creates a table capable of storing all event information from the JSON file.

Review the Table Structure

The completed table should contain the following fields:

Field Purpose
event_id Unique identifier for each event
device_id Device that generated the event
device_type Type of device
room Location of the device
event_timestamp Date and time of the event
event_type Type of activity
data_transmitted Data involved in the event
severity Event severity level
access_result Result of the event

View the Table Structure

Run:

DESCRIBE security_events;

You should see a structure similar to:

Field Type
event_id int
device_id varchar(50)
device_type varchar(100)
room varchar(100)
event_timestamp datetime
event_type varchar(100)
data_transmitted varchar(100)
severity varchar(20)
access_result varchar(20)

Insert a Test Record

Before importing JSON data, insert a test record to verify the table works correctly.

Run:

INSERT INTO security_events (

    device_id,
    device_type,
    room,
    event_timestamp,
    event_type,
    data_transmitted,
    severity,
    access_result

)
VALUES (

    'CAM-01',
    'Security Camera',
    'Front Entrance',
    '2026-03-14 02:18:45',
    'motion_detected',
    'video_stream',
    'high',
    'authorised'

);

If successful, MySQL will report:

1 row inserted

View the Data

Run:

SELECT * FROM security_events;

You should see:

event_id device_id device_type room event_timestamp event_type severity
1 CAM-01 Security Camera Front Entrance 2026-03-14 02:18:45 motion_detected high

Delete the Test Record

The test record is no longer required.

Run:

DELETE FROM security_events;

Verify the table is empty:

SELECT * FROM security_events;

You should see:

Empty set

Complete SQL Script

CREATE TABLE security_events (

    event_id INT AUTO_INCREMENT PRIMARY KEY,

    device_id VARCHAR(50) NOT NULL,

    device_type VARCHAR(100) NOT NULL,

    room VARCHAR(100) NOT NULL,

    event_timestamp DATETIME NOT NULL,

    event_type VARCHAR(100) NOT NULL,

    data_transmitted VARCHAR(100) NOT NULL,

    severity VARCHAR(20) NOT NULL,

    access_result VARCHAR(20) NOT NULL

);

INSERT INTO security_events (

    device_id,
    device_type,
    room,
    event_timestamp,
    event_type,
    data_transmitted,
    severity,
    access_result

)
VALUES (

    'CAM-01',
    'Security Camera',
    'Front Entrance',
    '2026-03-14 02:18:45',
    'motion_detected',
    'video_stream',
    'high',
    'authorised'

);

SELECT * FROM security_events;

DELETE FROM security_events;

You now have a database table ready to receive event data from a JSON file.

Importing JSON into MySQL with PHP

Importing JSON into MySQL with PHP

This tutorial imports a fictional JSON dataset into MySQL using 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 become one row in security_events.

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

Read and decode the JSON

<?php
declare(strict_types=1);

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

$jsonPath = __DIR__ . '/smart_security_data.json';

if (!is_readable($jsonPath)) {
    exit('The fictional JSON file could not be read.');
}

try {
    $data = json_decode(
        file_get_contents($jsonPath),
        true,
        512,
        JSON_THROW_ON_ERROR
    );
} catch (JsonException $error) {
    exit('The JSON structure is invalid.');
}

JSON_THROW_ON_ERROR prevents malformed JSON from being treated as valid data.

Prepare one reusable insert

$insert = $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
    )'
);

Preparing the statement once separates the SQL instructions from each row's values.

Validate and import each event

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

$pdo->beginTransaction();

try {
    foreach (($data['devices'] ?? []) as $device) {
        foreach (($device['events'] ?? []) as $event) {
            $deviceId = trim((string) ($device['deviceId'] ?? ''));
            $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 transaction prevents a database error from leaving an uncertain partial import. Row validation rejects unsuitable values before storage.

Report the result

echo '<p>' . $inserted . ' fictional events imported.</p>';
echo '<p>' . $rejected . ' rows rejected.</p>';

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

Check the import

Confirm that:

Importing JSON into MySQL with PHP

Querying Imported Data with SQL

In this tutorial, you will use SQL queries to analyse the imported data.

View All Events

Open phpMyAdmin and select the:

security_events

table.

Run:

SELECT *
FROM security_events;

This displays all imported records.

Count All Events

To find the total number of events:

SELECT COUNT(*) AS total_events
FROM security_events;

Example result:

total_events
3

This query is useful for dashboards and reporting.

Find Critical Events

To display only critical events:

SELECT *
FROM security_events
WHERE severity = 'critical';

Example result:

device_id event_type severity
CAM-01 remote_access_attempt critical

Count Events by Severity

Run:

SELECT
    severity,
    COUNT(*) AS total
FROM security_events
GROUP BY severity;

Example result:

severity total
critical 1
high 1
medium 1

This is often used to build dashboard summary cards.

Find Events from a Specific Device

To display events generated by the security camera:

SELECT *
FROM security_events
WHERE device_id = 'CAM-01';

Example result:

device_id event_type
CAM-01 motion_detected
CAM-01 remote_access_attempt

Count Events Per Device

Run:

SELECT
    device_id,
    COUNT(*) AS total_events
FROM security_events
GROUP BY device_id;

Example result:

device_id total_events
CAM-01 2
LOCK-02 1

This identifies which devices are generating the most activity.

Find High-Risk Events

To display high and critical events:

SELECT *
FROM security_events
WHERE severity IN ('high', 'critical');

Example result:

device_id event_type severity
CAM-01 motion_detected high
CAM-01 remote_access_attempt critical

This query could be used to generate security alerts.

Sort Events by Time

To view the newest events first:

SELECT *
FROM security_events
ORDER BY event_timestamp DESC;

This is commonly used in event logs and monitoring systems.

Useful Dashboard Queries

Total Events

SELECT COUNT(*) AS total_events
FROM security_events;

Critical Events

SELECT COUNT(*) AS critical_events
FROM security_events
WHERE severity = 'critical';

Device Count

SELECT COUNT(DISTINCT device_id) AS total_devices
FROM security_events;

Events by Severity

SELECT
    severity,
    COUNT(*) AS total
FROM security_events
GROUP BY severity;

These queries are commonly used when building dashboards.

Complete Practice Queries

SELECT *
FROM security_events;

SELECT COUNT(*) AS total_events
FROM security_events;

SELECT *
FROM security_events
WHERE severity = 'critical';

SELECT
    severity,
    COUNT(*) AS total
FROM security_events
GROUP BY severity;

SELECT
    device_id,
    COUNT(*) AS total_events
FROM security_events
GROUP BY device_id;

SELECT *
FROM security_events
ORDER BY event_timestamp DESC;

You have successfully queried imported JSON data using SQL.

Importing JSON into MySQL with PHP

Building a Security Dashboard Using PHP and MySQL

This standalone example queries fictional security-event data with PDO and presents useful summary and detail output.

A dashboard is meaningful when each output answers a user question. Do not add a statistic or chart merely because it is easy to calculate.

Connect with PDO

Use the shared connection created in includes/database.php:

<?php
declare(strict_types=1);

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

Do not repeat database credentials in the dashboard page.

Query useful summaries

$totalEvents = (int) $pdo
    ->query('SELECT COUNT(*) FROM security_events')
    ->fetchColumn();

$highRiskEvents = (int) $pdo
    ->query(
        "SELECT COUNT(*)
         FROM security_events
         WHERE severity IN ('high', 'critical')"
    )
    ->fetchColumn();

$deniedEvents = (int) $pdo
    ->query(
        "SELECT COUNT(*)
         FROM security_events
         WHERE access_result IN ('denied', 'blocked')"
    )
    ->fetchColumn();

These queries answer three different questions: how much activity exists, how much is high priority, and how often access was prevented.

Retrieve recent records

$recentStatement = $pdo->query(
    'SELECT
        event_timestamp,
        device_id,
        room,
        event_type,
        severity,
        access_result
     FROM security_events
     ORDER BY event_timestamp DESC
     LIMIT 10'
);

$recentEvents = $recentStatement->fetchAll();

Select only the fields needed by the interface.

Present accessible summary output

<section aria-labelledby="summary-heading">
  <h1 id="summary-heading">Security event summary</h1>

  <div class="summary-grid">
    <article class="card">
      <h2>Total events</h2>
      <p><?= $totalEvents ?></p>
    </article>

    <article class="card">
      <h2>High-priority events</h2>
      <p><?= $highRiskEvents ?></p>
    </article>

    <article class="card">
      <h2>Denied or blocked</h2>
      <p><?= $deniedEvents ?></p>
    </article>
  </div>
</section>

Headings and text communicate the meaning without relying on colour alone.

Present recent events safely

<?php if (!$recentEvents): ?>
  <p>No fictional events are available.</p>
<?php else: ?>
  <div class="table-wrap">
    <table>
      <caption>Ten most recent fictional security events</caption>
      <thead>
        <tr>
          <th scope="col">Time</th>
          <th scope="col">Device</th>
          <th scope="col">Room</th>
          <th scope="col">Event</th>
          <th scope="col">Severity</th>
          <th scope="col">Access result</th>
        </tr>
      </thead>
      <tbody>
        <?php foreach ($recentEvents as $event): ?>
          <tr>
            <td><?= htmlspecialchars($event['event_timestamp']) ?></td>
            <td><?= htmlspecialchars($event['device_id']) ?></td>
            <td><?= htmlspecialchars($event['room']) ?></td>
            <td><?= htmlspecialchars($event['event_type']) ?></td>
            <td><?= htmlspecialchars($event['severity']) ?></td>
            <td><?= htmlspecialchars($event['access_result']) ?></td>
          </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  </div>
<?php endif; ?>

htmlspecialchars() prevents stored text from being interpreted as page markup.

Responsive styling

.summary-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(12rem, 1fr));
    gap: 1rem;
}

.table-wrap {
    overflow-x: auto;
}

table {
    width: 100%;
    border-collapse: collapse;
}

th,
td {
    padding: 0.75rem;
    border: 1px solid #cbd5e1;
    text-align: left;
}

Test the dashboard

Confirm that:

Data Visualisation with Google Charts

Present processed SQL data as pie, bar and line charts using Google Charts.

Data Visualisation with Google Charts

Creating Charts with Google Charts

Google Charts is a free JavaScript library that can create professional graphs and charts using your data.

Google Charts can display:

In this tutorial you will create your first chart using sample data.

Step 1 - Load the Google Charts Library

Add the following line inside the <head> section of your webpage.

<script src="https://www.gstatic.com/charts/loader.js"></script>

Step 2 - Create a Chart Container

Add a div where the chart will be displayed.

<div id="chart_div"
     style="width:100%; max-width:900px; min-height:320px;">
</div>

Step 3 - Create the Chart

Add the following JavaScript before the closing </body> tag.

<script>

google.charts.load(
    'current',
    {'packages':['corechart']}
);

google.charts.setOnLoadCallback(drawChart);

function drawChart()
{

    var data =
        google.visualization.arrayToDataTable([

        ['Risk Level', 'Count'],

        ['Low',15],

        ['Medium',8],

        ['High',3]

    ]);


    var options = {

        title:'Security Events by Risk Level'

    };


    var chart =
        new google.visualization.PieChart(

        document.getElementById('chart_div')

    );


    chart.draw(data, options);

}

</script>

image-1782085452882.png

Understanding the Data

Google Charts uses an array to store chart data.

[
 ['Risk Level', 'Count'],

 ['Low',15],

 ['Medium',8],

 ['High',3]
]

The first row contains the headings.

The remaining rows contain the data values.

Changing the Chart Type

The chart type is controlled by:

google.visualization.PieChart

You can change it to:

Chart Class
Pie Chart PieChart
Column Chart ColumnChart
Bar Chart BarChart
Line Chart LineChart
Area Chart AreaChart

Example:

google.visualization.BarChart

will display the same data as a bar chart.

Complete Example

<!DOCTYPE html>

<html>

<head>

    <title>Google Charts Example</title>

    <script src="https://www.gstatic.com/charts/loader.js"></script>

</head>

<body>

<div id="chart_div"
     style="width:100%; max-width:900px; min-height:320px;">
</div>


<script>

google.charts.load(
    'current',
    {'packages':['corechart']}
);

google.charts.setOnLoadCallback(drawChart);

function drawChart()
{

    var data =
        google.visualization.arrayToDataTable([

        ['Risk Level', 'Count'],

        ['Low',15],

        ['Medium',8],

        ['High',3]

    ]);


    var options = {

        title:'Security Events by Risk Level'

    };


    var chart =
        new google.visualization.PieChart(

        document.getElementById('chart_div')

    );


    chart.draw(data, options);

}

</script>

</body>

</html>

Accessibility and data note

Provide the values in an HTML table or concise text summary as well as the chart. Do not rely on colour alone, and give the chart container an accessible name where practical. Google Charts loads JavaScript from Google; use fictional or non-personal summary data only.

Data Visualisation with Google Charts

Pie Charts from SQL Data

In this tutorial, the chart data will come directly from a MySQL database.

The example below uses the cyber_security_events table.

Step 1 - Create the SQL Query

The following query counts how many events belong to each risk level.

SELECT

    risk_level,

    COUNT(*) AS total

FROM cyber_security_events

GROUP BY risk_level;

Example result:

risk_level total
Low 15
Medium 8
High 3

Step 2 - Retrieve the Data with PHP

<?php

$sql = "

SELECT

    risk_level,

    COUNT(*) AS total

FROM cyber_security_events

GROUP BY risk_level

";

$result = $pdo->query($sql);

$data = [];

while($row = $result->fetch())
{

    $data[] = [

        $row['risk_level'],

        (int)$row['total']

    ];

}

?>

Step 3 - Convert the PHP Array to JSON

Google Charts uses JavaScript arrays.

PHP can convert data into JavaScript using:

<?php

echo json_encode($data);

?>

Step 4 - Create the Chart

<script>

google.charts.load(
    'current',
    {'packages':['corechart']}
);

google.charts.setOnLoadCallback(drawChart);

function drawChart()
{

    var data = google.visualization.arrayToDataTable([

        ['Risk Level','Count'],

        ...<?php echo json_encode($data); ?>

    ]);

    var options = {

        title:
        'Security Events by Risk Level'

    };

    var chart =

        new google.visualization.PieChart(

            document.getElementById('chart_div')

        );

    chart.draw(data, options);

}

</script>

Step 5 - Add the Chart Container

<div id="chart_div"
     style="width:100%; max-width:900px; min-height:320px;">
</div>

Complete Example

<?php

$sql = "

SELECT

    risk_level,

    COUNT(*) AS total

FROM cyber_security_events

GROUP BY risk_level

";

$result = $pdo->query($sql);

$data = [];

while($row = $result->fetch())
{

    $data[] = [

        $row['risk_level'],

        (int)$row['total']

    ];

}

?>

<!DOCTYPE html>

<html>

<head>

<script src="https://www.gstatic.com/charts/loader.js"></script>

</head>

<body>

<div id="chart_div"
     style="width:100%; max-width:900px; min-height:320px;">
</div>

<script>

google.charts.load(
    'current',
    {'packages':['corechart']}
);

google.charts.setOnLoadCallback(drawChart);

function drawChart()
{

    var data =

        google.visualization.arrayToDataTable([

        ['Risk Level','Count'],

        ...<?php echo json_encode($data); ?>

    ]);

    var options = {

        title:
        'Security Events by Risk Level'

    };

    var chart =

        new google.visualization.PieChart(

            document.getElementById('chart_div')

        );

    chart.draw(data, options);

}

</script>

</body>

</html>

Accessibility and data note

Provide the values in an HTML table or concise text summary as well as the chart. Do not rely on colour alone, and give the chart container an accessible name where practical. Google Charts loads JavaScript from Google; use fictional or non-personal summary data only.

Data Visualisation with Google Charts

Bar Charts from SQL Data

Bar charts are useful for comparing values between categories.

In this tutorial, the number of security events at each risk level will be displayed as a bar chart.

Step 1 - Create the SQL Query

SELECT

    risk_level,

    COUNT(*) AS total

FROM cyber_security_events

GROUP BY risk_level;

Example result:

risk_level total
Low 15
Medium 8
High 3

Step 2 - Retrieve the Data with PHP

<?php

$sql = "

SELECT

    risk_level,

    COUNT(*) AS total

FROM cyber_security_events

GROUP BY risk_level

";

$result = $pdo->query($sql);

$data = [];

while($row = $result->fetch())
{

    $data[] = [

        $row['risk_level'],

        (int)$row['total']

    ];

}

?>

Step 3 - Create the Chart Container

<div id="chart_div"
     style="width:100%; max-width:900px; min-height:320px;">
</div>

Step 4 - Create the Bar Chart

<script>

google.charts.load(
    'current',
    {'packages':['corechart']}
);

google.charts.setOnLoadCallback(drawChart);

function drawChart()
{

    var data =

        google.visualization.arrayToDataTable([

        ['Risk Level','Events'],

        ...<?php echo json_encode($data); ?>

    ]);


    var options = {

        title:'Security Events by Risk Level',

        legend:{position:'none'}

    };


    var chart =

        new google.visualization.BarChart(

            document.getElementById('chart_div')

        );


    chart.draw(data, options);

}

</script>

image-1782085906217.png

Changing the Chart Type

Only one line of code needs to change.

Pie Chart:

new google.visualization.PieChart()

Bar Chart:

new google.visualization.BarChart()

Column Chart:

new google.visualization.ColumnChart()

Line Chart:

new google.visualization.LineChart()

Complete Example

<?php

$sql = "

SELECT

    risk_level,

    COUNT(*) AS total

FROM cyber_security_events

GROUP BY risk_level

";

$result = $pdo->query($sql);

$data = [];

while($row = $result->fetch())
{

    $data[] = [

        $row['risk_level'],

        (int)$row['total']

    ];

}

?>

<!DOCTYPE html>

<html>

<head>

<script src="https://www.gstatic.com/charts/loader.js"></script>

</head>

<body>

<div id="chart_div"
     style="width:100%; max-width:900px; min-height:320px;">
</div>


<script>

google.charts.load(
    'current',
    {'packages':['corechart']}
);

google.charts.setOnLoadCallback(drawChart);

function drawChart()
{

    var data =

        google.visualization.arrayToDataTable([

        ['Risk Level','Events'],

        ...<?php echo json_encode($data); ?>

    ]);


    var options = {

        title:'Security Events by Risk Level',

        legend:{position:'none'}

    };


    var chart =

        new google.visualization.BarChart(

            document.getElementById('chart_div')

        );


    chart.draw(data, options);

}

</script>

</body>

</html>

Accessibility and data note

Provide the values in an HTML table or concise text summary as well as the chart. Do not rely on colour alone, and give the chart container an accessible name where practical. Google Charts loads JavaScript from Google; use fictional or non-personal summary data only.

Data Visualisation with Google Charts

Line Charts from SQL Data

Line charts are used to display trends and changes over time.

In this tutorial, the number of security events per day will be displayed using a line chart.

Step 1 - Create the SQL Query

The following query counts the number of events recorded each day.

SELECT

    DATE(event_timestamp) AS event_date,

    COUNT(*) AS total

FROM cyber_security_events

GROUP BY DATE(event_timestamp)

ORDER BY event_date;

Example result:

event_date total
2026-06-01 12
2026-06-02 18
2026-06-03 9
2026-06-04 15

Step 2 - Retrieve the Data with PHP

<?php

$sql = "

SELECT

    DATE(event_timestamp) AS event_date,

    COUNT(*) AS total

FROM cyber_security_events

GROUP BY DATE(event_timestamp)

ORDER BY event_date

";

$result = $pdo->query($sql);

$data = [];

while($row = $result->fetch())
{

    $data[] = [

        $row['event_date'],

        (int)$row['total']

    ];

}

?>

Step 3 - Create the Chart Container

<div id="chart_div"
     style="width:100%; max-width:900px; min-height:320px;">
</div>

Step 4 - Create the Line Chart

<script>

google.charts.load(
    'current',
    {'packages':['corechart']}
);

google.charts.setOnLoadCallback(drawChart);

function drawChart()
{

    var data =

        google.visualization.arrayToDataTable([

        ['Date','Events'],

        ...<?php echo json_encode($data); ?>

    ]);


    var options = {

        title:'Security Events Over Time',

        curveType:'function',

        legend:{position:'bottom'}

    };


    var chart =

        new google.visualization.LineChart(

            document.getElementById('chart_div')

        );


    chart.draw(data, options);

}

</script>

Understanding the Chart

The horizontal axis displays:

Date

The vertical axis displays:

Number of Security Events

This makes it easier to identify:

Complete Example

<?php

$sql = "

SELECT

    DATE(event_timestamp) AS event_date,

    COUNT(*) AS total

FROM cyber_security_events

GROUP BY DATE(event_timestamp)

ORDER BY event_date

";

$result = $pdo->query($sql);

$data = [];

while($row = $result->fetch())
{

    $data[] = [

        $row['event_date'],

        (int)$row['total']

    ];

}

?>

<!DOCTYPE html>

<html>

<head>

<script src="https://www.gstatic.com/charts/loader.js"></script>

</head>

<body>

<div id="chart_div"
     style="width:100%; max-width:900px; min-height:320px;">
</div>


<script>

google.charts.load(
    'current',
    {'packages':['corechart']}
);

google.charts.setOnLoadCallback(drawChart);

function drawChart()
{

    var data =

        google.visualization.arrayToDataTable([

        ['Date','Events'],

        ...<?php echo json_encode($data); ?>

    ]);


    var options = {

        title:'Security Events Over Time',

        curveType:'function',

        legend:{position:'bottom'}

    };


    var chart =

        new google.visualization.LineChart(

            document.getElementById('chart_div')

        );


    chart.draw(data, options);

}

</script>

</body>

</html>

Accessibility and data note

Provide the values in an HTML table or concise text summary as well as the chart. Do not rely on colour alone, and give the chart container an accessible name where practical. Google Charts loads JavaScript from Google; use fictional or non-personal summary data only.

Working with APIs

Create PHP JSON APIs and retrieve database records through focused endpoints.

Working with APIs

Creating a Simple JSON API with PHP

In this tutorial, you will create a simple API using PHP.

The API will return JSON data when visited in a browser.

This is the same type of data format used by many real-world APIs.

Project Structure

Create a folder called:

api-demo

Inside the folder place:

api-demo
├── api.php
└── smart_security_data.json

Create the API File

Create:

api.php

Add:

<?php

header(
    "Content-Type: application/json"
);

echo file_get_contents(
    "smart_security_data.json"
);

?>

Save the file.

Understanding the Code

This line tells the browser that JSON data is being returned:

header(
    "Content-Type: application/json"
);

This line reads the JSON file:

file_get_contents(
    "smart_security_data.json"
);

This line sends the JSON data to the browser:

echo file_get_contents(
    "smart_security_data.json"
);

Test the API

Open:

http://localhost/api-demo/api.php

You should see:

{
  "homeId": "GC-HOME-014",
  "location": "Gold Coast"
}

along with the rest of the JSON data.

Test the API in a New Browser Tab

Notice that the URL now behaves like a data source.

Instead of displaying a webpage, it returns structured JSON data.

This is the same concept used by many modern APIs.

Example:

https://example.com/api/users
https://example.com/api/products
https://example.com/api/weather

Complete API File

<?php

header(
    "Content-Type: application/json"
);

echo file_get_contents(
    "smart_security_data.json"
);

?>

You have successfully created a JSON API using PHP.

Working with APIs

Reading API Data with JavaScript

The API returns JSON data when visited in a browser.

In this tutorial, you will use JavaScript to read data from the API and display it on a webpage.

Project Structure

Your project should contain:

api-demo
├── api.php
├── index.html
└── smart_security_data.json

Create the HTML Page

Create:

index.html

Add:

<!DOCTYPE html>
<html>
<head>
    <title>API Demo</title>
</head>
<body>

<h1>API Dashboard</h1>

<div id="output"></div>

<script>

</script>

</body>
</html>

Save the file.

Read the API

Inside the <script> tags add:

fetch("api.php")
    .then(response => response.json())
    .then(data => {

        console.log(data);

    });

Save the file.

Test the API Request

Open:

http://localhost/api-demo/

Press:

F12

Open the Console tab.

You should see the JSON object displayed.

Display the Home Information

Replace:

console.log(data);

with:

document.getElementById(
    "output"
).innerHTML = `

    <p>
        <strong>Home ID:</strong>
        ${data.homeId}
    </p>

    <p>
        <strong>Location:</strong>
        ${data.location}
    </p>

`;

Refresh the page.

You should see:

Home ID: GC-HOME-014

Location: Gold Coast

Display Device Information

Replace the existing code with:

let html = `

    <h2>Devices</h2>

    <table border="1">

        <tr>
            <th>Device ID</th>
            <th>Device Type</th>
            <th>Room</th>
        </tr>

`;

data.devices.forEach(device => {

    html += `

        <tr>
            <td>${device.deviceId}</td>
            <td>${device.deviceType}</td>
            <td>${device.room}</td>
        </tr>

    `;

});

html += "</table>";

document.getElementById(
    "output"
).innerHTML = html;

Refresh the page.

You should now see a table of devices.

Why Use an API?

Previously, the data was loaded directly from:

fetch("smart_security_data.json")

Now the data is loaded from:

fetch("api.php")

This means the data can be:

The webpage does not need to know where the data comes from. It only needs the JSON returned by the API.

Complete Page

<!DOCTYPE html>
<html>
<head>
    <title>API Demo</title>
</head>
<body>

<h1>API Dashboard</h1>

<div id="output"></div>

<script>

fetch("api.php")
    .then(response => response.json())
    .then(data => {

        let html = `

            <h2>Devices</h2>

            <table border="1">

                <tr>
                    <th>Device ID</th>
                    <th>Device Type</th>
                    <th>Room</th>
                </tr>

        `;

        data.devices.forEach(device => {

            html += `

                <tr>
                    <td>${device.deviceId}</td>
                    <td>${device.deviceType}</td>
                    <td>${device.room}</td>
                </tr>

            `;

        });

        html += "</table>";

        document.getElementById(
            "output"
        ).innerHTML = html;

    });

</script>

</body>
</html>

You have successfully consumed data from an API using JavaScript.

Working with APIs

Creating a Database-Driven API with PHP and MySQL

This tutorial creates a read-only JSON endpoint using PHP, PDO and fictional database records.

An API should return only the data needed for its stated purpose. Do not expose password hashes, session data, personal information or unnecessary database fields.

Create the endpoint

Create api/events.php:

<?php
declare(strict_types=1);

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

header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: no-store');

try {
    $statement = $pdo->query(
        'SELECT
            event_id,
            event_timestamp,
            device_id,
            room,
            event_type,
            severity,
            access_result
         FROM security_events
         ORDER BY event_timestamp DESC
         LIMIT 100'
    );

    $events = $statement->fetchAll();

    echo json_encode(
        [
            'ok' => true,
            'count' => count($events),
            'events' => $events
        ],
        JSON_THROW_ON_ERROR | JSON_UNESCAPED_SLASHES
    );
} catch (Throwable $error) {
    http_response_code(500);

    echo json_encode([
        'ok' => false,
        'error' => 'The fictional event data is unavailable.'
    ]);
}

The endpoint:

Test the response

Open the endpoint through XAMPP:

http://localhost/project/api/events.php

A successful response should have this shape:

{
  "ok": true,
  "count": 2,
  "events": [
    {
      "event_id": 14,
      "event_timestamp": "2026-06-14 20:57:06",
      "device_id": "CAM-01",
      "room": "Science Lab",
      "event_type": "failed_login",
      "severity": "medium",
      "access_result": "denied"
    }
  ]
}

The values are fictional. Your live response will reflect the current database.

Important boundary

This example is a public, read-only classroom endpoint. A production API may also require authentication, authorisation, pagination, rate limiting, audit logging and a documented privacy purpose.

Check

Working with APIs

Creating API Endpoints for Specific Data

Focused endpoints return the smallest useful result for a particular user need. This tutorial adds filtered and summary endpoints using PDO and fictional records.

Endpoint for one severity

Create api/events-by-severity.php:

<?php
declare(strict_types=1);

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

header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: no-store');

$allowed = ['low', 'medium', 'high', 'critical'];
$severity = strtolower(trim($_GET['severity'] ?? ''));

if (!in_array($severity, $allowed, true)) {
    http_response_code(400);
    echo json_encode([
        'ok' => false,
        'error' => 'Choose low, medium, high or critical.'
    ]);
    exit;
}

$statement = $pdo->prepare(
    'SELECT
        event_id,
        event_timestamp,
        device_id,
        room,
        event_type,
        severity
     FROM security_events
     WHERE severity = :severity
     ORDER BY event_timestamp DESC
     LIMIT 100'
);

$statement->execute(['severity' => $severity]);
$events = $statement->fetchAll();

echo json_encode([
    'ok' => true,
    'filter' => ['severity' => $severity],
    'count' => count($events),
    'events' => $events
]);

Test it with a fictional category:

http://localhost/project/api/events-by-severity.php?severity=high

The allow-list rejects unexpected values and the prepared statement keeps the supplied value separate from the SQL.

Endpoint for summary totals

Create api/event-summary.php:

<?php
declare(strict_types=1);

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

header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: no-store');

$statement = $pdo->query(
    'SELECT severity, COUNT(*) AS total
     FROM security_events
     GROUP BY severity
     ORDER BY severity'
);

$rows = $statement->fetchAll();

$summary = [];

foreach ($rows as $row) {
    $summary[] = [
        'severity' => $row['severity'],
        'total' => (int) $row['total']
    ];
}

echo json_encode([
    'ok' => true,
    'summary' => $summary
]);

This endpoint returns processed information rather than every event record. It can support an accessible table or chart.

Choose endpoints from user needs

Useful endpoints might answer:

Avoid creating many endpoints that expose the same unnecessary fields.

Check

Working with JSON Data in JavaScript

Load, interpret and present nested fictional JSON data with JavaScript.

Working with JSON Data in JavaScript

Loading JSON Data with JavaScript

In this tutorial, you will load data from a JSON file and display it on a webpage using JavaScript.

The JSON file contains information about a smart home, including devices and security events.

Create the Project Files

Create a new folder called:

json-demo

Inside the folder create:

index.html
smart_security_data.json

Copy the JSON file into the project folder.

Your folder structure should look like:

json-demo
├── index.html
└── smart_security_data.json

Create the HTML Page

Open:

index.html

Add the following code:

<!DOCTYPE html>
<html>
<head>
    <title>JSON Demo</title>
</head>
<body>

<h1>Smart Home Dashboard</h1>

<div id="output"></div>

<script src="script.js"></script>

</body>
</html>

Save the file.

Create the JavaScript File

Create a new file called:

script.js

Your folder should now look like:

json-demo
├── index.html
├── script.js
└── smart_security_data.json

Load the JSON File

Open:

script.js

Add:

fetch("smart_security_data.json")
    .then(response => response.json())
    .then(data => {

        console.log(data);

    });

Save the file.

This code loads the JSON file and converts it into a JavaScript object.

Open Developer Tools

Open:

index.html

in your browser.

Press:

F12

and select the Console tab.

You should see the JSON data displayed.

Display the Home Information

Replace the contents of:

.then(data => {

});

with:

.then(data => {

    document.getElementById("output").innerHTML = `
        <p><strong>Home ID:</strong> ${data.homeId}</p>
        <p><strong>Location:</strong> ${data.location}</p>
    `;

});

Save the file.

Refresh the page.

You should now see:

Home ID: GC-HOME-014
Location: Gold Coast

Display the Devices

Update the code:

fetch("smart_security_data.json")
    .then(response => response.json())
    .then(data => {

        let html = `
            <p><strong>Home ID:</strong> ${data.homeId}</p>
            <p><strong>Location:</strong> ${data.location}</p>

            <h2>Devices</h2>
            <ul>
        `;

        data.devices.forEach(device => {

            html += `
                <li>
                    ${device.deviceId}
                    (${device.deviceType})
                </li>
            `;

        });

        html += "</ul>";

        document.getElementById("output").innerHTML = html;

    });

Refresh the page.

You should now see:

Home ID: GC-HOME-014
Location: Gold Coast

Devices

CAM-01 (Security Camera)
LOCK-02 (Smart Door Lock)

Complete JavaScript File

fetch("smart_security_data.json")
    .then(response => response.json())
    .then(data => {

        let html = `
            <p><strong>Home ID:</strong> ${data.homeId}</p>
            <p><strong>Location:</strong> ${data.location}</p>

            <h2>Devices</h2>
            <ul>
        `;

        data.devices.forEach(device => {

            html += `
                <li>
                    ${device.deviceId}
                    (${device.deviceType})
                </li>
            `;

        });

        html += "</ul>";

        document.getElementById("output").innerHTML = html;

    });

You have successfully loaded a JSON file and displayed data using JavaScript.

Working with JSON Data in JavaScript

Displaying JSON Data in a Table

In this tutorial, you will display the device data in an HTML table using JavaScript.

Current Project Structure

Your project should contain:

json-demo
├── index.html
├── script.js
└── smart_security_data.json

Create a Table Container

Open:

index.html

Update the page so it contains:

<!DOCTYPE html>
<html>
<head>
    <title>JSON Demo</title>
</head>
<body>

<h1>Smart Home Dashboard</h1>

<div id="output"></div>

<script src="script.js"></script>

</body>
</html>

The table will be generated inside the output div.

Create the Table Structure

Open:

script.js

Replace the previous code with:

fetch("smart_security_data.json")
    .then(response => response.json())
    .then(data => {

        let html = `
            <h2>Devices</h2>

            <table border="1">

                <tr>
                    <th>Device ID</th>
                    <th>Device Type</th>
                    <th>Room</th>
                </tr>
        `;

        data.devices.forEach(device => {

            html += `
                <tr>
                    <td>${device.deviceId}</td>
                    <td>${device.deviceType}</td>
                    <td>${device.room}</td>
                </tr>
            `;

        });

        html += "</table>";

        document.getElementById("output").innerHTML = html;

    });

Save the file.

View the Results

Open:

http://localhost/json-demo/

You should see:

Device ID Device Type Room
CAM-01 Security Camera Front Entrance
LOCK-02 Smart Door Lock Back Door

Add Basic Styling

The table works, but it looks very plain.

Add the following CSS inside the <head> section of index.html:

<style>

table {
    border-collapse: collapse;
    width: 100%;
}

th,
td {
    border: 1px solid #cccccc;
    padding: 10px;
    text-align: left;
}

th {
    background-color: #f2f2f2;
}

</style>

Refresh the page.

The table should now be easier to read.

Display the Home Information Above the Table

Update the start of the HTML string:

let html = `
    <p>
        <strong>Home ID:</strong>
        ${data.homeId}
    </p>

    <p>
        <strong>Location:</strong>
        ${data.location}
    </p>

    <h2>Devices</h2>

    <table border="1">

        <tr>
            <th>Device ID</th>
            <th>Device Type</th>
            <th>Room</th>
        </tr>
`;

The page should now display:

Home ID: GC-HOME-014
Location: Gold Coast

Devices

followed by the table.

Complete JavaScript File

fetch("smart_security_data.json")
    .then(response => response.json())
    .then(data => {

        let html = `
            <p>
                <strong>Home ID:</strong>
                ${data.homeId}
            </p>

            <p>
                <strong>Location:</strong>
                ${data.location}
            </p>

            <h2>Devices</h2>

            <table border="1">

                <tr>
                    <th>Device ID</th>
                    <th>Device Type</th>
                    <th>Room</th>
                </tr>
        `;

        data.devices.forEach(device => {

            html += `
                <tr>
                    <td>${device.deviceId}</td>
                    <td>${device.deviceType}</td>
                    <td>${device.room}</td>
                </tr>
            `;

        });

        html += "</table>";

        document.getElementById("output").innerHTML = html;

    });

You have successfully loaded JSON data and displayed it in an HTML table.

Working with JSON Data in JavaScript

Displaying Nested Event Data from JSON

In this tutorial, you will work with nested JSON data by displaying the security events associated with each device.

The JSON file contains devices, and each device contains its own list of events.

Understanding the JSON Structure

The JSON file contains:

Home
 └ Devices
     └ Events

Example:

{
    "deviceId": "CAM-01",
    "events": [
        {
            "eventType": "motion_detected"
        }
    ]
}

To display the events, we need to loop through:

  1. The devices
  2. The events within each device

Create an Events Table

Open:

script.js

Replace the previous code with:

fetch("smart_security_data.json")
    .then(response => response.json())
    .then(data => {

        let html = `
            <h2>Security Events</h2>

            <table border="1">

                <tr>
                    <th>Device ID</th>
                    <th>Event Type</th>
                    <th>Severity</th>
                </tr>
        `;

        data.devices.forEach(device => {

            device.events.forEach(event => {

                html += `
                    <tr>
                        <td>${device.deviceId}</td>
                        <td>${event.eventType}</td>
                        <td>${event.severity}</td>
                    </tr>
                `;

            });

        });

        html += "</table>";

        document.getElementById("output").innerHTML = html;

    });

View the Results

Open:

http://localhost/json-demo/

You should see a table similar to:

Device ID Event Type Severity
CAM-01 motion_detected high
CAM-01 remote_access_attempt critical
LOCK-02 unlock_attempt medium

Add Additional Event Information

The JSON file contains more information about each event.

Each event includes:

timestamp
eventType
dataTransmitted
severity
accessResult

Update the table headings:

<tr>
    <th>Device ID</th>
    <th>Timestamp</th>
    <th>Event Type</th>
    <th>Severity</th>
    <th>Access Result</th>
</tr>

Update the Table Rows

Replace the existing row code with:

html += `
    <tr>
        <td>${device.deviceId}</td>
        <td>${event.timestamp}</td>
        <td>${event.eventType}</td>
        <td>${event.severity}</td>
        <td>${event.accessResult}</td>
    </tr>
`;

Refresh the page.

The table should now display more detailed event information.

Display the Device Type

Sometimes multiple devices may generate events.

Add another column heading:

<th>Device Type</th>

Update the row:

<td>${device.deviceType}</td>

Your table will now identify which type of device generated each event.

Complete JavaScript File

fetch("smart_security_data.json")
    .then(response => response.json())
    .then(data => {

        let html = `
            <h2>Security Events</h2>

            <table border="1">

                <tr>
                    <th>Device ID</th>
                    <th>Device Type</th>
                    <th>Timestamp</th>
                    <th>Event Type</th>
                    <th>Severity</th>
                    <th>Access Result</th>
                </tr>
        `;

        data.devices.forEach(device => {

            device.events.forEach(event => {

                html += `
                    <tr>
                        <td>${device.deviceId}</td>
                        <td>${device.deviceType}</td>
                        <td>${event.timestamp}</td>
                        <td>${event.eventType}</td>
                        <td>${event.severity}</td>
                        <td>${event.accessResult}</td>
                    </tr>
                `;

            });

        });

        html += "</table>";

        document.getElementById("output").innerHTML = html;

    });

What Happened?

devices


In this tutorial, you looped through:

```javascript id="rjlwmz"
data.devices

and then:

device.events

This is known as a nested loop and is commonly used when working with JSON files and APIs.

You have successfully displayed nested JSON data in a table.

Working with JSON Data in JavaScript

Creating a Security Dashboard from JSON Data

In this tutorial, you will create a simple dashboard that summarises the data and highlights important security information.

The dashboard will display:

Using the sample JSON file, there are 2 devices and 3 events, including one critical event.

Create a Dashboard Section

Open:

script.js

Replace the existing code with:

fetch("smart_security_data.json")
    .then(response => response.json())
    .then(data => {

        let totalDevices = data.devices.length;

        let totalEvents = 0;
        let criticalEvents = 0;
        let highEvents = 0;
        let mediumEvents = 0;

        data.devices.forEach(device => {

            device.events.forEach(event => {

                totalEvents++;

                if (event.severity === "critical") {
                    criticalEvents++;
                }

                if (event.severity === "high") {
                    highEvents++;
                }

                if (event.severity === "medium") {
                    mediumEvents++;
                }

            });

        });

    });

This code calculates summary statistics from the JSON data.

Create Dashboard Cards

Below the calculations, add:

let html = `
    <h2>Security Dashboard</h2>

    <div class="card">
        <h3>Total Devices</h3>
        <p>${totalDevices}</p>
    </div>

    <div class="card">
        <h3>Total Events</h3>
        <p>${totalEvents}</p>
    </div>

    <div class="card">
        <h3>Critical Events</h3>
        <p>${criticalEvents}</p>
    </div>

    <div class="card">
        <h3>High Events</h3>
        <p>${highEvents}</p>
    </div>

    <div class="card">
        <h3>Medium Events</h3>
        <p>${mediumEvents}</p>
    </div>
`;

Display the Dashboard

Add:

document.getElementById("output").innerHTML = html;

The completed code should now display the dashboard cards.

Add Dashboard Styling

Open:

index.html

Inside the <style> section, add:

.card {
    border: 1px solid #cccccc;
    border-radius: 8px;
    padding: 15px;
    margin-bottom: 10px;
}

.card h3 {
    margin-top: 0;
}

Save the file.

View the Dashboard

Open:

http://localhost/json-demo/

Using the sample JSON file, you should see something similar to:

Security Dashboard

Total Devices: 2
Total Events: 3
Critical Events: 1
High Events: 1
Medium Events: 1

Display the Event Table Below the Dashboard

The dashboard is useful, but users often want to see the detailed events as well.

Add the following code underneath the dashboard cards:

html += `
    <h2>Security Events</h2>

    <table border="1">

        <tr>
            <th>Device ID</th>
            <th>Event Type</th>
            <th>Severity</th>
        </tr>
`;

Add Event Rows

Add:

data.devices.forEach(device => {

    device.events.forEach(event => {

        html += `
            <tr>
                <td>${device.deviceId}</td>
                <td>${event.eventType}</td>
                <td>${event.severity}</td>
            </tr>
        `;

    });

});

Close the table:

html += "</table>";

Complete JavaScript File

fetch("smart_security_data.json")
    .then(response => response.json())
    .then(data => {

        let totalDevices = data.devices.length;

        let totalEvents = 0;
        let criticalEvents = 0;
        let highEvents = 0;
        let mediumEvents = 0;

        data.devices.forEach(device => {

            device.events.forEach(event => {

                totalEvents++;

                if (event.severity === "critical") criticalEvents++;
                if (event.severity === "high") highEvents++;
                if (event.severity === "medium") mediumEvents++;

            });

        });

        let html = `
            <h2>Security Dashboard</h2>

            <div class="card">
                <h3>Total Devices</h3>
                <p>${totalDevices}</p>
            </div>

            <div class="card">
                <h3>Total Events</h3>
                <p>${totalEvents}</p>
            </div>

            <div class="card">
                <h3>Critical Events</h3>
                <p>${criticalEvents}</p>
            </div>

            <div class="card">
                <h3>High Events</h3>
                <p>${highEvents}</p>
            </div>

            <div class="card">
                <h3>Medium Events</h3>
                <p>${mediumEvents}</p>
            </div>

            <h2>Security Events</h2>

            <table border="1">

                <tr>
                    <th>Device ID</th>
                    <th>Event Type</th>
                    <th>Severity</th>
                </tr>
        `;

        data.devices.forEach(device => {

            device.events.forEach(event => {

                html += `
                    <tr>
                        <td>${device.deviceId}</td>
                        <td>${event.eventType}</td>
                        <td>${event.severity}</td>
                    </tr>
                `;

            });

        });

        html += "</table>";

        document.getElementById("output").innerHTML = html;

    });

You now have a dashboard that summarises JSON data and displays detailed event information underneath.

Importing CSV into MySQL with PHP

Build a secure administrator-only workflow that validates fictional CSV data and stores acceptable rows in MySQL.

Importing CSV into MySQL with PHP

Understanding CSV Data

CSV means comma-separated values. Each line is a record and each position represents a field.

match_id,team_name,wins,losses
M001,Fictional Falcons,4,1
M002,Sample Sharks,3,2

The first row is a header. Quoted fields may contain commas, so do not process CSV with explode(","); use fgetcsv().

Inspect before importing

Identify headers, data types, required fields, allowed ranges, unique identifiers, missing values and any fields that relate to other tables.

Treat every upload as untrusted input. A file being supplied with a task does not remove the need for validation.

Check

Importing CSV into MySQL with PHP

Designing a MySQL Table from a CSV

Design the destination table from the meaning of the data, not merely its appearance in a spreadsheet.

CSV field MySQL type Rule
match_id VARCHAR(20) unique and required
team_name VARCHAR(100) required
wins INT zero or greater
losses INT zero or greater 
CREATE TABLE team_results (
  result_id INT AUTO_INCREMENT PRIMARY KEY,
  match_id VARCHAR(20) NOT NULL UNIQUE,
  team_name VARCHAR(100) NOT NULL,
  wins INT NOT NULL,
  losses INT NOT NULL
);

A surrogate primary key supports database operations; the unique source identifier prevents accidental duplicates.

Design questions

Document and justify the chosen policy.

Check

Importing CSV into MySQL with PHP

Creating an Admin-Only CSV Upload Form

A CSV upload changes stored data and should be restricted to administrators. Apply session and role checks before any output.

<?php
session_start();
if (!isset($_SESSION["user_id"])) {
    header("Location: login.php");
    exit;
}
if (($_SESSION["role"] ?? "") !== "admin") {
    http_response_code(403);
    exit("Permission denied.");
}
?>
<form method="post" enctype="multipart/form-data">
  <label for="dataset">CSV dataset</label>
  <input id="dataset" name="dataset" type="file" accept=".csv,text/csv" required>
  <button type="submit">Validate and import</button>
</form>

The multipart/form-data encoding is required. The accept attribute guides file selection but does not provide server-side security.

Check

Importing CSV into MySQL with PHP

Validating an Uploaded CSV File

Validate the upload before reading its rows.

$file = $_FILES["dataset"] ?? null;
$errors = [];

if (!$file || $file["error"] !== UPLOAD_ERR_OK) {
    $errors[] = "Choose a CSV file that uploaded successfully.";
}
if ($file && $file["size"] > 2 * 1024 * 1024) {
    $errors[] = "The file must be 2 MB or smaller.";
}
$extension = $file ? strtolower(pathinfo($file["name"], PATHINFO_EXTENSION)) : "";
if ($extension !== "csv") {
    $errors[] = "The file must use the .csv extension.";
}

A filename or MIME type alone can be misleading. Combine upload status, size, extension, readable content, header and row validation.

Never use the original filename as a server path. Process the temporary upload and store only what the application requires.

Check

Test missing files, oversized files, wrong extensions, empty files and malformed content. Every failure should produce a clear message and must not partially import data.

Importing CSV into MySQL with PHP

Reading CSV Rows with fgetcsv

Use PHP’s CSV parser so quoted commas and escaped values are handled correctly.

$handle = fopen($file["tmp_name"], "r");
if ($handle === false) {
    exit("The uploaded file could not be read.");
}

$headers = fgetcsv($handle);
$expected = ["match_id", "team_name", "wins", "losses"];

if ($headers !== $expected) {
    fclose($handle);
    exit("The CSV headings do not match the expected format.");
}

$rowNumber = 1;
while (($row = fgetcsv($handle)) !== false) {
    $rowNumber++;
    // Validate and store this row.
}
fclose($handle);

This loop demonstrates iteration. The header comparison prevents values being assigned to the wrong fields.

Check

Importing CSV into MySQL with PHP

Validating CSV Rows

Validate every row before storing it.

if (count($row) !== 4) {
    $rowErrors[] = "Row $rowNumber has the wrong number of fields.";
    continue;
}

[$matchId, $teamName, $winsRaw, $lossesRaw] = array_map("trim", $row);

$wins = filter_var($winsRaw, FILTER_VALIDATE_INT);
$losses = filter_var($lossesRaw, FILTER_VALIDATE_INT);

if ($matchId === "" || $teamName === "") {
    $rowErrors[] = "Row $rowNumber has a missing required value.";
    continue;
}
if ($wins === false || $losses === false || $wins < 0 || $losses < 0) {
    $rowErrors[] = "Row $rowNumber has invalid results.";
    continue;
}

The loop is iteration; each validation decision is selection. Preserve row numbers so an administrator can correct the source.

Define whether one bad row rejects the whole file or only that row. For assessed work, justify the policy from integrity and user needs.

Check

Test blank, extra, missing, non-numeric, negative and duplicate values.

Importing CSV into MySQL with PHP

Inserting Imported Rows Safely

Prepare the insert once, then execute it for each valid row.

$insert = $pdo->prepare(
    "INSERT INTO team_results (match_id, team_name, wins, losses)
     VALUES (:match_id, :team_name, :wins, :losses)"
);

$insert->execute([
    "match_id" => $matchId,
    "team_name" => $teamName,
    "wins" => $wins,
    "losses" => $losses
]);

Use a transaction when the intended policy is all-or-nothing:

$pdo->beginTransaction();
try {
    // Read, validate and insert every row.
    $pdo->commit();
} catch (Throwable $error) {
    $pdo->rollBack();
    throw $error;
}

Transactions prevent a failed whole-file import from leaving a partial update. If valid rows may be retained while invalid rows are rejected, count both outcomes and report them clearly.

Check

Importing CSV into MySQL with PHP

Reporting CSV Import Results

An administrator needs evidence of what happened, not just “upload complete”.

Report useful totals

Show the escaped filename, rows read, inserted, updated, skipped and rejected, plus row-specific validation messages and whether a transaction committed or rolled back.

$summary = [
  "read" => $rowsRead,
  "inserted" => $rowsInserted,
  "rejected" => count($rowErrors)
];

Totals must reconcile: rows read equals all outcome categories combined. Never display passwords, server paths or raw SQL errors.

Accessible output

Use a clear heading and a list or table. Do not communicate success through colour alone. Make corrective messages specific: include the CSV row number and violated rule.

Check

Importing CSV into MySQL with PHP

Building a Complete Admin CSV Import

A complete import combines access control, upload checks, parsing, row validation, prepared statements and a result summary.

Processing sequence

  1. Start the session and require the administrator role.
  2. Accept a POST upload.
  3. Validate upload status, size and extension.
  4. Open the temporary file and verify headers.
  5. Begin the chosen transaction policy.
  6. Iterate through rows.
  7. Validate fields and store valid data.
  8. Commit or roll back.
  9. Close the file and display a summary.

Pseudocode

BEGIN
  REQUIRE administrator
  INPUT CSV
  IF file is invalid THEN OUTPUT error; STOP
  READ and CHECK header
  FOR EACH row
    VALIDATE fields
    IF valid THEN STORE with prepared statement
    ELSE RECORD row error
    ENDIF
  ENDFOR
  OUTPUT summary
END

This provides evidence of input, selection, iteration, storage and output. Separate repeated checks into functions when it improves readability.

Check

Trace one valid and one invalid row through every step. Explain the duplicate and partial-import policy rather than leaving either to chance.

Importing CSV into MySQL with PHP

Testing an Admin CSV Import

Testing must cover permissions, validation, database effects and feedback.

Test Condition Expected result
Logged out Direct import URL Redirect to login
Standard role Direct URL or POST 403; nothing imported
Valid CSV Correct headings and rows Expected records stored
Wrong heading Changed column File rejected
Invalid number Text or negative result Stated rejection policy
Duplicate ID Existing identifier Stated duplicate policy
Empty file No usable rows Clear rejection
Oversized file Above limit Rejected before parsing

Record actual result, pass/fail and refinement. Verify the database rather than trusting the on-screen message.

Use fictional accounts and data. Hide credentials and unrelated information in screenshots.

Completion checklist

Processing and Presenting Imported Data

Query, process and present stored data as meaningful outputs that respond to user needs and success criteria.

Processing and Presenting Imported Data

Querying Data for a User Need

A query is meaningful when it answers a user question. Begin with the need, then choose fields, filters, calculations and order.

Example need

A participant wants to find the strongest fictional teams and inspect their performance.

SELECT team_name,
       SUM(wins) AS total_wins,
       SUM(losses) AS total_losses
FROM team_results
GROUP BY team_name
ORDER BY total_wins DESC, team_name ASC;

This processes many stored rows into one summary per team. It is stronger evidence of processing than simply displaying the imported table.

Plan the output

Document:

Check

Processing and Presenting Imported Data

Building a Leaderboard

A leaderboard converts stored results into a ranked, understandable output.

SELECT team_name,
       SUM(wins) AS wins,
       SUM(losses) AS losses,
       SUM(wins) * 3 AS points
FROM team_results
GROUP BY team_name
ORDER BY points DESC, wins DESC, team_name ASC;

The points rule is an example only. Use the rule justified by the chosen context.

Display the ranking

Iterate through the query result and create a table with rank, team, wins, losses and points. Increment rank inside the loop. Escape text and cast numbers.

Explain tie behaviour. A deterministic secondary sort prevents ranks from changing unpredictably.

Test

Use a small dataset where you can calculate the result manually. Include a tie, zero values and an empty dataset.

Check

Processing and Presenting Imported Data

Calculating Summary Statistics

Statistics reduce a dataset to information users can interpret.

SELECT
  COUNT(*) AS result_count,
  COUNT(DISTINCT team_name) AS team_count,
  SUM(wins) AS total_wins,
  AVG(wins) AS average_wins,
  MAX(wins) AS highest_wins
FROM team_results;

Choose statistics because they answer a need, not because functions are available. Label values clearly and round averages deliberately.

<p>Average wins:
  <?= number_format((float) $stats["average_wins"], 1) ?>
</p>

Interpretation

A number without context is weak. State what population and timeframe it represents. Do not imply cause from a descriptive statistic.

Check

Processing and Presenting Imported Data

Creating Match and Player Views

A detail view lets a user move from a summary to the record needed for a task.

Receive a validated identifier

$matchId = trim($_GET["match_id"] ?? "");
if ($matchId === "" || mb_strlen($matchId) > 20) {
    exit("Invalid match.");
}

$stmt = $pdo->prepare(
  "SELECT match_id, team_name, wins, losses
   FROM team_results
   WHERE match_id = :match_id"
);
$stmt->execute(["match_id" => $matchId]);
$match = $stmt->fetch();

if (!$match) {
    http_response_code(404);
    exit("Match not found.");
}

Display escaped values with headings that explain the record. Provide a logical way back to the summary.

For player views, use an appropriate relationship and do not invent personal data. All names and records must be fictional.

Check

Processing and Presenting Imported Data

Creating Charts from Processed SQL Data

A chart is useful only when it makes a comparison or pattern easier to understand. JavaScript is a supporting technology; the assessed reasoning lies in the data choice, processing and justified output.

Prepare data in PHP

$labels = array_column($rows, "team_name");
$values = array_map("intval", array_column($rows, "points"));

Pass encoded data safely:

<script>
const labels = <?= json_encode($labels) ?>;
const values = <?= json_encode($values) ?>;
</script>

A chart library can use these arrays. Provide a visible title, axis labels, sufficient contrast and a text/table alternative.

Choose the chart

Do not create a chart from raw unprocessed data when a grouped or calculated result is what users need.

Check

Processing and Presenting Imported Data

Exporting Processed Data to CSV

CSV export can support download and reuse of a filtered or processed result. It is optional when import already satisfies the data-transfer requirement, but can be valuable for a justified user need.

Send download headers

header("Content-Type: text/csv; charset=UTF-8");
header('Content-Disposition: attachment; filename="team-summary.csv"');

$output = fopen("php://output", "w");
fputcsv($output, ["Team", "Wins", "Losses", "Points"]);

foreach ($rows as $row) {
    fputcsv($output, [
        $row["team_name"],
        $row["wins"],
        $row["losses"],
        $row["points"]
    ]);
}
fclose($output);
exit;

Run authentication and authorisation before output headers. Build $rows with the same validated filters used by the visible report.

Test

Open the download in a text editor and spreadsheet. Check headings, quoted commas, character encoding, row count and filtered scope.

Check