Welcome Guest, Not a member yet? Register   Sign In
Best practice to return multidimensional array
#1

(This post was last modified: 05-06-2020, 12:13 AM by jreklund.)

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 ..
Reply
#2

@adqda,

I'm going to assume that you are using a current version of MySQL. Why don't you create a database view that has all of the information you need from all three tables. Then in your CI model. All you have to do is call the database view and you will have all the information you need.
Reply
#3

(This post was last modified: 05-06-2020, 09:57 AM by adqda.)

(05-05-2020, 09:39 PM)php_rocs Wrote: @adqda,

I'm going to assume that you are using a current version of MySQL.  Why don't you create a database view that has all of the information you need from all three tables.  Then in your CI model.  All you have to do is call the database view and you will have all the information you need.

hey thanks for your reply .. yes latest SQL and php 7.3

I never even thought about it .. Ive worked very little with views. I understand a database view is basically a fake database.. but how would the array nesting be any different than for a real table/s .

I guess the question is .  Do database views provide extra options to nest results from other tables within column values ,, ? in this case getting all the product images inside the specific product array under a product image column? and in this case naming the keys with column values

Sorry I dont have the jargon yet. Thanks for your suggestion.. I will definitely look into views further, be it or not what I need for this instance
Reply
#4

@adqda,

You are basically running 3 queries and using their results to build an array. What I'm saying is instead of running three queries, merge them into one database view with specific field names (from each table). Then call the database view in the application and take the results and put it into an array. Although CI does put it into an array for you it might not be in the array format that you need ( https://codeigniter.com/userguide3/datab...ult-arrays ). I'm assuming that you are using CI3.
Reply
#5

Hey thanks for asking this question it is very helpful for me as a new member I learn lot of things from this post.
An enthusiastic person who loves logo design and redevelopment and working for https://britishcoursework.com in UK
Reply




Theme © iAndrew 2016 - Forum software by © MyBB