Tag Archives: php

Managing timezones with PHP and Javascript

In a current project we need to keep a log of all activities and present that log to the users. And once we start needing to tell users about times we raise the whole spectre of timezones.

After some heated design discussions we narrowed our requirements to needing to provide a choice of two possible display formats for users.

  1. show all activities in (my) local time eg if I’m in Melbourne, show all activities in Melbourne time, even if not completed in Melbourne
  2. show all activities in (their) local time – what we called “original” time eg if I’m in Melbourne, show all activities completed in Sydney in their original Sydney time, show all activities completed in Perth in their original Perth time.

For the latter format to make sense we agreed to add a timezone code to indicate where we were at the time. For convenience these were all given as time +/- UTC. Eg 4:23PM (UTC +11).

implementation – capturing activity times

To allow us to manipulate activity times after recording them we need to record two bits of information:

  • time completed
  • timezone completed in

Our decision for those two was to record the time in UTC and then for the timezone simply record the difference from UTC for the timezone where the activity was completed. That way we knew that whatever we did on display we had a consistent and understandable set of times in the database.

For example, I’m in Sydney (current timezone is UTC+11). An activity I complete at 4pm is recorded in the database as:

  • completion time: 5am that morning (4pm converted to UTC)
  • completion timezone: UTC+11

getting the timezone the activity was completed in

Getting the timezone is easy, and requires a few lines of Javascript added to the client where the activity is completed. So we can get the value to the server to manipulate and record in the database we pass it back to the server as a hidden form variable on our login page.

The magic function we need is getTimezoneOffset. This returns the number of minutes ahead/behind of GMT for the current user. For reasons that become clear when we start looking at presenting dates we convert it to seconds. And for reasons that are lost to us we need to negate the value because it returns the offset the wrong way around. Eg if the client is currently one hour ahead of UTC, getTimezoneOffset returns -60.

Putting all that together adds the following to our login page:

<script language="JavaScript"> 
   var d = new Date(); 
   var offset = 0 - (d.getTimezoneOffset() * 60); 
   document.write ("<input type='hidden' name='offset' value = '" + offset + "' />")
</script>

We added that code to the login form. That way we can capture it once and store as a session variable to use when recording all subsequent activities for that user in the same session.

getting the UTC time the activity was completed at

In getting the time an action was completed we can either take the client time or server time. Doesn’t really matter (if both are confidently accurate) since we’ll convert either to UTC.

For simplicity we chose to work from the server time. In PHP we can get the UTC of the current server time in 2 lines:

$timestamp = time();   
$timestampUTC = $timestamp - date("Z", $timestamp);

The “Z” option for Date returns the offset to UTC in seconds based on the server’s current timezone. And since timestamps simply count seconds it’s simple to add/remove our conversion factor.

You’ll have noticed there are a few config requirements if this approach is going to work. We were able to rely on these being true in our case but you’ll need to check for your own implementation:

  • Javascript enabled on the client
  • correct time (and timezone) set on the client
  • correct time (and timezone) set on the server

If you cannot rely on any of these then you’ve a few changes to make!

displaying times

With our activity recorded in the database as UTC it is a simple task to convert to the display times we require. To provide a different display format, we simply need to choose which offset we apply to the UTC time recorded in the database.

local time

This is the easiest to generate. Simply update the recorded UTC time with the current UTC offset of the client that we captured when they logged in. Since we converted that value to seconds when uploading we simply add it to the UTC timestamp from the server:

$timeLocal = $timeUTC + $_SESSION['offsetClient']; 
$timeLocalDisplay = date("D d M Y H:i:s", $timeLocal);

original time

For original time we just need to apply the offset originally recorded in the database, and then tweak the presentation of the timezone to something legible.

