March 21st, 2010 
admin
PHP- Automated Housekeeping
Queries are run by users through the web interface and by administrators through either administrative web interfaces or from the MySQL command interpreter. However, sometimes automated querying is necessary to produce periodic reports, update data, or delete temporary data. We discuss how queries can be automated in this section.
To show how queries can be automated, consider an example from the online winestore. The shopping cart in the online winestore is implemented using the winestore database. As discussed in Chapter 12, when an anonymous user adds a wine to their shopping basket, an order row is added to the orders table. The row is for a dummy customer with a cust_id=-1, and the next available order_id for this dummy customer. A related items row is created for each item in the shopping cart. The order_id is maintained in the session variable order_no so that orders by different anonymous customers aren’t confused.
Our system requirements in Chapter 1 specify that if a customer doesn’t purchase the wines in their shopping cart within one day, then the shopping cart should be emptied. This is an example of a DELETE operation that should be automated. It is impractical to require the administrator to run this query each day to remove junk data.
The following query can be run from the Linux shell to remove all orders rows that are more than one day old and are for the dummy customer:
Posted in 
Tags: 
