PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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), (2, 'Anna', 35); INSERT INTO `addresses` (`id`, `address`, `customer_id`) VALUES (1, 'Some address', 1), (2, 'Secondary address', 1), (3, 'Secondary address', 2), (4, 'Secondary address', 2);
Copy Clear
Copy Format Clear
<?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`, `addresses`.`address` FROM `customers` JOIN `addresses` ON `customers`.`id` = `addresses`.`customer_id` "; $result = $mysqli->query($sql); $rows = array(); while ($row = $result->fetch_assoc()) { if (is_array($rows[$row['id']])) { array_push($rows[$row['id']]['address'],$row['address']); } else { $rows[$row['id']] = $row; $rows[$row['id']]['address'] = (array)$rows[$row['id']]['address']; } } //var_export($rows); echo json_encode( $rows );
Show:  
Copy Clear