Date Arithmetic With MySQL - Code Poet

(Page 8 of 8 )

Now that the table structure is defined and some sample data has been stored in it, all that's needed is a script to parse the data and execute the appropriate tasks (based on the scheduled date of each task). Here's an example of what this script might look like:

<?php

// define array to convert interval type

// to corresponding SQL clause for DATE_ADD()

$typeArray = array("D" => "DAY", "M" => 
"MONTH", "Y" => "YEAR");
// what is today?

$today = date("Y-m-d", mktime());
// open connection to database

$connection = mysql_connect("localhost", "root", "secret") 
or die ("Unable to connect!");

mysql_select_db("db1") or die ("Unable to select database!");
// formulate and execute query

// get a list of all tasks due today

$query = "SELECT `id`,`msg`,`type`,`interval`,`date`, `owner` FROM `tasks` 
WHERE `date` = CURRENT_DATE()"; $result = mysql_query($query) or die("Error 
in query: " . mysql_error());
// iterate over result set

while ($row = mysql_fetch_object($result)) 

{

// for each task:



// send email

mail($row->owner, "[REMINDER] $today" , $row->msg) or die("Unable 
to send

mail!"); 
 // calculate next run date for task and update tale

$query2 = "UPDATE tasks SET `date` = DATE_ADD(`date`, INTERVAL `interval` 
" . $typeArray[$row->type] . " ) WHERE id = '" . $row->id 
. "'";

$result2 = mysql_query($query2) or die("Error in query: " . mysql_error()); 
}
// close connection

mysql_close($connection);
?>

Now, I've written this script in PHP; however, once you understand how it works, it should be fairly easy to write an equivalent piece of code in Perl, Python, Bash or any other language. This script should be executed once a day (maybe via a cron job - note that you will need to compile a PHP binary in order to use the script above as is); it will scan the records in the table, perform date calculations, and send out notifications to the email address specified for each task if it turns out that the task is due on that particular day.

The first thing the script does is figure out the current date (and format it to a MySQL-compliant format). Next, it executes an SQL query in order to obtain a list of all the tasks to be executed on the current date, and sends email notification to the owner of each task. The DATE_ADD() function discussed previously is then used to calculate the next run date of the task (by adding the specified interval period to the current date) and the table is updated appropriately with new dates.

Of course, this is not a foolproof design. Missing even a single script run could well result in some tasks not being executed and consequently, their next run dates not being updated. If these are recurring tasks, then future invocations of the script will not "see" them, and they will never be executed again.

This design flaw may be worked around by intermittently running automated scripts to verify the integrity of the table. Redundant tasks, or tasks with dates outside a specific temporal window, can be sent to a system administrator for inspection and deletion where needed.

Thus, by allowing you to perform arithmetic operations on temporal data, MySQL's date/time API allows you to easily and accurately build applications to manipulate date and time values. The example above is just one of many possibilities this opens up - I'll leave the rest to your imagination. Go on, think about it - and if you come up with something interesting, write in and tell me about it.

Until then, though...stay healthy!

Note: Examples in this article have been tested on Linux/i586 with MySQL 4.0 and PHP 4.2.3. Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Date Arithmetic With MySQL

>>> Be the FIRST to comment on this article!