Module 5: MySQL වෙතින් Live Data ප්‍රදර්ශනය කිරීම

<?php

$host = 'localhost';    // Database host
$dbname = 'ajax_course_db'; // Database name (Module 2 හි සෑදූ එක)
$username = 'root';       // Database username (XAMPP default)
$password = '';           // Database password (XAMPP default is empty)

try {
    // Create a new PDO instance
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    
    // Set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
} catch (PDOException $e) {
    // If connection fails, stop the script and show an error
    die("Connection failed: " . $e->getMessage());
}

?>

<?php

// 1. Include the database connection file
require_once 'db.php';

// 2. Prepare and execute the SQL query to get all users
$stmt = $pdo->query("SELECT id, name, email, created_at FROM users ORDER BY id DESC");

// 3. Fetch all results into an associative array
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 4. Set the content type header to JSON
header('Content-Type: application/json');

// 5. Encode the array into a JSON string and echo it
echo json_encode($users);

?>

document.getElementById('loadDataBtn').addEventListener('click', function() {
    const xhr = new XMLHttpRequest();
    xhr.open('GET', 'fetch-data.php', true);

    xhr.onload = function() {
        if (this.status === 200) {
            // 1. Parse the JSON string into a JavaScript object
            const users = JSON.parse(this.responseText);
            
            // 2. Prepare the HTML for the table
            let output = `
                <table class="table table-striped table-bordered">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Name</th>
                            <th>Email</th>
                            <th>Registered On</th>
                        </tr>
                    </thead>
                    <tbody>`;

            // 3. Loop through the users and add a table row for each
            users.forEach(function(user) {
                output += `
                    <tr>
                        <td>${user.id}</td>
                        <td>${user.name}</td>
                        <td>${user.email}</td>
                        <td>${user.created_at}</td>
                    </tr>`;
            });

            // 4. Close the table tags and display the result
            output += '</tbody></table>';
            document.getElementById('responseDiv').innerHTML = output;

        } else {
            document.getElementById('responseDiv').innerHTML = 'Error fetching data.';
        }
    };
    xhr.send();
});