Programmieren – die besten Beiträge

Verwandte Themen
udoMM278
24.11.2024, 20:24

Wie könnte ich die Datenbankabfragen oder das Programm optimieren?

<?php
include 'db.php';


// Five Most Frequently Ordered Products
$query_highesttotalquantity = "
    SELECT 
        id_product, 
        SUM(anzahl) total_quantity
    FROM 
        bestellungen_products
    GROUP BY 
        id_product
    ORDER BY 
        total_quantity DESC
    LIMIT 5
";
$highesttotalquantity = $conn->query($query_highesttotalquantity);
if (!$highesttotalquantity) {
    die("Query Error (highesttotalquantity): " . $conn->error);
}


// Five Products with the Highest Number of Orders
$query_highesnumb = "
    SELECT 
        id_product, 
        COUNT(DISTINCT id_bestellung) order_count
    FROM 
        bestellungen_products
    GROUP BY 
        id_product
    ORDER BY 
        order_count DESC
    LIMIT 5
";
$highesnumb = $conn->query($query_highesnumb);
if (!$highesnumb) {
    die("Query Error (highesnumb): " . $conn->error);
}


// Five Least Frequently Ordered Products
$query_lowest5 = "
    SELECT 
        id_product, 
        SUM(anzahl) total_quantity
    FROM 
        bestellungen_products
    GROUP BY 
        id_product
    ORDER BY 
        total_quantity ASC
    LIMIT 5
";
$lowest5 = $conn->query($query_lowest5);
if (!$lowest5) {
    die("Query Error (lowest5): " . $conn->error);
}


// Order History Over the Last Four Weeks
$query_last4weeks = "
    SELECT 
        YEARWEEK(bestelldatum, 1) week, 
        SUM(bp.anzahl) total_quantity
    FROM 
        bestellungen b
    JOIN 
        bestellungen_products bp ON b.id_bestellung = bp.id_bestellung
    WHERE 
        bestelldatum >= CURDATE() - INTERVAL 4 WEEK
    GROUP BY 
        week
    ORDER BY 
        week DESC
";
$last4weeks = $conn->query($query_last4weeks);
if (!$last4weeks) {
    die("Query Error (last4weeks): " . $conn->error);
}
?>


<!DOCTYPE html>
<html>
<head>
    <title>Admin Statistics</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            padding: 20px;
        }
        h1, h2 {
            color: #333;
        }
        .stat-section {
            margin-bottom: 20px;
        }
    </style>
</head>
<body>
    <h1>Admin Statistics</h1>


    <div class="stat-section">
        <h2>Five Most Frequently Ordered Products</h2>
        <?php while ($product = $highesttotalquantity->fetch_assoc()): ?>
            <p>Product ID: <?= htmlspecialchars($product['id_product']) ?> - Total Quantity: <?= htmlspecialchars($product['total_quantity']) ?></p>
        <?php endwhile; ?>
    </div>


    <div class="stat-section">
        <h2>Five Products with the Highest Number of Orders</h2>
        <?php while ($product = $highesnumb->fetch_assoc()): ?>
            <p>Product ID: <?= htmlspecialchars($product['id_product']) ?> - Order Count: <?= htmlspecialchars($product['order_count']) ?></p>
        <?php endwhile; ?>
    </div>


    <div class="stat-section">
        <h2>Five Least Frequently Ordered Products</h2>
        <?php while ($product = $lowest5->fetch_assoc()): ?>
            <p>Product ID: <?= htmlspecialchars($product['id_product']) ?> - Total Quantity: <?= htmlspecialchars($product['total_quantity']) ?></p>
        <?php endwhile; ?>
    </div>


    <div class="stat-section">
        <h2>Order History Over the Last Four Weeks</h2>
        <?php while ($week = $last4weeks->fetch_assoc()): ?>
            <p>Week: <?= htmlspecialchars($week['week']) ?> - Total Quantity Ordered: <?= htmlspecialchars($week['total_quantity']) ?></p>
        <?php endwhile; ?>
    </div>
</body>
</html>
programmieren, Code
1 Antwort

Meistgelesene Beiträge zum Thema Programmieren

Mehr meistgelesene Fragen zu Programmieren