27.13.1. How to find the last date of...

Dates are stored in many tables of PHP-Nuke. The date fields are used to hold the date we

• entered a banner and whhen that banner expires,

• wrote a private message,

• wrote a comment,

• entered a download link,

• voted for something (article, download, link etc.),

• wrote in our journal,

• inserted a web link,

• wrote a review,

• wrote an article

and so on. Sometimes, while writing a modification (Chapter 17, Chapter 18), or even a new block (Chapter 20) or module (Chapter 21), you may want to compute the last date that an event like the above has happened.

Whenever you encounter a problem of this type - find the first, the last, the minimum, the maximum, the average...of some attribute that is stored in a table field of PHP-Nuke - think aggregate functions. There is no need to go through a loop of all records in our table, just to find an aggregate function of its fields - the database does it for us:

SELECT max( date ) FROM nuke_downloads_downloads;

The above SQL query will compute the maximum date (i.e. the last date) of all links n the Downloads section. You can use SQL queries like the above in your blocks and modules. To start with, create (with a decent text editor, see Chapter 11) a file that contains (see How to compute the date of the last file added in Downloads):

<?php
require_once("mainfile.php");
global $db;
include("header.php");
$sql = "SELECT max(date) FROM nuke_downloads_downloads";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$maxdate = $row["max(date)"];
echo "maxdate = $maxdate";
echo "<br><br>";
?>

name it query.php, upload it in the same dir on your server where also mainfile.php is located, then point your browser to query.php. You should see a line like

maxdate = 2003-11-17 15:33:03

This example illustrates the use of the new SQL layer (see Section 28.5) for the computation of aggregates. Note that the argument of the $row array is a string that mimics exactly the aggregate function in the SELECT statement:

$maxdate = $row["max(date)"];

Here, "max(date)" is just a string and has nothing to do with the max() function.

Of course, this example is more a "proof of concept", rather than something you should use in production - on the contrary, you should delete it from server as soon as you don't need it, for security reasons. But it serves as a starting point for more ambitious projects. like the following modification to the Downloads module:

Problem: You have various categories and subcategories defined in the Downloads section. For each category, you want to compute the last date that you entered some download link, either in this category, or in any of its subcategories.

We have already seen how to find the lst date of the downloads in one category above. What makes the situation here somewhat more complicated is the fact that we have to do the same computation for every subcategory of a category - however we don't know all those subcategory IDs a priori, we have to search for them.

More specifically, the steps to be taken for every category ID $cid are:

• Compute the maximum date for that $cid.

• Search the nuke_downloads_categories for all category IDs, whose parent ID is $cid. Let's call such a category ID $subcid (meaning that it is a subcategory, since it has a parent, $cid).

• Compute the latest date a download was inserted in that $subcid. Do this for all $subcid's you can find for that $cid.

• Compute the maximum of all those maximum dates (including the maximum date for $cid). This will be the latest date a download link was inserted in either the category $cid, or any of its subcategories $subcid.

The code that implements the above algorithm is (see How to compute the date of the last file added in Downloads):

$sql3 = "SELECT max(date) FROM ".$prefix."_downloads_downloads WHERE cid='$cid'"; 
$result3 = $db->sql_query($sql3);
$row3 = $db->sql_fetchrow($result3);
$maxdate3 = $row3["max(date)"]; 
$sql4 = "SELECT cid FROM ".$prefix."_downloads_categories WHERE parentid='$cid'"; 
$result4 = $db->sql_query($sql4);
$maxdate4 = 0;
while ($row4 = $db->sql_fetchrow($result4)) { 
   $subcid = $row4["cid"];
   $sql5 = "SELECT max(date) FROM ".$prefix."_downloads_downloads WHERE cid='$subcid'"; 
   $result5 = $db->sql_query($sql5);
   $row5 = $db->sql_fetchrow($result5);
   $maxdate5 = $row5["max(date)"]; 
   if ($maxdate5 > $maxdate4){
      $maxdate4 = $maxdate5; 
   }
}
$maxdate = max($maxdate3, $maxdate4); 
if ($maxdate) {
   echo "<font class=\"content\">Latest Download on $maxdate</font><br>"; 
}

Use JOIN

This algorithm would be a perfect example of an application of the SQL JOIN query, since the "parent-child" information is kept in a different table, nuke_downloads_categories, while the dates themselves are kept in nuke_downloads_downloads. However, JOINs may not be supported by your version of MySQL, so we took the rather primitive (and usually slower) approach of nested SELECT statements in the code above. If you only have a few categories, subcategories and downloads, JOINs may not be a big deal for you, but the situation changes if you have thousands of them (see Section 27.6 on how you can enter that many, without subjecting yourself to Repetitive Stress Injury ). You are encouraged to experiment with JOINs and test if they improve the algorithm's performance!