retrieve data using sql complex join using php

dataaccessscriptphp

<?php

// Database configuration
$hostname = "your_hostname";
$username = "your_username";
$password = "your_password";
$database = "your_database_name";

try {
    // Establish the connection using PDO
    $dsn = "mysql:host=$hostname;dbname=$database";
    $conn = new PDO($dsn, $username, $password);

    // Set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Perform an SQL query to join and aggregate data
    $query = "
        SELECT
            c.CategoryName,
            COUNT(p.ProductID) AS ProductCount
        FROM
            Categories c
        LEFT JOIN
            Products p ON c.CategoryID = p.CategoryID
        GROUP BY
            c.CategoryName
        ORDER BY
            ProductCount DESC
    ";

    // Prepare and execute the query
    $stmt = $conn->prepare($query);
    $stmt->execute();

    // Fetch results
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Close the connection
    $conn = null;

    // Return results as JSON
    header('Content-Type: application/json');
    echo json_encode($results);
} catch (PDOException $e) {
    // Handle PDO exceptions
    header('HTTP/1.1 500 Internal Server Error');
    echo json_encode(['error' => $e->getMessage()]);
}
?>