使用SQL查询从PHP构建多维数组

Question

Form a SQL query i receive the following datas from two differences tables.

|----------------|----------|--------------|
|CON_ContinentId | FRU_Name | FRU_Quantity |
|----------------|----------|--------------|
| 1              | Apple    | 100          |
| 1              | Banana   | 200          |
| 2              | Peach    | 300          |
| 2              | Cherry   | 400          |
| 3              | Coconut  | 500          |
| 4              | Grape    | 1100         |
| 5              | Pear     | 1500         |
|----------------|----------|--------------|

I need to get an array like this:

$datas = [ 
    1 => [ 
        [
            'FRU_Name' => 'Apple', 
            'FRU_Quantity' => '100'
        ], 
        [
            'FRU_Name' => 'Banana', 
            'FRU_Quantity' => '200'
        ]
    ],
    2 => [ 
        [
            'FRU_Name' => 'Peach', 
            'FRU_Quantity' => '300'
        ], 
        [
            'FRU_Name' => 'Cherry', 
            'FRU_Quantity' => '400'
        ]
    ],
    3 => [ 
        [
            'FRU_Name' => 'Coconut', 
            'FRU_Quantity' => '500'
        ]
    ],
    4 => [ 
        [
            'FRU_Name' => 'Grape', 
            'FRU_Quantity' => '1000'
        ]
    ],
    5 => [ 
        [
            'FRU_Name' => 'Pear', 
            'FRU_Quantity' => '1100'
        ]
    ],
]

So, basically, I need to groupe the rows with the same CON_ContinentId and list the FRU_Name and FRU_Quantity.

What I try:

$datas = [];

while ($fetch = $query->fetch(PDO::FETCH_ASSOC)){
    $CON_ContinentId = $fetch['CON_ContinentId'];
    $FRU_Name = $fetch['FRU_Name'];
    $FRU_Quantity = $fetch['FRU_Quantity'];

    if (!in_array($CON_ContinentId, $datas)) {
        $datas = array(
            'CON_ContinentId' => $CON_ContinentId,
            'FRU_Name' => $FRU_Name,
            'FRU_Quantity' => $FRU_Quantity
        );
    }
}

But it doesn't work.

Could you please help me ?

Thanks.

Answer 1

Every iteration of your results set, just use the CON_ContinentID value as the key, then [] to auto-index the subarrays.

There is no need to declare resultset values to new variables, just apply them to the $datas declaration and move on.

while ($fetch = $query->fetch(PDO::FETCH_ASSOC)){
    $datas[$fetch['CON_ContinentId']][]=['FRU_Name'=>$fetch['FRU_Name'],'FRU_Quantity' =>$fetch['FRU_Quantity']];
}

a bit more explanation...

//                   hard-coded keys -vvvvvvvv-----------------------vvvvvvvvvvvv
$datas[$fetch['CON_ContinentId']][]=['FRU_Name'=>$fetch['FRU_Name'],'FRU_Quantity' =>$fetch['FRU_Quantity']];
//  id-^^^^^^^^^^^^^^^^^^^^^^^^^ ^^-auto-index   ^^^^^^^^^^^^^^^^^^------------------^^^^^^^^^^^^^^^^^^^^^^-resultset data
//                                  ^^^^^^^^^^-keys start from zero, and increment by one at each new pushed subarray)