Skip to main content

Creating a Database-Driven API with PHP and MySQL

InThis thistutorial tutorial,creates youa willread-only createJSON anendpoint using PHP, PDO and fictional database records.

An API thatshould readsreturn only the data fromneeded afor MySQLits stated purpose. Do not expose password hashes, session data, personal information or unnecessary database and returns it as JSON.fields.

The System Architecture

The completed system will work like this:

security_events table
        ↓
      api.php
        ↓
       JSON
        ↓
   JavaScript

Instead of reading a JSON file, the API will generate JSON directly from the database.

Create the API Fileendpoint

Create:

Create 
api_events.api/events.php

Add the database connection::

<?php
declare(strict_types=1);

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

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

try {
    $connstatement = new$pdo->query(
        mysqli('SELECT
            "localhost",event_id,
            "root",event_timestamp,
            "",device_id,
            "project_db"room,
            event_type,
            severity,
            access_result
         FROM security_events
         ORDER BY event_timestamp DESC
         LIMIT 100'
    );

    if$events ($conn->connect_error) {
    die("Connection failed: " .= $conn-statement->connect_error)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'

Set=> the'The Contentfictional Type

event

Add:

data 
header(is "Content-Type:unavailable.'
    application/json"
]);
}

The API will now return JSON instead of HTML.endpoint:

Query

uses the Databaseshared

Add:

PDO 
$queryconnection;
=selects " SELECT * FROM security_events "; $result = $conn->query($query);

This retrieves all records fromonly the table.

required
fields;

Create

limits anthe Arrayresponse

Add:

size; 
$eventsreturns =a [];predictable
object;

Thisand array

avoids will hold allexposing database records.error
details.

Loop Through the Results

Add:

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

    $events[] = $row;

}

This converts each database record into an array item.

Convert the Array to JSON

Add:

echo json_encode(
    $events,
    JSON_PRETTY_PRINT
);

The completed API should now return JSON.

Test the APIresponse

Open:Open the endpoint through XAMPP:

http://localhost/api-demo/api_events.project/api/events.php

YouA successful response should see:have this shape:

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

The exactvalues recordsare fictional. Your live response will dependreflect onthe yourcurrent database.

CompleteImportant API Fileboundary

<?php

$conn = new mysqli(
    "localhost",
    "root",
    "",
    "project_db"
);

if ($conn->connect_error) {
    die("Connection failed: " .
        $conn->connect_error);
}

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

$query = "
    SELECT *
    FROM security_events
";

$result =
    $conn->query($query);

$events = [];

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

    $events[] = $row;

}

echo json_encode(
    $events,
    JSON_PRETTY_PRINT
);

?>

Create a Webpage to Read the API

Create:

events.html

Add:

<!DOCTYPE html>
<html>
<head>
    <title>Security Events</title>
</head>
<body>

<h1>Security Events</h1>

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

<script>

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

        let html = `
            <table border="1">

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

        data.forEach(event => {

            html += `
                <tr>
                    <td>${event.device_id}</td>
                    <td>${event.event_type}</td>
                    <td>${event.severity}</td>
                </tr>
            `;

        });

        html += "</table>";

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

    });

</script>

</body>
</html>

Test the Complete System

Open:

http://localhost/api-demo/events.html

The page should display the records stored in MySQL.

Why This Is Useful

Previously:

JSON File
↓
JavaScript

Now:

MySQL Database
↓
PHP API
↓
JavaScript

This means: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

  • NewThe recordsendpoint canreturns bevalid added to the databaseJSON.
  • The APIresponse updatesuses automaticallyan appropriate HTTP status when processing fails.
No credentials, password hashes or unnecessary fields are exposed. The webpageresponse updatesis automaticallylimited to a reasonable number of records. NoAll JSONrecords fileare needs to be edited manuallyfictional.

This is the foundation of many modern web applications.

Back to top