categories and items 1 big array

Question

I've made some search on the forum without any good answers for my problem. If I missed something, feel free to link me to the question!

What I need to do is simple: a function that returns an array of the full tree of my categories and items. I only have 1 depth (item and a cat_id), so no recursion involved (though if you have a recursive solution, I would gladly accept it).

Right now, I've done this, but it's pretty bad, since I do multiple queries...

function build_tree()
{
    global $wpdb;

    $cats = $wpdb->get_results("SELECT * FROM wp_catering_cats");


    foreach($cats as &$cat)
    {
      $id = $cat->id;

      $cat->items = $wpdb->get_results("SELECT * FROM wp_catering_items WHERE cat_id = $id");
    }

    return $cats;
}

My tables are really simple:

wp_catering_items

id, cat_id, name, price

wp_catering_cats

id, name

Here is an exemple the results array I want:

    Array
    (
        [0] => array
            (
                [id] => 1
                [name] => Cat #1
                [items] => Array
                    (
                        [0] => array
                            (
                                [id] => 1
                                [cat_id] => 1
                                [name] => Item #1
                                [price] => 5
                            ),
                        ...

                    )

            ),
           ...
   );

If something is not clear, feel free to comment!

Thanks!

EDIT

I've made some modifications using the code bellow, but I' pretty sure there's a neater way to do this. Having to order one DESC and one ASC just doesn't sounds right..

function build_tree()
{
    global $wpdb;

    $cats = $wpdb->get_results("SELECT * FROM wp_catering_cats ORDER BY id DESC");
    $items = $wpdb->get_results("SELECT * FROM wp_catering_items ORDER BY cat_id ASC");

    $item = array_pop($items);

    foreach($cats as &$cat)
    {   
        while($item->cat_id == $cat->id)
        {
            $cat->items[] = $item;
            $item = array_pop($items);
        }
    }

    print_r($cats);
}

Answer 1

If you are just trying to optimize, then do the simple thing, instead of only grabbing the items for the specific cat you are on, grab all the items at once, and order them by catID. Then loop through your cats, and pop items off your item results until you hit the next cat.

function build_tree()
{
    global $wpdb;

    $cats = $wpdb->get_results("SELECT * FROM wp_catering_cats order by cat_id asc");
    $items = $wpdb->get_results("SELECT * FROM wp_catering_items ORDER BY cat_id asc");

    foreach($cats as &$cat)
    {
      $id = $cat->id;
      $item = array_pop($items)
      while($item['cat_id'] == $id)
      {
        $cats->item[] = $item;
        $item = array_pop($items)
      }
      #do a little bookkeeping so you next cat gets its first item, and zero item cats get skipped.

    }
}

Update: Thanks for the comment.. Forgot to add the pop in the while loop!

Second update: use array_shift instead of array_pop if you don't want reverse ordering to be a problem...