从数据库中拉出以前看不见的记录

Question

I have a web app (built with codeigniter) that runs every n amount of time. It will execute a stored procedure that gets a list of recent orders. Part of the task is to use the stored procedure but I'm sure if a solution requires another method that shouldn't be a problem.

The records retrieved will each have a unique order id. When the records are pulled out the order data is taken, put together as a pdf, and sent in an email to the order owner.

My question is which is the best way to go about only retrieving records that haven't been processed before. My initial thought was to store the last record processed in a file. Each time the applcation is run, look up the file, get the last stored ID, and then pull out records with an id > that that.

A while back I wrote a Perl script that did something similar with a log file using the Tail module. It would look up and file and see a new line it hadn't processed then process it, but then again that scipt was constatly running. I know it's quite different but is there anything similar I can do in PHP with a db?

I'd prefer not to have to store anything in a DB, only querying via a stored procedure.

Thanks,

Billy

Answer 1

IMHO the best way to retrieve records that haven't been processed before is to fetch records with certain status.

You mentioned:

I'd prefer not to have to store anything in a DB, only querying via a stored procedure.

I'm suggesting adding status field into your orders table and change the order status once the order has been sent.

I wouldn't trust the fetching order IDs > previously sent order ID. What happens if you need to update an old already sent order and you need to send it again?