checking if the all the values in rows are equal in table

[mythri]

Hello,

 

I am trying to check whether the values in rows are equal or same?

 

I have two tables orders and line_items.  I will store more than 1 line items in line_items table with the same order_id getting from orders table. In line items if i have 3 line items for same order_id, when each line items gets dispatched i will set the status to 'Dispatched' in line_items table. When all the 3 becomes 'Dispatched', i will update the order status as 'Completed' in orders table. 

 

Now i am not getting how to check whether all the 3 line items status id 'Dispatched'.

 

I tried doing like this

 

$q1 = "select orders.order_id, orders.item_status as istatus, orders.status as ostatus, orders.authorise, line_items.order_id, line_items.status as bstatus, COUNT(line_items.id) as bid from orders inner join line_items on orders.order_id=line_items.order_id where line_items.order_id=".$id." AND line_items.id<>'Dispatched'";




$q2 = mysql_query($q1);
while($q3 = mysql_fetch_array($q2))
{
echo $q3['bstatus'];
if($q3['bstatus']=='');
{
echo "HELLLOOOO";
}
}

But its not working. It goes into if loop even if a single value is 'Dispatched'

 

Please help me.

 

 

 

 

 

 

[mythri]

can somebody please help me....

[requinix]

Sometimes it takes more than an hour to get an answer.

 

It'd be easier to check for any items that are not Dispatched than to check that all items are.

[mac_gyver]

i would get a COUNT() of rows with the same orders.order_id and a SUM() of the line_item.status values for that order that are equal to Dispatched and test if they are the same or not.

 

just the relevant bits of your query, would be - 

SELECT o.order_id, IF(COUNT(*) = SUM(IF(li.status = 'Dispatched',1,0)),'Completed','Not Complete') as order_status
 FROM orders o
 JOIN line_items li on o.order_id = li.order_id AND o.order_id = $id
 GROUP BY o.order_id

btw - as mentioned in one of your previous threads, you need to use table alias names in your query to simplify it and make it more readable. some new-lines would help to format it as well.

 

also, by putting the o.order_id = $id into the join condition, you will limit the rows that get operated on to just those you are interested in. when you use a where condition -  WHERE o.order_id = $id, the join condition joins ALL the corresponding rows from both tables, so, if you had 100's of thousands of orders, you would join the 100's of thousands of rows in the orders table to the corresponding rows in the line_items table.

 

edit: you would also not store the result of this into the orders table. it is derived information and storing it will create data sync problems. what happens if someone returns an item and the status for that item changes from Dispatched to Returned? if you store this derived status in the orders table, you must now have more code and queries to update it. if you don't store this derived status, but calculate it as needed, you don't need to do anything extra when a status changes.

[Barand]

Test data

mysql> SELECT * FROM orders;
+----------+------------+--------+
| order_id | order_date | status |
+----------+------------+--------+
|        1 | 2014-11-01 |        |
|        2 | 2014-11-02 |        |
|        3 | 2014-11-03 |        |
|        4 | 2014-11-04 |        |
|        5 | 2014-11-05 |        |
+----------+------------+--------+

mysql> SELECT * FROM line_items;
+--------------+----------+------------+
| line_item_id | order_id | despatched |
+--------------+----------+------------+
|            1 |        1 |          0 |
|            2 |        1 |          1 |
|            3 |        1 |          1 |
|            4 |        2 |          1 |
|            5 |        2 |          1 |
|            6 |        2 |          1 |
|            7 |        3 |          0 |
|            8 |        3 |          0 |
|            9 |        3 |          0 |
|           10 |        4 |          0 |
|           11 |        5 |          1 |
+--------------+----------+------------+

Query to update orders to "COMPLETED" id all items despatched

UPDATE orders
LEFT JOIN line_items ON orders.order_id = line_items.order_id
    AND despatched = 0
SET orders.status = 'COMPLETED'
WHERE line_items.order_id IS NULL;

Resulting orders table

mysql> SELECT * FROM orders;
+----------+------------+-----------+
| order_id | order_date | status    |
+----------+------------+-----------+
|        1 | 2014-11-01 |           |
|        2 | 2014-11-02 | COMPLETED |
|        3 | 2014-11-03 |           |
|        4 | 2014-11-04 |           |
|        5 | 2014-11-05 | COMPLETED |
+----------+------------+-----------+

[mythri]

Thanks a ton Barad. This is exactly what i wanted.