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 tutorial 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 tutorial 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:

% /usr/local/mysql/bin/mysql -uusername -psecret
    -e 'USE winestore; DELETE FROM orders WHERE
        unix_timestamp(date) <
 (unix_timestamp(date_add(now(  ), interval -1 day)))
AND cust_id = -1;'

The MySQL time and date function unix_timestamp( ) converts a timestamp attribute to an integer that is accurate to the nearest second. In this query, we compare the value of the entry in the orders table with the value of exactly one day earlier from the current date and time. If the row is older than one day, then it is deleted. The same query works for the items table, when orders is replaced with items in the FROM clause.