Pseudo code for retrieving MySQL data with PHP associative arrays

Question

I have two MySQL tables, products and prodGroups. I need to retrieve the following data in this format of an associative array:

Array ( [0] => Product Group 1 => Item 1
                               => Item 2
                               => Item 3
        [1] => Product Group 2 => Item 4
                               => Item 5
                               => Item 6
        [2] => Product Group 3 => Item 7
                               => Item 8
                               => Item 9 )

The above was freely written so obviously that's not correct format of print_r for an assoc array, but hopefully you get the idea.

I'm having trouble comprehending to retrieve Items from a MySQL table where their prodGroup value matches the name of Product Group 1/2/3. I want the items belonging to a particular product group to be apart of its rightful parent array.

I'm not the best at explaining, hope what I've written is enough to point my question across. However, in summary if you're still lost, I need Item 1&2&3 to be apart of Product Group 1 in the array.

Pseudo code would be great, I have a feeling a while and foreach loop is required, I'm just totally lost for its structure.

Why do you need the data in a multidimensional array of this sort? What are you intending to do with it? You may well find that it's unnecessary to convert into this form... has the XY problem struck again? — eggyal
– eggyal, Commented Apr 29, 2013 at 15:42
I've got an unordered list as a Menu, each list item is titled as a Product Group's name, then another unordered list popping out where I wish to then list all the items of that Product Group. — user2324069
– user2324069, Commented Apr 29, 2013 at 15:47

Niels Keurentjes · Accepted Answer · 2013-04-29 15:45:51Z

You can solve this in one of two ways:

1) With nested queries. For small amounts of data, why not:

while($row = getNextProductGroup())
    $row->items = getItemsForGroup($row->ProductGroupId);

2) If you have lots of data this will be costly in performance, so you'll need a smarter way. Just join them together and pick it apart in PHP:

$productGroups = [];
while($row = getNextProductGroupAndItem()) {
    if(!isset($productGroups[$row->ProductGroupId])) {
        $row->items = [];
        $productGroups[$row->ProductGroupId] = $row;
    }
    $productGroups[$row->ProductGroupId]->items[] = $row;
}

Collectives™ on Stack Overflow

Pseudo code for retrieving MySQL data with PHP associative arrays

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related