[SOLVED] MySQL Query Works In MySQL QB But Not PHP

[chbrules]

So I created a MySQL query that works perfectly fine when I test it against my databases in MySQL Query Browser, but when I use the PHP mysql_query() function it just doesn't work, period. No errors, no nothing. This is part of a big project I'm doing for someone, but I just can't seem to figure out why it won't work. I suspect it has something to do with IFNULL():

[code]SELECT inventory.id, inventory.name, IFNULL(inventory.count - shipments_inventory.count, inventory.count), inventory.bin, inventory.unit_value, inventory.donation_id
FROM ss.inventory
LEFT JOIN shipments_inventory
ON inventory.id = shipments_inventory.inventory_id
ORDER BY name DESC
LIMIT 2000[/code]

[.josh]

can you post the actual code you are trying to use the query in?

[Nhoj]

You would need to do something more than just select the items from the DB, try doing this..

[code=php:0]$query = mysql_query('SELECT inventory.id, inventory.name, IFNULL(inventory.count - shipments_inventory.count, inventory.count), inventory.bin, inventory.unit_value, inventory.donation_id
FROM ss.inventory
LEFT JOIN shipments_inventory
ON inventory.id = shipments_inventory.inventory_id
ORDER BY name DESC
LIMIT 2000');

while ($inventory = mysql_fetch_assoc($query)) {
    echo $inventory['id'].' - '.$inventory['name'];
}
[/code]

[chbrules]

Well how come the select statement works just fine in MySQL QB and not through PHP's mysql extension? I use my other mysql queries with mysql_query() and mysql_result() all the time. I think I've only had this issue one other time and I just tossed the idea and made a less efficient thing.

[.josh]

again, post your code.

[chbrules]

Well I think a better way to go about this would be to ask this question:

Say I have 2 tables, one of all inventory ever recieved, then another list of all inventory ever shipped out. Comparing both lists will give you the current inventory. Say I have this setup:

Table 1 (inventory):
id
count

Table 2 (shipments inventory):
id
inventory_id
count


As you can see, when you ship inventory, it makes a new line in the database of which item shipped and how many. So you can see how it's comparative in that way.

What I propose is how do I pull a list of all current inventory in stock? I have to do 2 comparisons, one of which items don't exist in the shipment table (no items ever shipped), and one of which compares how many are in stock to how many have actually shipped total (to see if that item is sold out). Maybe I'm just having a mind fart here, but maybe someone can help me out?