$timeOriginal = $timeUTC + $offsetActivity  //both from the DB
$timeOriginalDisplay = date("D d M Y H:i:s", $timeOriginal);
if ($offsetActivity == 0) { 
   $timeOriginalDisplay .= " (UTC)"; 
} else { 
   if ($offsetActivity > 0) {   
      $timeOriginalDisplay .= " (UTC +" . ($offsetActivity/3600) . ")"; 
   } else {   
      $timeOriginalDisplay .= " (UTC -" . ($offsetActivity/3600) . ")"; 
   } 
}

That code gives us a date like “Mon 23 Jun 2006 12:23:45 (UTC +7)”.

A third option (that we did not implement) was to give the timezone as relative to our own. Eg if we’re at “UTC+4″ the previous date would be “Mon 23 Jun 2006 12:23:45 (+3)”. This format would be easy to calculate as we already have our current timezone recorded as a session variable after login. However no-one really wanted it (or could agree how to present it) so the idea was dropped.

conclusion

So far this design has handled all the requirements we’ve thrown at it. Only changes we made after implementation was to move some of the display conversion code into mySQL so our queries return the dates in the formats we require. Otherwise, all works well.

What’s missing?

There is one glaring whole in the design and that is the ugliness called daylight saving. It becomes an issue if displaying local times. Our design converts the original activity time to the local time based on the current difference between local time and UTC. But if we’ve moved in or out of daylight savings (so the relative difference now is different to what it was then) our calculation will be out.

To manage that we need to know whether we, locally, were in/out of daylight savings when the activity was completed, and whether we are in/out of daylight savings now. Achievable but a not insignificant piece of work. The client agreed and thus we decided to ignore this issue reasoning:

  • a max of one hour out is liveable, and
  • the issue is consistent (meaning a series of activities reported in local time will still present in the correct order, even if for a period of time they are all 1 hour too early/late)

Comparison and Logical Operators: Javascript, SQL, PHP and ASP Compared

Comparison and Logical operators are used for testing data values. Sadly the operators to use are not consistent across languages. This article summarises the most common operators for the key web development languages:

  • Javascript
  • ASP
  • PHP
  • SQL

For SQL we checked both mySQL and MS SQL. All operators listed here should work in both.

Comparison

Operator Javascript ASP PHP SQL
Less than < < < <
Less than or equal to <= <= <= <=
Greater than > > > >
Greater than or equal to >= >= >= >=
Equal to == = == (equal)
=== (identical)
=
Not equal to != <> != (not equal)
<> (not equal)
!== (not identical)
<>

Testing for “identical” in PHP arrived with PHP 4. Two values are identical if they are the same, and the same type. Eg the values “1″ (a string) and 1 (an integer) are equal but not identical.

Logical

Operator Javascript ASP PHP SQL
AND && AND &&
AND
AND
OR || OR ||
OR
OR
NOT ! NOT ! NOT

PHP provides two options for AND and OR with different operator precedences. Refer to the PHP manual: Operator Precedence for details.

useful reference lists

Javascript
comparison and logical operators (w3schools)
ASP
comparison and logical operators (msdn)
PHP
comparison operators (php.net)
logical operators (php.net)
MS SQL
comparison and logical operators (msdn)
MySQL
comparison operators (mysql.com)
logical operators (mysql.com)

Sorting a PHP multi-column array

PHP support for arrays is fantastic, particularly for those of us with a history using ASP. Sorting a single-column array is straightforward (there is a sort() function). But what about sorting a multi-column array? Well, there is a function (array_multisort()) that does it, but understanding how to use it is a challenge. This article hopefully provides a simpler explanation.

If you are using a multi-column array to store data from a database, it is much simpler and faster to get the data directly from the database in the order required (use the ORDER BY clause in your SELECT statement).  This function is best used for multi-column arrays that are generated locally, or only in part from a database.

How to

To sort a multi-column array:

  1. determine which columns you want to sort by
  2. for each column you want to sort by, copy its contents into a separate, single-column array
  3. build your array_multisort() command:
    • add the new single-column array as a parameter
    • optionally follow the array with parameters for sort order and type
    • repeat above two steps for each column to be sorted on
    • complete command with name of multi-column array to be sorted

