PHP-Nuke: Management and Programming
PrevChapter 27. MiscellaneousNext

27.13. How to deal with date and time

You will encounter date/time functions all over PHP-Nuke: cookie expiration time, broadcast message duration time, article time, server time. PHP offers many functions that help us with date/time tasks. Here we will cover:

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

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:

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)
}
(1)
Compute the maximum date for that $cid. The SQL query is the same as the one we used in the simplified example above.
(2)
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.
(3)
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.
(4)
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.
(5)
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.
(6)
The maximum date of downloads under the $subcid is stored in $maxdate5.
(7)
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.
(8)
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.
(9)
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.

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

27.13.2. How to adjust server time

Figure 27-13. Your Info profile: Timezone and Date settings.

Your Info profile: Timezone and Date settings.

In some cases, perhaps due a misconfiguration of your server, the time shown up in the News stories is wrong. Normally, it should be possible to control it by changing the time settings in either the Preferences of the administration panel, or the user preferences (Figure 27-13). However, there will be situations where a quick fix is desirable.

You can offset the time by tweaking the following line in the mainfile.php in the function formatTimeStamp (see How to adjust server time in PHP-Nuke):

$datetime = strftime(""._DATESTRING."", mktime($datetime[4],$datetime[5],$datetime[6],
$datetime[2],$datetime[3],$datetime[1]));

For example to add one hour (3600 seconds), you would simply add 3600:

$datetime = strftime(""._DATESTRING."", mktime($datetime[4],$datetime[5],$datetime[6],
$datetime[2],$datetime[3],$datetime[1]) + 3600);

or, to subtract two hours:

$datetime = strftime(""._DATESTRING."", mktime($datetime[4],$datetime[5],$datetime[6],
$datetime[2],$datetime[3],$datetime[1]) - 7200);

A situation that calls for server time modification arises when your server is located in a different timezone than your geographic location - say you are located in Japan, but your hosting company is in Canada (see How to adjust server time in PHP-Nuke). The article on phpbuilder.com On User-Defined Timezones in PHP explains the problem in detail. Quoting:

PHP's 'mktime' and 'date' functions work well as a pair without the help of any other timestamp manipulation routines, but only if the application in which they are used is concerned solely with display and entry of time in the servers timezone. If an application needs to handle entry from a timezone other than that in which the server is located something more than 'mktime' and 'date' is required.

Two things are required to accomplish this: a location independent format for storing time in the database, and methods to translate to and from that format into the user's local time.

Read the original article for a solution. To apply it to PHP-Nuke, you would need to replace the time functions with the new ones, read the time offset from the user's profile and display the result.

To reflect the new time zone that may be implied by the hardcoded time offset, you would also want to change the _DATESTRING definition in your language file, e.g. language/lang-english.php:

define("_DATESTRING","%A, %B %d @ %T %Z");

You can change the "%A, %B %d @ %T %Z" string to whatever you deem appropriate. PHP gives to some placeholders a special meaning:

You can combine them with punctuation or extra words to construct the datetime string of your liking. You are not limited to the above interpretation. For example, you can just delete the %Z, and replace it with EST or whatever you time zone is (if you cannot set it from the preferences, that is). There are some points to bear in mind while experimenting:

See the PHP manual page for strftime for more information and examples about date string formatting.

27.13.3. The Discordian Calendar

Date and time functions in PHP are very flexible, but I still have to find a PHP function that converts my date strings to the Discordian Calendar:

The Discordian calendar divides the year into five seasons (commonly known as Chaos (la kalsa), Discord (la tolsarxe), Confusion (la cfipu), Bureaucracy (la lujycatni) and the Aftermath (la jalge) ) of 73 days each. Every four years, an extra day (St. Tib's Day / la tib noi vrude) is inserted between Chaos 59 and Chaos 60.

The Discordian calendar defines a week as having five days, named Sweetmorn(la titcer), Boomtime (la bumtem), Pungenday (la pynjdei/cpinydei), Prickle-Prickle (la kilkil) and Setting Orange (after the Five Elements). Chaos 1 is always Sweetmorn, Chaos 2 Boomtime, The Aftermath 73 Setting Orange(la canci narju), etc. St. Tib's Day is none of the five days of the week.

The Discordian calendar's year 0 (notionally the time of the Original Snub) is known in the Gregorian calendar as 1166 BC; alternatively, the Gregorian year 0 (notionally the birth of the Jewish mystic Yeshua ben Yosef) is known as 1166 YOLD in the Discordian calendar. The year known in the Gregorian calendar as 2001 is known as 3167 in the Discordian calendar.

Just in case you wanted to know.

How do we output a date string in the Discordian Calendar? If you want to write your own PHP ddate function, you might find the code of the Perl Date::Discordian module useful. If the settings in your php.ini allow the execution of system commands (either safe_mode is off, or safe_mode is on and safe_mode_exec_dir contains the directory that holds the ddate executable, see safe mode settings) and you are running off a Linux box, then you can use the output of ddate to construct a discordian date in mainfile.php:

$datetime = explode("Today is", exec('ddate'));

This will result in something like

Setting Orange, the 41st day of Bureaucracy in the YOLD 3169

as the value of $datetime displayed in the News posts. More on this confusing topic in Principia Discordia, the Opiatum Magnum of the Discordian Philosophy, as well as in the Discordian Forum - where Enlightenment meets Confusion (TM)...

PrevHomeNext
How to size the select boxes in a moduleUpHow to deal with quotes in the site's name