PHP Security Best Practices - Autocodewizard Ebooks - PHP Essentials: Building Dynamic Web Applications

Chapter 14: PHP Security Best Practices

Combine your PHP and front-end skills to create a real-time data dashboard, integrating database interactions and dynamically updating the UI.

In this final project, we’ll bring together PHP, JavaScript, and database management skills to build a real-time data dashboard. This project will allow you to create a dynamic, interactive interface that automatically updates with data from the server, providing valuable insights or live data monitoring capabilities.

Setting Up the Database

Start by setting up a MySQL table to store data for the dashboard. For this example, let’s create a table to track product sales:

<?php
$sql = "CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    quantity_sold INT NOT NULL,
    sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
$conn->query($sql);
?>

This table tracks each sale with fields for product_name, quantity_sold, and sale_date.

Creating the PHP Backend

Create a PHP file that retrieves data from the database in JSON format. This data will be used to update the dashboard in real-time:

<?php
header('Content-Type: application/json');

$sql = "SELECT product_name, quantity_sold, sale_date FROM sales ORDER BY sale_date DESC LIMIT 10";
$result = $conn->query($sql);

$data = [];
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
}

echo json_encode($data);
?>

This code fetches the latest 10 sales records and returns them in JSON format, ready for use by the front end.

Building the Front-End Dashboard

Next, create an HTML structure to display the sales data. Use JavaScript to dynamically update this data:

<div id="dashboard" class="bg-white shadow rounded p-4">
    <h2 class="text-xl font-bold mb-4 text-gray-800">Real-Time Sales Dashboard</h2>
    <table class="w-full text-gray-700">
        <thead>
            <tr>
                <th class="px-4 py-2">Product Name</th>
                <th class="px-4 py-2">Quantity Sold</th>
                <th class="px-4 py-2">Sale Date</th>
            </tr>
        </thead>
        <tbody id="sales-data">
            <!-- Data will be populated here -->
        </tbody>
    </table>
</div>

This HTML structure sets up a basic table to display the product sales data.

Fetching and Updating Data with JavaScript

Use JavaScript with fetch to retrieve and display the data. Set up an interval to refresh the data periodically:

<script>
async function fetchSalesData() {
    const response = await fetch('sales_data.php');
    const data = await response.json();

    const tbody = document.getElementById('sales-data');
    tbody.innerHTML = '';
    data.forEach(item => {
        const row = document.createElement('tr');
        row.innerHTML = `
            <td class="px-4 py-2">${item.product_name}</td>
            <td class="px-4 py-2">${item.quantity_sold}</td>
            <td class="px-4 py-2">${item.sale_date}</td>
        `;
        tbody.appendChild(row);
    });
}

// Fetch data every 5 seconds
setInterval(fetchSalesData, 5000);
fetchSalesData(); // Initial call
</script>

This JavaScript code fetches the latest sales data every 5 seconds and updates the table in the dashboard with the new data.

Adding Real-Time Updates with AJAX

In a more advanced setup, you could use WebSockets or Server-Sent Events (SSE) for real-time updates. For this project, however, periodic AJAX polling with fetch is sufficient for most dashboards.

Summary and Final Thoughts

In this final project, we built a real-time data dashboard using PHP, JavaScript, and MySQL. This project combines back-end and front-end skills, demonstrating how to create a dynamic user interface that reflects live data changes. Congratulations on reaching the end of this book! By mastering these PHP skills, you’re well-prepared to develop powerful and interactive web applications.