You are missing our premiere tool bar navigation system! Register and use it for FREE!

•  Home •  Downloads •  Gallery •  Your Account •  Forums • 
Readme First
- Readme First! -

Read and follow the rules, otherwise your posts will be closed
· Home
· Buy a Theme
· Advertising
· AvantGo
· Bookmarks
· Columbia
· Community
· Donations
· Downloads
· Feedback
· Forums
· Private Messages
· Search
· Statistics
· Stories Archive
· Submit News
· Surveys
· Theme Gallery
· Top
· Topics
· Your Account
Who's Online
There are currently, 359 guest(s) and 0 member(s) that are online.

You are Anonymous user. You can register for free by clicking here
How to find the last date of...

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):

global $db;
$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'"; (1)
$result3 = $db->sql_query($sql3);
$row3 = $db->sql_fetchrow($result3);
$maxdate3 = $row3["max(date)"]; (2)
$sql4 = "SELECT cid FROM ".$prefix."_downloads_categories WHERE parentid='$cid'"; (3)
$result4 = $db->sql_query($sql4);
$maxdate4 = 0;
while ($row4 = $db->sql_fetchrow($result4)) { (4)
   $subcid = $row4["cid"];
   $sql5 = "SELECT max(date) FROM ".$prefix."_downloads_downloads WHERE cid='$subcid'"; (5)
   $result5 = $db->sql_query($sql5);
   $row5 = $db->sql_fetchrow($result5);
   $maxdate5 = $row5["max(date)"]; (6)
   if ($maxdate5 > $maxdate4){
      $maxdate4 = $maxdate5; (7)
$maxdate = max($maxdate3, $maxdate4); (8)
if ($maxdate) {
   echo "<font class=\"content\">Latest Download on $maxdate</font><br>"; (9)
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.

Tip 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!

Help us make a better PHP-Nuke HOWTO!

Want to contribute to this HOWTO? Have a suggestion or a solution to a problem that was not treated here? Post your comments on my PHP-Nuke Forum!

Chris Karakas, Maintainer PHP-Nuke HOWTO

Web site engine's code is Copyright © 2002 by PHP-Nuke. All Rights Reserved. PHP-Nuke is Free Software released under the GNU/GPL license.
Page Generation: 0.114 Seconds - 293 pages served in past 5 minutes. Nuke Cops Founded by Paul Laudanski (Zhen-Xjell)
:: FI Theme :: PHP-Nuke theme by coldblooded ( ::