Hey coders, my first post will probably get some hate but I really want to achieve this..
I got 3 separate tables with rows belonging to a user all have a user id that identifies the owner
I want to get an array of user rows from all 3 tables. but making the key of each row from each table an unique_id thats saved within each..
This is my current model method
PHP Code:
$resultsarray = array();
$this->db->select('*');
$this->db->from('invoicestables');
$this->db->where('user_id', $this->_userid);
$this->db->where('status !=', 0);
$query = $this->db->get();
foreach($query->result_array() as $val){
$resultsarray['invoices'][$val['unique_id']] = $val;
}
$this->db->select('*');
$this->db->from('producttables');
$this->db->where('user_id', $this->_userid);
$this->db->where('status !=', 0);
$query = $this->db->get();
foreach($query->result_array() as $val){
$resultsarray['products'][$val['unique_id']] = $val;
}
$this->db->select('*');
$this->db->from('imagestables');
$this->db->where('user_id', $this->_userid);
$this->db->where('status !=', 0);
$query = $this->db->get();
foreach($query->result_array() as $val){
$resultsarray['images'][$val['product_unique_id']][$val['unique_id']] = $val;
}
return $resultsarray;
the array im aiming for looks like this.. .
Code:
[invoices] => array(
[invoice1_uniqueid]=> array(invoice row),
[invoice2_uniqueid]=> array(invoice row),
[invoice3_uniqueid]=> array(invoice row),
),
[products] => array(
[product1_uniqueid]=> array(invoice row),
[product2_uniqueid]=> array(invoice row),
[product3_uniqueid]=> array(invoice row),
),
[images] => array(
[product1_uniqueid] => array(
[product1_img1unique]=> array( image row)
),
[product2_uniqueid] => array(
[product2_img1unique]=> array( image row),
[product2_img2unique]=> array( image row)
),
)
Or even better it should look like this..
[invoices] => array(
[invoice1_uniqueid]=> array(invoice row),
[invoice2_uniqueid]=> array(invoice row),
[invoice3_uniqueid]=> array(invoice row)
),
[products] => array(
[product1_uniqueid]=> array(
[product1_images] => array(
[product1_images1_unique] => array(img1 row),
[product1_images2_unique] => array(img2 row)
),
[product1_id] => 'id1 column',
[product1_uniqueid] => 'product1_uniqueid column',
[product1_name] => 'name1 column',
[product1_price] => 'product1_price column'
),
[product2_uniqueid]=> array(
[product2_images] => array(
[product2_images1_unique] => array(img1 row),
[product2_images2_unique] => array(img2 row)
),
[product2_id] => 'id2 column'
[product2_uniqueid] => 'product2_uniqueid column',
[product2_name] => 'name2 column',
[product2_price] => 'product2_price column'
)
)
The question is. ..
can this be achieved with a more efficient join query or am I stuck with the code at top of individual queries and array building with each one..
basically I have no idea If a join query can return an array with some of it's values having an arrays of multiple results from the joined tables..
dont make fun if its stupid question to you.. I've been programming for 6 years and still have not found one use for left right inner outer join queries. and dont even understand parts of it ..
