use foreach or single query? (Counting Inventory)

[eluser]seanloving[/eluser]
[quote author="noctrum" date="1264212125"]Can you post the actual SQL being run by enabling profiler?

Also, you can use IFNULL to display 0 instead of NULL

Code:

SELECT IFNULL(qty, 0) AS qty

[/quote]

@noctrum-
I like IFNULL, but not sure if I'm using it properly.

Take a look at my SQL and output [EDIT --> remove link]

there are many products in my products table...
but my inventory table has only 3 entries...
-(inventory_id=1) product_id=1
-(inventory_id=2) product_id=2
-(inventory_id=3) product_id=2

I want to see all the products in my products table on display here, with QTY column showing 0 for those products that don't show up on the inventory table.

-SL

[eluser]danmontgomery[/eluser]
SQL looks correct, you're almost certainly being limited by the WHERE statement... If there are only 3 rows in inventory, you are joining location on inventory, then limiting location, so you have a maximum of 3 rows with that query... If you remove the WHERE you should see all products... Just confirmed this locally. (IFNULL is unnecessary with COUNT(), which returns 0 for NULL)

Code:

SELECT COUNT(inventory.serial_no) AS qty, products.id AS product_id, products.name AS product_name
FROM products
LEFT JOIN inventory ON inventory.product_id = products.id
LEFT JOIN locations ON locations.id = inventory.location_id
GROUP BY products.id

qty product_id product_name
2 1 Product 1
1 2 Product 2
0 3 Product 3
0 4 Product 4
0 5 Product 5
0 6 Product 6

[eluser]seanloving[/eluser]
@noctrum-

Thanks. Yes, removing the WHERE clause works to get an inventory count for all locations... of course, I need the count at a certain $location. Fun stuff.

-SL

[eluser]danmontgomery[/eluser]
Ah, I think I get it now.

You can get the location ID first, then add it to the join statement instead of using WHERE:

Code:

SELECT COUNT(inventory.serial_no) AS qty, products.id AS product_id, products.name AS product_name
FROM products
LEFT JOIN inventory ON ( inventory.product_id = products.id AND location_id = 2 )
GROUP BY products.id

qty product_id product_name
1 1 Product 1
0 2 Product 2
0 3 Product 3
0 4 Product 4
0 5 Product 5
0 6 Product 6

[eluser]seanloving[/eluser]
Hooray that works. THANKS. Sincerely, Sean Loving

Code:

function get_inventory($location_id)
    {
        // for a given $location_id, return the product list showing product inventory levels
        return $this->CI->db
                        ->select('count(inventory.inventory_id) AS qty, part_number, description, products.product_id, locations.inventory_id')
                        ->from('products')
                        ->join('inventory', 'inventory.product_id = products.product_id AND location_id='.$location_id, 'left')
                        ->join('locations', 'locations.location_id=inventory.location_id', 'left')
                        ->group_by('product_id')
                        ->get()->result_array();
    }