# Creating a Database-Driven API with PHP and MySQL
In the previous tutorials, the API returned data from a JSON file.
In this tutorial, you will create an API that reads data from a MySQL database and returns it as JSON.
---
## The System Architecture
The completed system will work like this:
```text
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 File
Create:
```text
api_events.php
```
Add the database connection:
```php
connect_error) {
die("Connection failed: " .
$conn->connect_error);
}
?>
```
---
## Set the Content Type
Add:
```php
header(
"Content-Type: application/json"
);
```
The API will now return JSON instead of HTML.
---
## Query the Database
Add:
```php
$query = "
SELECT *
FROM security_events
";
$result =
$conn->query($query);
```
This retrieves all records from the table.
---
## Create an Array
Add:
```php
$events = [];
```
This array will hold all database records.
---
## Loop Through the Results
Add:
```php
while (
$row =
$result->fetch_assoc()
) {
$events[] = $row;
}
```
This converts each database record into an array item.
---
## Convert the Array to JSON
Add:
```php
echo json_encode(
$events,
JSON_PRETTY_PRINT
);
```
The completed API should now return JSON.
---
## Test the API
Open:
```text
http://localhost/api-demo/api_events.php
```
You should see:
```json
[
{
"event_id":"1",
"device_id":"CAM-01",
"event_type":"motion_detected",
"severity":"high"
},
{
"event_id":"2",
"device_id":"CAM-01",
"event_type":"remote_access_attempt",
"severity":"critical"
}
]
```
The exact records will depend on your database.
## Complete API File
```php
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:
```text
events.html
```
Add:
```html
Security Events
Security Events
```
---
## Test the Complete System
Open:
```text
http://localhost/api-demo/events.html
```
The page should display the records stored in MySQL.
## Why This Is Useful
Previously:
```text
JSON File
↓
JavaScript
```
Now:
```text
MySQL Database
↓
PHP API
↓
JavaScript
```
This means:
* New records can be added to the database
* The API updates automatically
* The webpage updates automatically
* No JSON file needs to be edited manually
This is the foundation of many modern web applications.
Next tutorial: **Creating API Endpoints for Specific Data (Critical Events, Devices and Statistics)**.