Web Database Applications with PHP and MySQL, 2nd Edition

Automating Querying

Sometimes automated queries are useful for producing periodic reports, updating data, or deleting temporary data. As we show you later in "Backup and Recovery," they're also useful tools to produce database backups.

Consider an example from the winestore database where query automation is useful. The shopping cart in the online winestore is implemented using the database. As we discuss in detail later in Chapter 18, when an anonymous user adds a wine to their shopping basket, a row is added to the orders table. The row is for a dummy customer with a cust_id=-1. A related items row is then created for each item in the shopping cart. For the moment, the details of how this works and why we do it this way aren't important.

Our system requirements in Chapter 16 specify that if a customer doesn't purchase the wines in their shopping cart within one day, the shopping cart should be emptied. This is similar to most online stores, and it's necessary to prevent the database being filled with abandoned carts. In this case, it's a DELETE query that should be automated.

The following instructions assume you've followed our installation instructions in Appendix A through Appendix C.

If you're using a Unix environment, the following query can be run from the shell to remove all shopping cart rows from the orders and items tables that are more than one day old:

%/usr/local/mysql/bin/mysql -uusername -ppassword -e 'USE winestore;
             DELETE orders, items FROM orders INNER JOIN items ...