监视mysql表的订单和存储订单历史记录

Question

1. I have some website when user can click "order" and his order i saved to orders table.
2. Orders table has 3 fields: id, user_id, book_id.

I need some script which will monitor changes in orders table - if some orders appear it will read them, 'execute', and remove from orders table.

How I can efficently do it? I have never done anything like this before - I thought about some php script which will be executed in some intervals (10-15 secs - it must be fast) and will read the orders table. But then I thought that I have to keep the orders history - and there is second question - should I make second table for orders history or just add field like "completed" to orders table? I ask this two questions at once because they are corelated - maybe when my orders table will contain also completed orders querying it by my script will be unefficient. I am looking for best solution.

I use MySQL 5.0 databse...

---------- edit

I can't use triggers - I have to execute some php script to process the orders when they appear in orders table!

Answer 1

You need a trigger. Something like this will work.

create trigger newOrderTrigger after insert on order_table for each row
//you order execution code goes here

See MySQL Trigger documentation. I've also found this tutorial helpful.

As for you second question, it depends on your requirements. If you need a full audit-able history, you should probably copy the changes into a new table. If you don't, adding an order_status and changing the value from open to complete is less work.