Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

15.5 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 
  USING (cust_id, order_id) WHERE date < date_sub(now( ), interval 1 day)
  AND orders.cust_id = -1;'

In a Microsoft Windows environment you can do the same thing using
the Run dialog box that's accessible from the Start
menu. Type the following and then click OK:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysql.exe" -uusername -ppassword 
-D winestore -e "DELETE orders, items FROM orders INNER JOIN items 
USING (cust_id, order_id) WHERE date < date_sub(now( ), interval 1 day)
AND orders.cust_id = -1;"

The MySQL time and date functions date_sub( )
and now( ) are described in Section 15.4. The next two sections show
how to install a command so it runs regularly.

15.5.1 Unix

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

30 17 * * 1-5 echo 'Go home!'

This instructs crond to print the string at 5:30
p.m. each day from Monday (day 1) to Friday (day 5). The two
asterisks mean every day of the month, and every month of the year
respectively. The string 1-5 means the days Monday
to Friday inclusive.

A cron entry has six parts: a time in minutes from 0 to 59, a time in
hours using the 24-hour clock, a day of the month from 1 to 31, a
month of the year from 1 to 12, a day of the week from 0 to 7 (Sunday
is both 0 and 7), and the command to execute. For each of the first
five parts, you can set an integer value (for example,
1), a comma-separated list of values (for example,
1,3,5), a range of values (for example,
1-3), a combination or a list and a range (for
example, 1-3,5), or a stepped value (for example,
0-23/2 could be used to mean every second hour).
You can also replace any value with an asterisk *
meaning all values.

Under Linux, you can replace integer day numbers with the shortcut
names mon to sun, and numeric
months with the shortcuts jan to
dec. More details about cron
can be found by typing man crontab in a shell
to read the manual page. Note that crontabs in some other Unix
variants also have a slightly different format.

You can add the housekeeping query discussed in the previous section
to the cron table by typing the following at a
shell prompt:

% crontab -e

This edits your cron table.
Let's decide that the system should check for old
shopping carts every 30 minutes. To do so, add the following line to
the file (it must be on one line):

0,30 * * * * /usr/local/mysql/bin/mysql -uusername -ppassword
-e 'USE winestore; DELETE orders, items FROM orders INNER JOIN items 
  USING (cust_id, order_id) WHERE date < date_sub(now( ), interval 1 day)
  AND orders.cust_id = -1;'

After you save the file, the shopping cart DELETE
query runs every 30 minutes.

Reports and other tasks can be added to the cron
table in a similar way. For example, you can output a simple report
of the number of bottles purchased yesterday and send this to your
email address each morning. Here's how you might do
it:

0 8 * * * mon-fri /usr/local/mysql/bin/mysql -uusername -ppassword
  -e 'USE winestore; SELECT sum(qty) FROM orders INNER JOIN items 
  USING (cust_id, order_id) WHERE date > date_sub(now( ), interval 1 day)
  AND orders.cust_id != -1;' | /bin/mail hugh@hughwilliams.com

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

There are other ways to automate queries or housekeeping in a Unix
environment, including with the commands at and
batch. We don't discuss these
here, but you can find out more by typing man
at or man batch at a shell prompt.
A Mac OS X-focused article (that's also mostly
relevant to other Unix users) can be found at http://www.macdevcenter.com/pub/a/mac/2002/07/02/terminal_5l.

15.5.2 Microsoft Windows

Having designed and tested a query,
it can be scheduled to run automatically by Microsoft Windows. The
Windows task scheduler is a process that runs by default and
continually checks the time. If any of the scheduled entries match
the current date and time, the commands in the entries are executed.

Suppose you want to check for old shopping carts once every day. To
do this, click on the Start Menu, then on Settings, and then on the
Control Panel menu option. Now, double-click on the Scheduled Tasks
icon. In the window, you'll see an icon labeled Add
Scheduled Task. Double click the icon, and a wizard that guides you
through setting up a task will start. We discuss the steps for
Windows 2000 next and assume you've followed our
installation instructions in Appendix A
through Appendix C.

To use the wizard, click Next to begin. Then, click the Browse button
and locate the mysql.exe program in the
directory C:\Program Files\EasyPHP1-7\mysql\bin.
Click Open to select the program. Now, because we want to run the
task daily, click on the Daily radio button and click on Next. On the
next screen, you can accept the default start time, interval (Every
Day), and start Date, and click Next again. The following screen asks
for your Windows username and password: enter these and press Next.
You're now on the final screen of the wizard, but
you still need to modify exactly what the task will do. So, click the
Open Advanced Properties checkbox and click on Finish. A mysql task
dialog box should appear containing three tabs.

You've now completed working with the wizard, but
you need to add extra details to the command that will run. In the
Run text box shown in the Task tab, alter the text so that it is as
follows:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysql.exe"-uusername -ppassword
  -D winestore -e "DELETE orders, items FROM orders INNER JOIN items 
  USING (cust_id, order_id) WHERE date < date_sub(now( ), interval 1 day)
  AND orders.cust_id = -1;"

Click on Apply, and then on OK. After the dialog window closes, the
shopping cart DELETE query runs every day.

You can schedule tasks to run once or more frequently than daily by
changing the settings in the Schedule tab for the task. You can
access this by selecting Settings from the Start Menu, then Control
Panel, double-clicking on Scheduled Tasks, and then on the task you
want to edit. The Advanced button allows you to customize when a task
repeats. For example, suppose you want a task to run every 10
minutes. To do this, you select the Repeat Task checkbox, change the
Every option to 10 minutes, select the Duration radio button, and
change the hour(s) setting to 24. Then, click on OK.

The Settings tab lets you adjust other conditions that determine if a
task should be run, such as whether your notebook is running on
batteries, whether the computer is sleeping, or if the computer is
idle.

You can find out more about the Task Scheduler using the Microsoft
Windows Help system. Click on Help in the Start Menu, and typing
Scheduled Tasks into the Index tab.
Double-click the Overview sub-entry.