Hi! Could we please enable some services and cookies to improve your experience and our website?
Online Sandbox for SQL and PHP: Write, Run, Test, and Share SQL Queries and PHP Code
CREATE TABLE `customers` (
`id` bigint(20) NOT NULL,
`name` varchar(50) NOT NULL,
`age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `addresses` (
`id` bigint(20) NOT NULL,
`address` varchar(150) NOT NULL,
`customer_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `customers`
ADD PRIMARY KEY (`id`);
ALTER TABLE `customers`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `addresses`
ADD PRIMARY KEY (`id`),
ADD KEY `customer_id` (`customer_id`);
ALTER TABLE `addresses`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `addresses`
ADD CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;
INSERT INTO `customers` (`id`, `name`, `age`) VALUES
(1, 'John', 32);
INSERT INTO `addresses` (`id`, `address`, `customer_id`) VALUES
(1, 'Some address', 1),
(2, 'Secondary address', 1);
<?php
printf("Current PHP version: %s \n", phpversion());
$query = "SELECT VERSION() as version;";
$stmt = $pdo->prepare($query);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
printf('DB version: %s', $row['version']);
$sql = "SELECT
`customers`.`id`,
`customers`.`name`,
`customers`.`age`,
JSON_ARRAYAGG(`addresses`.`address`) AS `addresses`
FROM `customers`
JOIN `addresses` ON `customers`.`id` = `addresses`.`customer_id`
";
$result = $mysqli->query($sql);
$rows = array();
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
var_export($rows);