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
