php – 从一对多的mysql结果创建多维数组

发布时间：2020-12-13 21:53:07 所属栏目：PHP教程来源：网络整理

导读：我有三个 mysql表：客户,汽车和保险.每个客户可以拥有多辆汽车,每辆汽车都有多种保险政策.这是我的查询和sqlfiddle的链接： SELECT a.*,b.id AS car_id,b.license_number,b.brand,b.model,c.id AS insurance_id,c.insurance_company,c.start_date,c.end_date

我有三个 mysql表：客户,汽车和保险.每个客户可以拥有多辆汽车,每辆汽车都有多种保险政策.这是我的查询和sqlfiddle的链接：

SELECT
  a.*,b.id AS car_id,b.license_number,b.brand,b.model,c.id AS insurance_id,c.insurance_company,c.start_date,c.end_date,c.price
FROM clients a
LEFT JOIN cars b ON a.id = b.client_id
LEFT JOIN insurance c ON b.id = c.car_id

http://sqlfiddle.com/#!2/773a5/1

因此,对于两个客户,其中第一个客户有两辆车和三个保险单,第二个客户没有车和没有保险,查询将返回4行.

我无法弄清楚的是如何循环此查询以返回以下结构,同时避免重复的客户端条目.

Array
(
  [0] => Array
    (
      [id] => 1
      [first_name] => John
      [last_name] => Smith
      [cars] => Array
        (
          [id] => 1
          [license_number] => 'plate1'
          [brand] => 'BMW'
          [model] => 'E35'
          [insurance] => Array
            (
              [id] => 1
              [start_date] => '2015-02-10'
              [end_date] => '2016-02-10'
              [price] => '100'
            )

            (
              [id] => 2
              [start_date] => '2014-02-10'
              [end_date] => '2015-02-10'
              [price] => '50'
            )
        )

        (
          [id] => 2
          [license_number] => 'plate2'
          [brand] => 'VW'
          [model] => 'Golf'
          [insurance] => Array
            (
              [id] => 3
              [start_date] => '2015-02-10'
              [end_date] => '2016-02-10'
              [price] => '100'
            )
        )
    )

  [1] => Array
    (
      [id] => 1
      [first_name] => John
      [last_name] => Smith
      [cars] => NULL
    )
)

解决方法

这很容易.它是关于使用assoc数组.您可以使用id,car_id和insurance_id作为数组键.

因此,假设您将结果作为存储在$rows变量中的获取的assoc行.简单的循环将是：

$result = array();
foreach($rows as $row) {
    $id    = $row['ID'];
    $carId = $row['CAR_ID'];
    $insId = $row['INSURANCE_ID'];

    $result[$id]['first_name'] = $row['FIRST_NAME'];
    $result[$id]['last_name']  = $row['LAST_NAME'];

    if(!isset($result[$id]['cars'])) {
        $result[$id]['cars'] = array();
    }

    if($carId && !isset($result[$id]['cars'][$carId])) {
        $car = array(
            'id'             => $row['CAR_ID'],'license_number' => $row['LICENSE_NUMBER'],'brand'          => $row['BRAND'],'model'          => $row['MODEL'],'insurance'      => array()
        );
        $result[$id]['cars'][$carId] = $car;
    }

    if($insId && isset($result[$id]['cars'][$carId]) && !isset($result[$id]['cars'][$carId]['insurance'][$insId])) {
        $insurance = array(
            'id'         => $row['INSURANCE_ID'],'start_date' => $row['START_DATE'],'end_date'   => $row['END_DATE'],'price'      => $row['PRICE']
        );
        $result[$id]['cars'][$carId]['insurance'][$insId] = $insurance;
    }
}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!