I'm working on what I think is a fairly complex set of queries and then sorting of data. I can get everything to work but only if I nest two queries in a while loop. I know this is bad, but this is the only way I'm able to get it to work. Is this ok to do on a limited basis, or is there always a way to do it without nesting? I basically need to get multiple rows from one table then pull multiple rows from another table to match them to. Maybe I'm not seeing a better way to execute this?
The whole purpose of this is to look into the past of product inventory. I want to be able to go back in time and figure out how many of each product existed and what the total value of the inventory was based on the month and year selected. Hopefully this makes sense.
Product Query - simply selects the products to generate the initial product list.
$query = "SELECT products_id, products_coid, products_name, products_inventory, products_size, products_inventory, products_inventory AS proInv, size_name
FROM products
LEFT JOIN sizes ON products.products_size = sizes.size_id
WHERE products_coid = '$companyId' AND products_category = '$catId' AND products_status = 'A'
GROUP BY products_id
ORDER BY products_name, products_id";
$result = mysql_query($query);
Order Query - finds how many of each product has been sold based on the month and year selected.
while($row = mysql_fetch_assoc($result)) {
extract($row);
$query2 = "SELECT orderitems_productid, SUM(orderitems_quantity) AS proSum
FROM orderitems
LEFT JOIN orderinfo ON orderitems.orderitems_oid = orderinfo.orderinfo_id
LEFT JOIN products ON orderitems.orderitems_productid = products.products_id
WHERE orderitems_productid = $products_id AND date_format(`orderinfo_date`, '%m') >= '$monthId' AND date_format(`orderinfo_date`, '%Y') = '$yearId'";
$result2 = mysql_query($query2);
while($row2 = mysql_fetch_assoc($result2)) {
extract($row2);
}
Inventory History Query - This table contains all inventory additions and subtractions. These are separate additions and subtractions from orders. This table could have multiple rows that need to be pulled to match against the first query.
$query1 = "SELECT inventory_id, inventory_proid, inventory_quantity, inventory_cost
FROM inventory
WHERE inventory_proid = $products_id AND date_format(`inventory_create`, '%m') <= '$monthId' AND date_format(`inventory_create`, '%Y') = '$yearId'
ORDER BY inventory_create DESC";
$result1 = mysql_query($query1);
There is some php math and such that goes here.
html that is displayed via the loop would go here.
}
