Pulling out previously unseen records from database

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 开发者_StackOverflow社区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

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?

Hmmm, sounds clunky to me. I'd store this info in the database.

Either add a flag to the Orders table that indicates if it has been processed. Alternatively keep a table with MailID, OrderId that keeps track in wich email an order was mailed. To mail new ones just left join and say where MailId is null. That'l give you the ones that weren;t mailed yet.

I don't see a reason to keep this seperate from the DB, and a lot of reasons (especially Keeping It Simple ...) to keep it in.

Rgds GJ