sort type and order

Sort type can be:

  • SORT_REGULAR – compare/sort normally (default)
  • SORT_NUMERIC – compare/sort as numbers
  • SORT_STRING – compare/sort as strings

Sort regular uses standard ASCII order (“ABa” is 3 characters in regular order). The other two options operate more logically (eg “aAB” is the same characters now in string order).

Sort order can be:

  • SORT_ASC – ascending order (default)
  • SORT_DESC – descending order

For example…

For example. here’s a 3-column array we want to sort:

<?php
  $data[0]['name']= 'Google';
  $data[0]['url'] = 'http://www.google.com';
  $data[0]['rank'] = 45;

  $data[1]['name']= 'Yahoo';
  $data[1]['url'] = 'http://www.yahoo.com';
  $data[1]['rank'] = 17;

  $data[2]['name']= 'MSN';
  $data[2]['url'] = 'http://www.msn.com';
  $data[2]['rank'] = 6;
?>

Assume we want to sort this array by rank (ascending order). The first step is to store the rank column as a separate, single-column array. This is easy to do using the foreach command:

<?php
  foreach ($data as $val)
  {
    $sortArrayRank[] = $val['rank'];
  }
?>

To sort by this column, use array_multisort and pass our newly created rank array as the first parameter:

<?php
  array_multisort($sortArrayRank, $data);
?>

To change the sort to descending order, add the required order parameter after the 1-column array:

<?php
  array_multisort($sortArrayRank, SORT_DESC, $data);
?>

To sort by more than one column (eg to sort by rank, then name), simply repeat the above steps for the subsequent column(s) and add the paramaters to the list.

<?php
  foreach ($data as $val)
  {
    $sortArrayName[] = $val['name'];
  }
  array_multisort($sortArrayRank, SORT_DESC, $sortArrayName, $data);
?>

more information

The official explanation of array_multisort can be found in the PHP manual.

Logical and comparison operators for common web languages

Comparison and Logical operators are used for testing data values. Sadly the operators to use are not consistent across languages. This article summarises the most common operators for the key web development languages:

  • Javascript
  • ASP
  • PHP
  • SQL

For SQL we checked both mySQL and MS SQL. All operators listed here should work in both.

Comparison

Operator Javascript ASP PHP SQL
Less than < < < <
Less than or equal to <= <= <= <=
Greater than > > > >
Greater than or equal to >= >= >= >=
Equal to == = == (equal)
=== (identical)
=
Not equal to != <> != (not equal)
<> (not equal)
!== (not identical)
<>

Testing for “identical” in PHP arrived with PHP 4. Two values are identical if they are the same, and the same type. Eg the values “1″ (a string) and 1 (an integer) are equal but not identical.

Logical

Operator Javascript ASP PHP SQL
AND && AND &&
AND
AND
OR || OR ||
OR
OR
NOT ! NOT ! NOT

PHP provides two options for AND and OR with different operator precedences. Refer to the PHP manual: Operator Precedence for details.

useful reference lists

Javascript
comparison and logical operators (w3schools)
ASP
comparison and logical operators (msdn)
PHP
comparison operators (php.net)
logical operators (php.net)
MS SQL
comparison and logical operators (msdn)
MySQL
comparison operators (mysql.com)
logical operators (mysql.com)

Generating a random password with PHP

One of the simplest techniques for ensuring the quality of registrations to a website is to require confirmation via an e-mail address before providing access. Quickest way to do this is to not ask for a password but instead mail a random generated password to the provided address. If they then log in with the password you sent you know the e-mail address is valid.

But how do you generate a random password for every pending user? Well generating random strings is an art in itself. However one of the simplest and neatest techniques I’ve come across was described by Kevin Yank on Sitepoint:


$newpass = substr(md5(time()),0,6);

His command basically takes a substring of an encrypted version of the current time. A good chance that you’ll never get two users therefore with the same password.

You can change the length of the provided password by changing the “6″ at the end of the command.

Very neat. Very simple.