multiple query results in a loop to be passed in a view

[eluser]rei[/eluser]
I'm having a hard time rewriting my working code to MVC. w/c implements cart options/variations that affects prices of a product using the CI cart library. what I did is query the products that are needed to be displayed and run a loop for the fetched products and query the option keys like color,size etc.. then make a loop again for the option keys and query the option values like for color: red,yellow, blue etc. etc.. But I think its really bad for performance because I'm making too many queries. Do u have a better idea of how will I do that in a better/nicer way? without making too many queries.

heres the logic I did: 1. query the products 2. query the option_keys (color,size etc) for each product. 3. query the option_values (red,yellow,blue etc.. ) for each option_keys. Please suggest me a better solution than this one. Thanks

[eluser]boltsabre[/eluser]
Try using joins, you should be able to get everything you need from your database with one query. And then cache your result ;-)

[eluser]rei[/eluser]
How will I do that sir? hmm. I think I cannot do that for just using one query because 1 product has many option_keys (color,size,weight) and 1 option_key has many option_values(red,yellow,blue, small,medium,large,5kgs,2kgs, ) for example.

and nwei my problem is similar to this one -- http://ellislab.com/forums/viewthread/80539/P15

[eluser]rei[/eluser]
Up. Can someone please help me?


Hi all, I managed to solve my problem. It now supports product variations that affects the price of a product. The code is not yet clean because I just want to learn the concept of product variations so dont worry if the code is not yet optimized/clean. If you have time please have a look at the source code to see if the logic is okay/acceptable. Thanks in advance and nwei I worry about the queries I have made. because its really not good for performance because I'm making too much queries. 1. querying the products 2. querying the option_keys FOR EACH product 3. querying the option_values FOR EACH product_key. So if you have an idea on how can I minimize the number of queries please share it to me Thank you so much here is the source -- https://github.com/basco-johnkevin/Cart_Igniter

[eluser]CroNiX[/eluser]
In your loop, instead of performing a new query, just store the IDs in an array. Then use where_in('key', $id_array) in a single query.

[eluser]rei[/eluser]
Thanks. here is my database table structure.

products table:
id, name, price

option_keys table:
id, prod_id, option_key

option_values table:
id, option_id, option_value, price



Here's what I did:

1. query all the products and store the product_id's in an array
2 SELECT * FROM option_keys WHERE prod_id IN ($prod_ids);

I'm getting this result:

id prod_id option_key
1 5 size
3 5 color
4 6 weight
6 6 style
7 5 font



Now the question is how can I assign the option_keys to be an array or object and assign them to their corresponding parent(prod_id)

[eluser]CroNiX[/eluser]

Code:

$products = array();
foreach($option_keys as $option)
{
  $products[$option['prod_id']] = $option;
}
//then when you're looping through your products...
print_r($products[this_product_id]);

[eluser]rei[/eluser]
Thanks. But I can only store 1 option key per product. What I need is to store many option_keys per 1 product. How can I do that sir? Thanks in advance

[eluser]rei[/eluser]
It works! Thank you sir...

Here is my new code:

Code:

function get_all_products()
    {

        $query = $this->db->query('SELECT * FROM products');

        $option_keys_query =  $this->db->query('     SELECT *
                                                    FROM option_keys
                                                    WHERE prod_id
                                                    IN ( 5, 6 )     ');

        $option_values_query =  $this->db->query('     SELECT *
                                                    FROM option_values
                                                    WHERE option_id
                                                    IN ( 1, 3, 4, 6, 7 )     ');


        $products_array = array(); // array the will hold all the products



        // option values
         foreach ($option_values_query->result() as $option_value)
         {
              
            $option_values[ $option_value->option_id ][] = $option_value;
            
        }
        // print_r($option_values);
      




        // option keys
       foreach ($option_keys_query->result() as $option_key)
       {
            // associate the option values to their corresponding option_keys
            $option_key->option_values = isset($option_values[$option_key->id]) ? $option_values[$option_key->id] : '';

            $option_keys[ $option_key->prod_id ][] = $option_key;
       }

        // print_r($option_keys);




        // products
       foreach ($query->result() as $product) {
            
            // check if there are available option keys related to this product id
            // if there is none, do not assign a option_key to the product
            if (isset($option_keys[$product->id]))
            {
                $product->option_keys = isset($option_keys[$product->id]) ? $option_keys[$product->id] : '';
            }
            
            // print_r($product);

            $products_array[] = $product;
            
       }

       print_r($products_array);

    
    
        
    }

This is the output of the products array for 1 product:

Code:

Array
(
    [0] => stdClass Object
        (
            [id] => 5
            [name] => nike tshirt
            [price] => 10
            [option_keys] => Array
                (
                    [0] => stdClass Object
                        (
                            [id] => 1
                            [prod_id] => 5
                            [option_key] => size
                            [option_values] => Array
                                (
                                    [0] => stdClass Object
                                        (
                                            [id] => 1
                                            [option_id] => 1
                                            [option_value] => small
                                            [price] => 2
                                        )

                                    [1] => stdClass Object
                                        (
                                            [id] => 2
                                            [option_id] => 1
                                            [option_value] => medium
                                            [price] => 3
                                        )

                                )

                        )

                    [1] => stdClass Object
                        (
                            [id] => 3
                            [prod_id] => 5
                            [option_key] => color
                            [option_values] => Array
                                (
                                    [0] => stdClass Object
                                        (
                                            [id] => 3
                                            [option_id] => 3
                                            [option_value] => red
                                            [price] => 6
                                        )

                                    [1] => stdClass Object
                                        (
                                            [id] => 4
                                            [option_id] => 3
                                            [option_value] => blue
                                            [price] => 4
                                        )

                                )

                        )

                    [2] => stdClass Object
                        (
                            [id] => 7
                            [prod_id] => 5
                            [option_key] => font
                            [option_values] => Array
                                (
                                    [0] => stdClass Object
                                        (
                                            [id] => 9
                                            [option_id] => 7
                                            [option_value] => italic
                                            [price] => 3
                                        )

                                    [1] => stdClass Object
                                        (
                                            [id] => 10
                                            [option_id] => 7
                                            [option_value] => bold
                                            [price] => 250
                                        )

                                )

                        )

                )

        )

Now the problem is how can I display the contents of that array.hmmm

[eluser]rei[/eluser]
problem solved. Thanks sir for your help

Here is the source w/c will be helpful for others who will also encounter my problem

https://github.com/basco-johnkevin/Cart_Igniter/