Built a multidimensional array with PHP from an SQL query

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.

would you show us the result of $query->fetch(PDO::FETCH_ASSOC) by var_dump($query->fetch(PDO::FETCH_ASSOC)) — Mark
– Mark, Commented Nov 10, 2017 at 3:28

John Ellmore · Accepted Answer · 2017-11-10 02:51:03Z

1

There are a few different problems in your code:

Your desired array structure isn't valid, so it's unclear what you want.
$datas isn't initialized. Use $datas = [] instead.
$FRU_Name is being set twice? Did you mean $FRU_Quantity?
This code won't group multiple fruits under their continent ID--it'll put them into an associative array alongside their continent ID.

answered Nov 10, 2017 at 2:51

John Ellmore

2,2291 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ArtisticPhoenix Over a year ago

Good Point! I didn't even notice that. have to do [ [ 'CON_ContinentId' => 1, ..], [ ...] ]

user5434892 Over a year ago

Hello, I changed the code according to your helpful comments. Could you please try reach the goal? Thanks.

John Ellmore Over a year ago

Your desired structure still isn't valid--you have arrays where there are two values with the same key.

user5434892 Over a year ago

Could you please propose me a new structure depending my need? I think you understand it. I'm looking my documentation near my wish but arrays are difficult for me.

John Ellmore Over a year ago

something like

$continents = [ 1 => [ ['FRU_Name' => '...', 'FRU_Quantity'] => '...'],  ['FRU_Name' => '...', 'FRU_Quantity' => '...'] ], 2 => [ .. ] ]

is probably what you want.

|

mickmackusa · Accepted Answer · 2017-11-10 03:39:35Z

0

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)

edited Nov 10, 2017 at 3:39

answered Nov 10, 2017 at 3:30

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

2 Comments

user5434892 Over a year ago

Ho it's working man. Thanks so much for your additional explanations. It helps me a lot.

user5434892 Over a year ago

Thanks mickmackusa!

Collectives™ on Stack Overflow

Built a multidimensional array with PHP from an SQL query

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related