-- Create customers table with customer_type
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
customer_type ENUM('online', 'on_site') NOT NULL DEFAULT 'online'
);
-- Create orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10, 2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Insert sample customers with customer_type
INSERT INTO customers (name, email, customer_type) VALUES
('John Doe', 'john.doe@example.com', 'online'),
('Jane Smith', 'jane.smith@example.com', 'on_site'),
('Alice Johnson', 'alice.johnson@example.com', 'online'),
('Florin T', 'florin.t@example.com', 'online');
-- Insert sample orders
INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(1, '2024-01-15', 100.00),
(1, '2024-02-20', 150.50),
(2, '2024-01-10', 200.75),
(2, '2024-03-05', 300.40),
(2, '2024-03-20', 50.00),
(3, '2024-02-25', 400.20);
<?php
$sql = "SELECT c.customer_id,
c.name,
c.email,
c.customer_type,
o.order_id,
o.order_date,
o.total_amount
FROM
customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id
";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$customers = [
];
$customersIndexMap = [
];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$customer_type = $row['customer_type'];
$customer_id = $row['customer_id'];
if (!isset($customersIndexMap[$customer_type][$customer_id])) {
$customers[$customer_type][] = [
'customer_id' => $row['customer_id'],
'name' => $row['name'],
'email' => $row['email'],
'orders' => []
];
$customersIndexMap[$customer_type][$customer_id] = count($customers[$customer_type]) - 1;
}
$targetIndex = $customersIndexMap[$customer_type][$customer_id];
if(isset($row['order_id'])) {
$customers[$customer_type][$targetIndex]['orders'][] = [
'order_id' => $row['order_id'],
'order_date' => $row['order_date'],
'total_amount' => $row['total_amount']
];
}
}
// echo json_encode($customers);
echo "\n";
// foreach($customers['online'] as $index => $customer) {
// var_dump($customer);
// }