PHP —

cron Jobs

Having designed and tested the query, it can be inserted into a Unix cron table to automate the operation. The crond daemon is a process that runs by default in a Linux installation and continually checks the time. If any of the entries in user tables match the current time, then the commands in the entries are executed. Consider an example:

30 17 * * mon-fri echo 'Go home!'

This prints the string at 5:30 p.m. each working day. The two asterisks mean every day of the month, and every month of the year respectively. The string mon-fri means the days Monday to Friday inclusive. More details about cron can be found by running man crontab in a Linux shell.

We can add our housekeeping query to our cron table by running:

% crontab -e

This edits the user's cron table.

We have decided that the system should check for old shopping carts every 30 minutes. To do so, we add the following two lines to the file:

0 * * * * /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;'
30 * * * * /usr/local/mysql/bin/mysql -uusername -psecret
  -e 'USE winestore; DELETE FROM items WHERE
        unix_timestamp(date) <
   (unix_timestamp(date_add(now(  ), interval -1 day)))
  AND cust_id = -1;'

The first line contains the complete query command for the orders table from earlier in this section, and the second line the items query. The shopping cart orders DELETE query runs exactly on each hour, while the items DELETE query runs at 30 minutes past each hour. Different times are used to balance the DBMS load.

Reports, updates, delete operations, and other tasks can be added to the cron table in a similar way. For example, we can output a simple report of the number of bottles purchased yesterday and send this to our email address each morning:

0 8 * * * mon-fri /usr/local/mysql/bin/mysql -uusername
 -psecret -e 'USE winestore; SELECT sum(qty) FROM
  items WHERE unix_timestamp(date) >
 (unix_timestamp(date_add(now(  ), interval -1 day))) AND
 cust_id != -1;' | mail help@webdatabasebook.com

We could also have automatically written the information to a log file or to a table in the database.

Templates

Separating code from HTML can be difficult in PHP. As we discussed in Chapter 1 and have shown throughout this tutorial, one of the best features of PHP is that scripts can be embedded anywhere in HTML documents. However, this can lead to maintenance problems: if we want to redesign the presentation of the web site, then we may need to rewrite code or, at the very least, understand how PHP and HTML are interleaved in the application. This also makes it difficult to maintain code when it is interleaved with presentational components.

A good solution for medium- to large-scale web database applications is to use templates to separate markup and code. In this section, we illustrate how templates can be used in PHP applications through a case study example from the online winestore. In our example, we use the open source XTemplate class library available from http://sourceforge.net/projects/xtpl/. The XTemplate library is object-based, and Chapter 2 provides a brief introduction to the object-oriented features of PHP. There are other excellent template libraries, including most notably the Smarty PHP template engine available from http://www.phpinsider.com/php/code/Smarty/.