Combine one database table row with pivoted data from another database table in CodeIgniter model method

Question

I want to combine two arrays from two separate queries. I stored my products in two separate tables: one for general information like name, description, price, etc; and another for their various details i.e. a clothing items size and colour.

So my database is structured like this for products:

products table:
p_id | title | descr | etc

product_attrs table:
attr_id | products.p_id | name | value

where name and value can be name = Size value = Large

If I trying getting all the details for a product out in one query, like this:

this->db->select('products.title,
                  p.description,
                  p.price,
                  p.stock,
                  p.name,
                  p.value');
$this->db->from('p');
$this->db->where('p.p_id', $id);
$this->db->join('product_attrs', 'product_attrs.product_id = p.p_id', 'inner');
$result = $this->db->get();
        
return $result->result_array();

I get an array populated with the number of name/value pairs there are in the table product_attributes for that product. So if there are say 5 attributes of a product, I would get everything back 5 times like this:

Array (
    [0] => Array (
        [title] => Modest Swimsuit - Full body
        [description] => UV +50 Protection - Chlorine Resistant - Water Resistant - Quick Drying - Maximum Breathe Ability- Sea Water Resistant
        [price] => 59.95
        [stock] => 20
        [name] => Brand
        [value] => Modestly Active Swimwear
    )
    [1] => Array (
        [title] => Modest Swimsuit - Full body
        [description] => UV +50 Protection - Chlorine Resistant - Water Resistant - Quick Drying - Maximum Breathe Ability- Sea Water Resistant
        [price] => 59.95
        [stock] => 20
        [name] => Colour
        [value] => Black and Light Blue
    )
    [2] => Array (
        [title] => Modest Swimsuit - Full body
        [description] => UV +50 Protection - Chlorine Resistant - Water Resistant - Quick Drying - Maximum Breathe Ability- Sea Water Resistant
        [price] => 59.95
        [stock] => 20
        [name] => size
        [value] => small
    )
    [3] => Array (
        [title] => Modest Swimsuit - Full body
        [description] => UV +50 Protection - Chlorine Resistant - Water Resistant - Quick Drying - Maximum Breathe Ability- Sea Water Resistant
        [price] => 59.95
        [stock] => 20
        [name] => size
        [value] => medium
    )
    [4] => Array (
        [title] => Modest Swimsuit - Full body
        [description] => UV +50 Protection - Chlorine Resistant - Water Resistant - Quick Drying - Maximum Breathe Ability- Sea Water Resistant
        [price] => 59.95
        [stock] => 20
        [name] => size
        [value] => large
    )
)

So I decided to separate the queries for each table, so that I can get one result set each. But I want to combine them both so that I can return the data back as one array to the Controller and display that onto my view. This is how I've queried the two tables, I just need a way to combine the results for both:

$this->db->select('p.title,
                   p.description,
                   p.price,
                   p.stock');
$this->db->from('p');
$this->db->where('p_id', $id);
$result = $this->db->get();
        
$this->db->select('name, value');
$this->db->from('product_attrs');
$this->db->where('p_id', $id);
$result2 = $this->db->get();

If someone could please help I'd greatly appreciate it. Thank you

EDIT:

I'm looking at array_merge() function right now in php.net, but if I do this:

$result = $this->db->get();
$array1 = $result->result_array();

$result2 = $this->db->get();
$array2 = $result2->result_array();
$data = array_merge($array1,$array2);
return $data;

I get more than one array:

Array (
    [0] => Array (
        [title] => Modest Swimsuit - Full body
        [description] => UV +50 Protection - Chlorine Resistant - Water Resistant - Quick Drying - Maximum Breathe Ability- Sea Water Resistant
        [price] => 59.95
        [stock] => 20
    )
    [1] => Array (
        [name] => Brand
        [value] => Modestly Active Swimwear
    )
    [2] => Array (
        [name] => Colour
        [value] => Black and Light Blue
    )
    [3] => Array (
        [name] => size
        [value] => small
    )
    [4] => Array (
        [name] => size
        [value] => medium
    )
    [5] => Array (
        [name] => size
        [value] => large
    )
)

Is there a way to get the values out of the above array in my view?

Answer 1

Another alternative would be to loop through the second results array and add the values to the first array.

    // Get products results as an array
    $this->db->select('products.title,
                       products.description,
                       products.price,
                       products.stock');
    $this->db->from('products');
    $this->db->where('product_id', $id);
    $product = $this->db->get()->result_array();

    // Get product attributes as an array
    $this->db->select('name, value');
    $this->db->from('product_attributes');
    $this->db->where('product_id', $id);
    $product_attributes = $this->db->get()->result_array();

    // Loop through the results
    foreach($product_attributes as $attribute) {

         // Add results to original product array
         $product[$attribute['name']] = $attribute['value'];
    }

This should produce an array like this:

        [title]       => Modest Swimsuit - Full body 
        [description] => UV +50 Protection - Chlorine Resistant - Water Resistant - Quick Drying - Maximum Breathe Ability- Sea Water Resistant 
        [price]       => 59.95 
        [stock]       => 20
        [Brand]       => Modestly Active Swimwear 
        [Colour]      => Black and Light Blue
        [size]        => small