Nested associative array from DB fetch

Question

i want to do the following but haven't found how to.

Or i'm not sure if there is a better way / practice, if you can point me that way i will appreciate it.

I have 2 tables on my DB

Table1 : Categories
Table2 : Subcategories

Table2 is associated with table1 by the primary key.

So for 1 row in the table1 i can have multiple associated rows on the table2.

I query and fetch the data on my Table1 like this:

$result = $mysqli->query("SELECT * FROM table1");

$post = array();
    while ($row = $result->fetch_object()){
        $row->sub = get_subcategories($row['id']); /* this value is added to the array since i want to store in here the associated arrays from the table2 */
        $post[] = $row;
    }
return $post;

Then i do the following query also on another function for the table2 data based on every id from the table1

function get_subcategories($id){
   $result = $mysqli->query("SELECT * FROM table2 where categories_id = '$id'");

   $post = array();
    while ($row = $result->fetch_object()){
        $post[] = $row;
    }
return $post;
}

But the result im getting when using var_dump doesn't seem right.

Can i do it? or what am i doing wrong?

Thanks in advance

You're assigning the array to $post, but returning $posts (with an extra s). — Barmar
– Barmar, Commented Jan 15, 2014 at 13:12
$row['sub'] won't work when you use fetch_object, it should be $row->sub. — Barmar
– Barmar, Commented Jan 15, 2014 at 13:16
@barmar didn't noticed, just fixed it, mixed a code when i pasted it. thanks — user3066721
– user3066721, Commented Jan 15, 2014 at 13:22

Barmar · Accepted Answer · 2014-01-15 13:26:00Z

1

Use a single JOIN query:

$result = $mysqli->query("SELECT * FROM table1 t1 JOIN table2 t2 ON t2.categories_id = t1.id ORDER BY t1.id"); 
$posts = array();
while ($row = $result->fetch_object) {
    if (!isset($posts[$row->categories_id])) {
        $posts[$row->categories_id] = $row;
        $posts[$row->categories_id]->sub = array();
    }
    $posts[$row->categories_id]->sub[] = $row;
}

This will create nested arrays. The first level will be an associative array whose keys are category IDs and values are objects. The objects will have a sub property that is an array of all the subcategories. Both the categories and subcategories are the objects returned by fetch_object, so there will be duplication. But you can just refer to the appropriate object properties for a particular level.

edited Jan 15, 2014 at 13:26

answered Jan 15, 2014 at 13:20

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

3 Comments

Barmar Over a year ago

Try it and use var_dump() to see.

user3066721 Over a year ago

Ok i tried and it seems to be working, or at least it's bringing the proper ammount of rows on table2 but i'm getting the same result for every item like this Electronica Celulares Celulares Celulares Celulares Celulares Maybe i'm doing something wrong on the loop, but almost there. :)

Barmar Over a year ago

All the elements of a particular sub array will be the same except for the properties specific to subcategories.

Collectives™ on Stack Overflow

Nested associative array from DB fetch

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related