PHP MYSQL Select OneToMany as a nested json

I’ve two tables, one being the Customer table and the other the Address table having a one to many between them. I want to select all customers with their respective addresses and displaying it something like this:

{
  id: 222,
  name: John Doe,
  age: 32,
  addresses: [
    { id: 1, address: "some address", customer_id: 222 },
    { id: 2, address: "secondary address", customer_id: 222}
  ]
}

My code is the following now:

$sql = "SELECT * FROM customers JOIN addresses ON customers.id = addresses.customer_id";
$result = $connection->query($sql);


$rows = array();

while ($row = $result->fetch_assoc()) {
  $rows[] = $row;
}

print json_encode($rows); 

But it results in two records with same data but different address property. How could I achieve the upper example?

Sample data:

customers table:

id  | name | age
222 | John | 32

addresses table:

id | customer_id | address
1  | 222         | Some address
2  | 222         | Secondary address

Current result:

[
  {
    id: 222,
    name: John,
    age: 32,
    address: Some address
    customer_id: 222
  },
  {
    id: 222,
    name: John,
    age: 32,
    address: Secondary address
    customer_id: 222
  }
]

SQL scripts:

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

Answer

If you need to solve the issue on DB side you can use JSON_ARRAYAGG function in you query:

    SELECT 
        `customers`.`id`,
        `customers`.`name`,
        `customers`.`age`,
        JSON_ARRAYAGG(`addresses`.`address`) AS `addresses`
    FROM `customers` 
    JOIN `addresses` ON `customers`.`id` = `addresses`.`customer_id`

Here You can test this query and PHP code

The PHP solution can be next:

<?php
$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); 

Run MySQL and PHP code online