<?php
// Конфигурация БД
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "shop";
$port = 3306;
// Валидация параметров
$min_orders = filter_input(INPUT_GET, 'min_orders', FILTER_VALIDATE_INT, [
'options' => ['default' => 3, 'min_range' => 1]
]);
$min_total = filter_input(INPUT_GET, 'min_total', FILTER_VALIDATE_FLOAT, [
'options' => ['default' => 1000, 'min_range' => 0]
]);
try {
// Подключение к серверу MySQL
$conn = mysqli_connect($servername, $username, $password, '', $port);
if (!$conn) {
throw new Exception("MySQL connection error: " . mysqli_connect_error());
}
// Создание БД и таблиц если не существуют
mysqli_query($conn, "CREATE DATABASE IF NOT EXISTS $dbname");
mysqli_select_db($conn, $dbname);
mysqli_query($conn, "
CREATE TABLE IF NOT EXISTS customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE,
registration_date DATE
)
");
mysqli_query($conn, "
CREATE TABLE IF NOT EXISTS products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
category VARCHAR(30)
)
");
mysqli_query($conn, "
CREATE TABLE IF NOT EXISTS orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
product_id INT,
quantity INT,
price DECIMAL(10,2),
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(id),
FOREIGN KEY (product_id) REFERENCES products(id)
)
");
// Добавление тестовых данных
mysqli_query($conn, "
INSERT IGNORE INTO customers (name, email, registration_date)
VALUES
('Иван Петров', 'ivan@mail.com', '2023-01-15'),
('Мария Сидорова', 'maria@mail.com', '2022-11-30'),
('Алексей Иванов', 'alex@mail.com', '2023-03-22')
");
mysqli_query($conn, "
INSERT IGNORE INTO products (name, category)
VALUES
('Ноутбук ASUS', 'Электроника'),
('Смартфон Xiaomi', 'Электроника'),
('Книга PHP 8', 'Книги')
");
mysqli_query($conn, "
INSERT IGNORE INTO orders (customer_id, product_id, quantity, price, order_date)
VALUES
(1, 1, 2, 45000, '2023-05-10'),
(1, 3, 1, 1200, '2023-05-15'),
(2, 2, 3, 25000, '2023-04-20'),
(3, 1, 1, 45000, '2023-05-01')
");
// Основной запрос
$sql = "
SELECT
c.name,
c.email,
MAX(o.order_date) as last_order,
COUNT(o.id) as total_orders,
SUM(o.quantity * o.price) as total_spent,
AVG(o.quantity * o.price) as avg_order
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
HAVING total_orders > ? AND total_spent > ?
ORDER BY total_spent DESC
";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "dd", $min_orders, $min_total);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if (!$result) {
throw new Exception("Query error: " . mysqli_error($conn));
}
} catch (Exception $e) {
die("<h3>Error: " . htmlspecialchars($e->getMessage()) . "</h3>
<p>Проверьте:</p>
<ul>
<li>Доступность MySQL-сервера</li>
<li>Правильность логина/пароля</li>
<li>Наличие прав у пользователя '$username'</li>
</ul>");
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Аналитика клиентов</title>
<style>
table {border-collapse: collapse; margin: 20px 0;}
th, td {padding: 12px; border: 1px solid #ddd; text-align: left;}
th {background-color: #4CAF50; color: white;}
tr:hover {background-color: #f5f5f5;}
.filter-box {padding: 20px; background: #f0f0f0;}
</style>
</head>
<body>
<div class="filter-box">
<form method="GET">
<label>Минимум заказов:
<input type="number" name="min_orders"
value="<?= htmlspecialchars($min_orders) ?>" min="1">
</label>
<label>Минимальная сумма:
<input type="number" name="min_total"
value="<?= htmlspecialchars($min_total) ?>" step="0.01" min="0">
</label>
<button type="submit">Применить фильтры</button>
</form>
</div>
<?php if (mysqli_num_rows($result) > 0): ?>
<table>
<tr>
<th>Имя</th>
<th>Email</th>
<th>Последний заказ</th>
<th>Всего заказов</th>
<th>Общая сумма</th>
<th>Средний чек</th>
</tr>
<?php while($row = mysqli_fetch_assoc($result)): ?>
<tr>
<td><?= htmlspecialchars($row['name']) ?></td>
<td><?= htmlspecialchars($row['email']) ?></td>
<td><?= date('d.m.Y', strtotime($row['last_order'])) ?></td>
<td><?= $row['total_orders'] ?></td>
<td><?= number_format($row['total_spent'], 2, ',', ' ') ?> ₽</td>
<td><?= number_format($row['avg_order'], 2, ',', ' ') ?> ₽</td>
</tr>
<?php endwhile; ?>
</table>
<?php else: ?>
<p>Нет данных, соответствующих критериям поиска.</p>
<?php endif; ?>
</body>
</html>
<?php
mysqli_close($conn);
?>
Telegram: DB developers channel