savvas999 savvas999 - 2 months ago 7
MySQL Question

Select field from table only when all rows paired to same ID have a date

I am trying to retrieve some info from the DB.

I have the following table

orderNo item shipping
1050 1 0000-00-00
1050 2 0000-00-00
1050 6 2016-09-18
2000 2 0000-00-00
2006 1 2016-09-05
2006 2 0000-00-00


There are some orders paired with multiple items. When the item is 2, I want to automatically add a shipping date (that functionality works fine). But the script will need to be added ONLY when all the rest items has a date attached to them.

For example:
-> order number '1050': Item 6 has a date but Item 1 has no date, so we won't add shipping date to 2
-> order number '2000': There is only item 2 there, so we are good to go, shipping date will be added
-> order number '2006': Item 1 has a date, so again, we can add date to item 2 as well.

In other words, when the order has an item other than 2, and at least one of the other paired items has no shipping date then I want to ignore it

//adding the shipping date to item 2
$sql = "UPDATE orders
SET shipping = '".$shipping."'
WHERE orderNo = '".$orderNo."' AND item = '2'";
Db::getInstance()->query($sql);

//add some kind of query to check whether shipping of rest items is not zero. If it is '0000-00-00' then do not proceed
SELECT orderNo FROM orders WHERE orderNo = '".$orderNo."' AND shipping in other items than 2 has a date
//If query returns true then proceed and execute the following
$new_payment = new itemPayment();
//else do nothing


So in the above query, I am passing the order number (eg. '1050') and I somehow want to check if all the other items (other than 2) has a shipping date. If they all have then proceed, if not then do nothing.

I tried different queries but nothing worked. Can it be done only with mysql queries? or does it need some PHP code too?

would really appreciate if anything can help me with that or at least lead me to the right path.

Answer

An understandable way to do this, is going for a query:

SELECT orderNo FROM orders WHERE orderNo = '".$orderNo."' AND item !=2 AND shipping>'0000:00:00'

Then run php num rows to see how many rows match to your query. If the result is more than 0, that means you should not proceed