*************

SQL code: Upload Copy Format Clear

-- 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);

SQL Server: Preserve result

Result: Copy Clear

PHP code: Copy Format Clear

<?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); $a = [ 'fruits' => ['apple', 'banana'], 'vegetables' => ['carrot', 'tomato'] ]; $search = 'tomato'; $found = null; array_walk($a, function ($k, $v) use ($search, &$found) { if (in_array($search, $k)) { $found = $v; } }); echo $found; // Outputs: vegetables echo "\n"; // foreach($customers['online'] as $index => $customer) { // var_dump($customer); // }

PHP version:

Show: Warnings Notice

Result: Copy Clear

PHPize Online / SQLize Online / SQLtest Online