My issue: I am trying to make an system which checks if orders has their items in stock. I am using 3 databases for this.
Products Database:
Product ID Name Disposable
ABC123 Chair 15
AFG999 Table 1
CHO555 Sofa 15
Order Details:
ID Customer ID Date
1555 123 2016-04-12
2666 333 2016-04-13
Order Details:
ProductID Quantity OrderID
ABC123 5 1555
CHO555 2 2666
AFG999 2 2666
What I'm trying to achieve is that it iterates through the ordered products and checks if they are in stock. Also imagine that some orders will have over 20 products on them, how would I do this most efficiently?
What I have tried:
I have tried to solve it by iterating through the order details database and splitting them by a comma "," in php, like so:
$getorderdetails = mysqli_query($connect,
"SELECT corders.id, GROUP_CONCAT(corderdetails.productid) as productid,
GROUP_CONCAT(corderdetails.quantity) AS quantity
FROM corders, corderdetails
WHERE corders.id = corderdetails.orderid
GROUP BY corders.id
ORDER BY corders.id");
This outputs something like this:
Order ID Ordered Products Ordered Quantity
2666 CHO555,AFG999 2,2
So I tried splitting it up in arrays and searching the database one by on but I never really got it to work and I was wondering if someone else may share their solution?