MySQL and PHP. Should the code below be a MySQL Routine or PHP function?

Question

I'm working on a LAMP project. This isn't for a customer. It's just a learning experience. The project simulates a warehouse that tracks orders as they're placed, assembled, and shipped. When all items for an order are assembled, I want the order to automatically update its status to "ready to ship."

I don't know if I can write this logic in a MySQL routine, or if I should write it in the PHP code.

I’ll focus on two key tables: sale and sale_detail.

Sale has two important columns: id and stage. Id is the customer order number. It’s unique, primary key, and auto-incremented. Stage is the stages the order goes through: 0 (in progress), 1 (retrieving from warehouse), 2 (ready to ship), and 3 (shipped).

Sale_detail has the item list for each sale. It has three columns: order_id, item_id, and status. Order_id is a foreign key to the id column in sale. Item_id is the SKU of each item in the order. Status in this table is binary: 0 means it hasn’t been retrieved from the warehouse. 1 means it has.

Here’s some sample data:

Sale
id…..stage
10…..3
11…..3
12…..1
13…..1
14…..2

Sale_detail
order_id….item_id….status
10….43…1
10….45…1
10….42…1
11….42…1
12….45…0
12….43…1
13….44…1
13….48…1
14…42…2

In the example above, orders 10 and 11 are shipped (stage 3). Order 14 is ready to ship (stage 2). Orders 12 and 13 are at the warehouse for retrieval (stage 1).

Look at orders 12 and 13 in the sale_detail table. Order 12 is halfway ready. One item has been retrieved, but the other item hasn’t. Order 13 is ready to ship; both its items have been retrieved from the warehouse (status 1).

So if order 13 has all its items set to Status 1, its stage in the sale table should be set to 2. I want a process that will do this automatically. First, it finds all orders in sale that have status 1. For each such order, it finds all the items for that order in sale_detail. If all rows for that order in sale_detail have stage 1, then it sets the stage in sale to 2.

I could do this with a pair of nested for-each loops. Here’s some mashed up SQL/Java pseudo-code:

int[] stage1_orders := (SELECT id FROM sale WHERE stage = 1);
for(int id : orders){
    bool ready = false;
    int[] statuses := (SELECT status FROM sale_detail WHERE order_number = id);
        for (int status: statuses){
        if (status == 0){ready = false; break;}
        if(status == 1){ready = true;}
        }
    if (ready == true){UPDATE sale SET stage = 2 WHERE id = id;}
    }

I know I can’t do for-each loops in MySQL. Can I write something similar in MySQL? Or am I better writing a job in another language (Python, PHP, etc) and making it run every minute or so?

Turophile · Accepted Answer · 2014-02-26 02:10:41Z

1

update Sale
set stage = 2 
where stage = 1
and 0 = (
  select count(*) 
  from Sale_detail
  where order_id = Sale.id
  and status <> 1 
  )

Put this in a stored procedure which is triggered when either table is updated.

answered Feb 26, 2014 at 2:10

Turophile

3,4151 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Bagheera Over a year ago

Fascinating. I had never learned to use a constant in a WHERE clause.

Turophile Over a year ago

It would probably be more efficient (and thus better) to 'fix' just the order_id being updated, but I wanted to present a straightforward solution.

Bagheera Over a year ago

How do we know that the section in parentheses will run once for each sale.id that it finds? Related: how does it load "Sale.id" with the id value from each row in the sale table?

Turophile Over a year ago

Remember, SQL deals with sets. So SELECT * FROM table is the set of everything in the table. WHERE stage = 1 is the subset meeting that criteria. The sub-select has it's own set. That starts with every row in Sale_detail, but is then 'filtered' to remove the rows where status = 1 and where the order_id matches Sale.id - but Sale.id depends on the row being checked in Sale - so the database engine figures out that for every row in Sale, it has to do the sub-select (a smart DB engine will optimise this and check the stage = 1 predicate first, so it only does the sub-select where necessary).

Turophile Over a year ago

BTW this is called a correlated sub-select because there is a join between the two tables in the sub-select where clause. See en.wikipedia.org/wiki/Correlated_subquery

Collectives™ on Stack Overflow

MySQL and PHP. Should the code below be a MySQL Routine or PHP function?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related