How do I array push this 2-D array? (PHP)

Question

In this codeigniter model, I am trying to push the category name of each product. I want the array to look like:

    Array
(
    [0] => Array
        (
        [id] => 1
        [name] => Game 1!
        [category_id] => 3
        [category] => games //the category element is in the [0] array.
    )

    [1] => Array
        (
            [id] => 2
            [name] => Game 2
            [category_id] => 3
            [category] => games
        )

here's how the array looks right now:

Array
(
    [0] => Array
        (
            [category] => games //i want this to be in the [1] array
        )

    [1] => Array
        (
        [id] => 1
        [name] => Game 1!
        [category_id] => 3
    )

[2] => Array
    (
        [category] => games
    )

[3] => Array
    (
        [id] => 2
        [name] => Game 2
        [category_id] => 3
    )

here's my function that gets all the products and puts them in the array.

function getAllProducts(){
    $data=array();
    $Q=$this->db->get('products');
    if($Q->num_rows() > 0){
        foreach($Q->result_array() as $row){
            $Q2=$this->db->query("select name FROM categories 
            where id={$row['category_id']}");
            if($Q2->num_rows() > 0){
                foreach($Q2->result_array() as $row2){
                    //Trouble Here: dont know how to push this into the array.
                    //is there a function that i can put in the $data[___] 
                    //area so that it knows it is in the [0] element, then [1],etc?
                    $data[]['category']=$row2['name'];  
                }

            }    
            $data[]=$row;
        }
    }
    $Q->free_result();
    return $data;
}

Answer 1

I would try a different approach that reduces the number of total queries you need to make.

$productRows = getAllProducts();
$categoryRows = getAllCategories();

$categoriesById = array();

foreach ($categoryRows as $categoryRow) {

    $categoriesById[$categoryRow['id']] = $categoryRow;
}

$productsWithCategory = array();

foreach ($productRows as $productRow) {

    $categoryId = $productRow['category_id'];
    $productsWithCategory[] = array(
                 'id' => $productRow['id'],
               'name' => $productRow['name'],
        'category_id' => $categoryId,
           'category' => $categoriesById[$categoryId]['name']
    );
}

Answer 2

Either do:

foreach($Q->result_array() as $key=>$row){
        $data[$key]=$row;
        $Q2=$this->db->query("select name FROM categories 
        where id={$row['category_id']}");
        if($Q2->num_rows() > 0){
            foreach($Q2->result_array() as $row2){
                $data[$key]['category']=$row2['name'];  
            }

        }    

    }

or even better, make the right query:

function getAllProducts(){
    $data=array();
    $Q=$this->db->query("SELECT p.*, c.name as category FROM products p, LEFT JOIN categories c  ON c.id=p.category_id");
    if($Q->num_rows() > 0){
        foreach($Q->result_array() as $row){
             $data[] = $row;
        }
    }
    $Q->free_result(); // <- needed here? don't know
    return $data;
}

I don't know CodeIgniter, so I don't know whether this is totally correct. But in general this is much better, as you reduce the number of DB access which is always good.
This way, you have only one query instead of n+1 if you have n products.

P.S.: I bet CodeIgniter also provides some APIs to make a JOIN. Read the documentation.

I just saw, that you could do this:

$this->db->select('*');
$this->db->from('products');
$this->db->join('categories', 'categories.id = products.category_id', 'left');

$Q = $this->db->get();

Make use of the API.