时间戳

首页 > 解决方案 > PHP MYSQL 选择 OneToMany 作为嵌套 json

php - PHP MYSQL 选择 OneToMany 作为嵌套 json

问题描述

我有两个表，一个是 Customer 表，另一个是 Address 表，它们之间是一对多的。我想选择具有各自地址的所有客户并显示如下：

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

我的代码现在如下：

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

但它会导致两条记录具有相同的数据但不同的地址属性。我怎样才能实现上面的例子？

样本数据：

客户表：

id  | name | age
222 | John | 32

地址表：

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

当前结果：

[
  {
    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 脚本：

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

标签： phpjsonmariadb

解决方案

如果您需要解决数据库端的问题，您可以JSON_ARRAYAGG在查询中使用函数：

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

在这里你可以测试这个查询和 PHP 代码

PHP解决方案可以是下一个：

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

在线运行 MySQL 和 PHP 代码

推荐阅读