CODEWITHSUNDEEP
phpmultidimensional-arraymysqli
user3066721
user3066721

Reputation:

Nested associative array from DB fetch

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

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

Upvotes: 1

Views: 462

Answers (1)

Barmar
Barmar

Reputation: 780974

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.

Upvotes: 1

Related Questions