In 1942 the greatest threat to allied shipping was the mighty German battleship, Tirpitz. Her vast size meant that the only dock on the Atlantic seaboard that could accommodate her was at St Nazaire.**So Operation Chariot was
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:
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.
$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>";
}
Compute the maximum date for that $cid. The SQL query is the same as the one we used in the simplified example above.
This is the maximum date for the category ID $cid. It may or may not be set, depending on whether we entered a download directly under that category at all,
i.e. without making use of any of its subcategories. If we did, this is the last date (and time) we did it, otherwise it is 0.
With this SQL query, we select all subcategories of that category. The subcategories' parentid field will contain the parent category ID, therefore this field must be equal to our $cid.
We enter a loop for every subcategory. Inside the loop, the subcategory will be stored in $subcid and we will compute the maximum date for each subcategory ID.
This is the same SQL query we used previously to compute the maximum date for a given ID. In this case, the ID is that one of
the subcategory, i.e. $subcid. Depending on whether we entered a download under that subcategory this will either be the last date (and time) we did so, or just 0.
The maximum date of downloads under the $subcid is stored in $maxdate5.
Since for every subcategory $subcid, a different maximum date is going to be stored in $maxdate5, we have to compute the maximum of all those maximums. This is stored in $maxdate4. At the end of
the loop, $maxdate4 will be the maximum of all dates of all subcategories. Again, this will be some date, or zero.
Finally, we take the maximum of the maximum date of all subcategories and the maximum date of the category itself. This is the last date we inserted a link, either in the category itself, or in
any of its subcategories.
Print some result, if the last date we computed is not 0. It looks as in Figure 27-12.
Figure 27-12. Downloads: Main categories with last download date.
Downloads: Main categories with last download date.
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!