# Building a Security Dashboard Using PHP and MySQL In the previous tutorial, you used SQL queries to analyse imported JSON data. In this tutorial, you will build a dashboard that displays security statistics directly from the database. The dashboard will display: * Total Devices * Total Events * Critical Events * High Events * Recent Security Events --- ## Create the Dashboard File Create a new file called: ```text dashboard.php ``` Add the database connection: ```php connect_error) { die("Connection failed: " . $conn->connect_error); } ?> ``` --- ## Get the Total Number of Events Add: ```php $totalEventsQuery = " SELECT COUNT(*) AS total_events FROM security_events "; $totalEventsResult = $conn->query($totalEventsQuery); $totalEvents = $totalEventsResult->fetch_assoc()["total_events"]; ``` --- ## Get the Number of Devices Add: ```php $totalDevicesQuery = " SELECT COUNT(DISTINCT device_id) AS total_devices FROM security_events "; $totalDevicesResult = $conn->query($totalDevicesQuery); $totalDevices = $totalDevicesResult->fetch_assoc()["total_devices"]; ``` --- ## Get Critical Events Add: ```php $criticalEventsQuery = " SELECT COUNT(*) AS critical_events FROM security_events WHERE severity = 'critical' "; $criticalEventsResult = $conn->query($criticalEventsQuery); $criticalEvents = $criticalEventsResult->fetch_assoc()["critical_events"]; ``` --- ## Get High Events Add: ```php $highEventsQuery = " SELECT COUNT(*) AS high_events FROM security_events WHERE severity = 'high' "; $highEventsResult = $conn->query($highEventsQuery); $highEvents = $highEventsResult->fetch_assoc()["high_events"]; ``` --- ## Create the Dashboard Page Add the following HTML underneath the PHP code: ```html Security Dashboard

Security Dashboard

Total Devices

Total Events

Critical Events

High Events

``` --- ## Test the Dashboard Open: ```text http://localhost/json-import/dashboard.php ``` Using the sample data, you should see something similar to: ```text Security Dashboard Total Devices: 2 Total Events: 3 Critical Events: 1 High Events: 1 ``` ## Display Recent Events Add the following query before the HTML: ```php $recentEventsQuery = " SELECT * FROM security_events ORDER BY event_timestamp DESC "; $recentEvents = $conn->query($recentEventsQuery); ``` --- ## Create the Events Table Add the following underneath the dashboard cards: ```php

Recent Events

fetch_assoc() ) { ?>
Device Event Type Severity Timestamp
``` Refresh the page. You should now see all imported events underneath the dashboard. ## Add Basic Styling Inside the `` section add: ```html ``` Then update each dashboard `
`: ```html
``` The dashboard should now look much more professional. ## Complete Dashboard Features Your dashboard now: * Connects to MySQL * Runs SQL queries * Displays summary statistics * Displays recent events * Updates automatically whenever new JSON data is imported This is one of the major advantages of importing JSON into a database rather than displaying the JSON directly. Next tutorial: **Creating Filters and Search Options for the Dashboard**.