PHP wont work after 2038

Added on: Tuesday 11th June 2013

That is a bit of an attention grabbing headline but there is at least one function in PHP that doesn't like dates after 2038.

I've recently been working on a web based CRM system for a client who is an Independent Financial Adviser.

They rang me up the other day and told me they couldn't save the date in a particular field of a mortgage record.

I duly logged on to their server (it is hosted on their Intranet) and tried to edit the record in question. As they had said when saved it displayed 31/12/1969.

I was using strtotime to convert the database value before displaying it. The function converts the date into the number of seconds since 1st Jan 1970 so that date I was seeing implied an empty field in the database so I initially thought that it was just the form not saving the value.

I went through the code time and time again and tried a few tests on other records and all seemed fine.

It was only when I looked in the database that I saw the date had been stored (2041-05-20) but for some reason wasn't being displayed.

It turns out that after a date in 2038, the number of seconds since 1st Jan 1970 is too big to store as an integer value so it was returning nothing.

There is a fairly easy solution though which is to use the date_create function to return a DateTime object and then use the date_format function to format it.

$date = date_create($mortgageEndDate);
$end_date = date_format($date, "d-m-Y");

The only caveat is that this requires php 5.2 or greater.

However, it is worth thinking about because 2038 isn't that far away especially when you are dealing with 25 year mortgages!

Illegal Characters in jQuery

Added on: Wednesday 14th September 2011

I was caught out the other day by some jQuery form validation not working on one of CMS sites. The reason, I eventually found out was an illegal character in an element ID.

Our CMS includes a form builder that allows users to add contact forms, surveys etc to their websites.

The form builder generates the html to go on the page and users the jquery validate and metadata plugins to do the validation.

The validate plugin works by running the validate() function on a form element when the page loads. for example:

$("#form").validate();

Now as it happens, the form builder creates the 'form id' from the name of the form as defined by the user - after stripping out illegal characters of course.

I spent hours trying to find out why the javascript validation on this particular form wasn't working and eventually traced it to the fact the the 'form id' contained ( and ) characters.

The form builder wasn't stripping these out and there was no error in the browser - I guess jQuery just wasn't finding the element at all.

I've altered the form builder now but just thought this might be useful for anyone else having problems.

Syntax highlighting on web pages

Added on: Wednesday 24th August 2011

I've recently been looking at various solutions for highlighting code snippets as I wanted something to make it easier to publish on this blog. I've settled on highlight.js as it is so simple.

Part of the reason I haven't published an article for a while is that I wanted a better way of displaying the code than I had before. (The other reason is lack of time!)

highlight.js is a javascript library releases by Software Maniacs and setting it up couldn't be easier because it automatically recognises the scripting language - well, as long as you use one of the 41 that it knows about.

To add the feature to a web site you just have to reference a script file and a stylesheet. These are both hosted on external servers so you don't even need to download them.

The javascript reference is:

<script src="http://yandex.st/highlightjs/6.0/highlight.min.js"></script>

and the stylesheet reference is:

<link rel="stylesheet" href="http://yandex.st/highlightjs/6.0/styles/default.min.css">

If you want a style other than the default just replace default in the url above with the name of the style. (see the site for available styles).

Thats about it, except that you have to initialise the script when the page loads - after that it will format any code within <pre><code> tags.

I had a slight problem here in that I use WymEditor for my blog which doesn't easily allow me to wrap text within these two elements.

However, a bit of jquery soon sorted this. Within the editor, I give any code a class of code_block. The jquery then goes to work on each element with this class and wraps the contents in <pre><code> tags.

I've included the script below (because I can highlight it now!)

$("p.code_block").each(function(){
var txt = $(this).html();
$(this).replaceWith('<pre><code>'+txt+'</code></pre>');
});

Except I've found a problem. As the code above contains < and > characters to show the html being output, it isn't formatted. If I take that line out it recognises it as javascript

PHP and Ajax with special characters

Added on: Thursday 14th January 2010

Here's a quick tip when using Ajax and PHP to output HTML to a browser. If the code contains special characters such as currency symbols you may get unexpected results.

I was recently working on an ajax script to update our CMS shopping cart and noticed some strange behaviour.

The html for the cart is stored as an editable template to allow it to be styled for different sites. The currency symbols are written into the template.

When the page was first loaded the PHP script processed the template and displayed everything just fine. However, when the cart was updated via the ajax script the currency symbols were replaced in Firefox by the FF FD code for a missing character in a font.

I thought at first that by using the javascript escape function to encode the characters or by using html entity encoding in PHP it would solve the problem. Unfortunately, both of these didn't work.

A quick Google search and I found this article on Roshan's Blog.

The solution is to set the character set in the script that outputs the html to the ajax function using the PHP header function (example below).

header("Content-Type: text/html; charset=iso-8859-1\n");

Google Calendar API PHP Class

Added on: Saturday 22nd August 2009

I recently wanted to update a Google calendar via the API using PHP. There are plenty of samples on the Calendar API site but they all use the Zend framework which I didn't want to use.

I've been working hard on a new version of the projects module of our CRM package which enables users to create quotes that when accepted are converted into tasks that can then be scheduled on a calendar.

This also creates a timesheet that users can then update on a daily basis and any completed tasks are automatically added to a customers account ready for billing.

As part of this I thought it would be a good idea to add any scheduled tasks to a Google Calendar - mainly because its then easy to sync this with Outlook.

As I said I wasn't keen on using the Zend examples as I don't use the framework elsewhere so I searched for a PHP 'wrapper' for the calendar API.

As is often the case, PHP Classes had just what I wanted. The class I found also used another class for the Curl calls. The only problem was that the requirements stated that PHP5 was needed and the server I was using only had 4.4.

However, after a quick look at the two classes it seemed that all I would need to do was change the public and private variable declarations to just var. This did the trick up to a point but although the first curl call to login to the account worked fine, the attempt to add a new event failed.

After a liberal sprinkling of curl_error calls, I tracked this down to the curl_execute function returning the error: Failed to open/read local data from file/application.

A quick search on Google and I came across a post on MilkHub that solved the problem.

The wrapper class calls curl with POST data to login to the account but then uses HTTP headers to update an event. Apparently if the POST data is empty you should explicitly set the CURLOPT_POSTFIELDS to a zero length string, something the curl wrapper class wasn't doing.

A quick change to the curl class and it all worked fine. The files are included here if anyone else is having similar problems.

Using GROUP CONCAT in MySQL

Added on: Thursday 30th July 2009

Yesterday I had the need to get a list of unique values from a MySQL database where the values were stored as a comma delimited set. There were several ways to approach this but I wanted to try to minimise the amount of processing required.

Good database design says that it would be better to store the values in a table that links the value to a record in another table - allowing multiple values for each record - but I was stuck with the current arrangement so had to make do.

The particular field in the database could contain a single value such as item1 or multiple (comma separated) values in any order such as item2,item1 or item1,item3

The first thing that occurred to me was just to retrieve the records in question and then use PHP to loop through each one, splitting them into individual values. The code would then add the value to an array if it wasn't already in there or ignore it if it was. Finally it would need to sort the array.

This only involves a single call to MySQL but there is still a lot of processing for PHP.

I then decided to make use of the GROUP_CONCAT function in MySQL as follows:

SELECT GROUP_CONCAT(items ORDER BY items SEPARATOR ',') AS items

This produces a single row with a field containing a comma separated list of all possible combinations eg. item1,item1,item3,item2,item1 from the example above.

By making use of the array_unique function in PHP there are only three lines of code required to create the unique sorted list.

$ary = explode(",", $items);
$tags = array_unique($ary);
sort($tags);

I am assuming in the above code that $items is the value of the field resulting from the MySQL query. The script then uses explode to create an array of values - array_unique removes any duplicates and the sort function ensures they are in alphabetical order.

Finding the position of a record in MySQL

Added on: Saturday 4th July 2009

Normally when retrieving records from a MySQL database you loop through the results and perform an operation on each in time. The other day, however, I simply wanted to get the position of a certain record in a set - nothing more.

Many people say that the actual position in a result set is meaningless because it depends on the sort order and other factors.

To a certain extent this is true but consider this scenario:

Our CRM application allows you to scroll through groups of records and keeps track of which record you are on even if you go to other pages and then come back.

It also features a navigation bar to allow you to move forward or backward through the set.

As it uses session variables these will be cleared once you've logged out and rather than using database storage I just wanted a quick way to jump to a certain record in the set.

A dropdown list of all records in the set works fine for small sets but the overhead of loading this is unacceptable for very large sets.

Instead I wanted users to be able to find a record in the database and then be able to jump to scroll mode at the position of that record in the set.

I could have simply pulled all the IDs from the set and then looped through them in php until a match is found for the current record but I was sure there was a way to get the position of a record using MySQL alone.

It turns out there is and the code is something like that shown below:

SET @C=0;
SELECT C FROM (SELECT @C := @C +1 AS C, [recordID] FROM [sqlStatement]) X WHERE [recordID] = Y;

Where C is your counter, [sqlStatement] is the SQL to retrieve the IDs (ie [recordID]) in the order that you want them and Y is the ID of the record you want the position of.

Note that this requires two calls to MySQL so if you are using PHP you'll find you have to make them separately as mysql_query doesn't support multiple queries.

Restricting API usage on websites

Added on: Thursday 4th June 2009

It seems any web application worth its salt nowadays must have an API that developers can use to create mashups or display content within their web pages.

There are various ways that these APIs can be called from a web page - PHP users can use the CURL library - but the most common is an AJAX call via javascript as this can be used on any web page as long as the browser has javascript turned on.

The problem with the javascript option is that the call to the API is exposed on the page for anyone who wants to see.

This might be fine if your API is only for displaying content and doesn't allow interaction with a database but you still might want to restrict the usage so as not to overload your servers.

One of the most common ways to do this is to get users to register and give them a 'key' to include in any call. Your server then validates this key - in the case of Google Maps (version 2) the domain of the calling website is also checked to make sure the key hasn't just been copied from somewhere else.

A search for options on securing API calls brings up this method amongst several others but I haven't yet found a good article on how to put it into practice.

I am rolling my own API at the moment for our Contact Management Software and needed a way of protecting client data by ensuring that only registered websites use the API.

I initially thought of using the server variables exposed to PHP to check the originating site but that didn't work - probably because there are several redirects before the authorisation script.

Then I got thinking that there must be a way to do it with mod_rewrite.

I was already using mod_rewrite in order to set up a clean REST style url for calling the API and I wondered if there was anyway I could pass the calling page url to the script. It turns out there is.

mod_rewrite allows the use of variables to check for certain conditions and you can use the HTTP_REFERER variable in a condition to check if the user has come from a certain page.

I then thought that it might be possible to pass the value of this variable into the rewritten url and so make it available to the checking script.

In fact its easy and it works just include the variable within curly brackets and prefixed by the percent sign and it will then be part of the query string passed to the page.

eg http://www.slowducks.co.uk?d=%{HTTP_REFERER}

Its not foolproof as it can be spoofed and its not always available but my API will never be as popular as Googles (sadly) so I can handle the support issues.

PHP functions and varying numbers of arguments

Added on: Friday 29th May 2009

There are times when you want to pass an unknown number of arguments to a function. A simple example might be a function to add a list of numbers.

At this point I can hear people shouting - 'to do this you just pass a single parameter that is an array of numbers to add'.

In fact this is what I thought I could do for a bit of code that I needed to add to our content management system but it didn't work.

The problem was that I was using the php function call_user_func_array. This function has two parameters, an array containing the class and function to call and a second array with the function parameters.

I mistakenly thought that the call_user_func_array function would pass the second parameter as an array to the nominated function but it doesn't.

If the function being called has one defined parameter then call_user_func_array passes it the first item in the array and ignores the rest.

There is an easy way around this using the func_num_args and func_get_arg php functions.

In the case of adding a list of numbers the code within the function would be:

for ($i = 0; $i < func_num_args();$i++) {
      $return = $return + func_get_arg($i);
}

Just remember not to specify any parameters in the function definition.

Ordnance survey maps on your website

Added on: Thursday 28th May 2009

I've recently come across an application from the Ordnance Survey that allows you to embed maps on your website much like the very popular Google Maps.

The difference is that the mapping used is from the Ordnance Survey which has much more detail than Google Maps.

In all other respects this looks to be very similar to Google's application - you need an API key which covers a single domain and they quote usage limits of 30,000 map tiles of data and 1,000 place name lookups a day.

The service offers maps covering England, Scotland and Wales and you can place any kind of information that has a geographic reference on top of the Ordnance Survey maps and interact with a map - pan, zoom in and out, add markers and polygons.

It all sounds very exciting. Just need to learn the API!

Expect a post here soon with details of how I get on with it.

For more information see the OS OpenSpace website.

Short URLs

Added on: Saturday 9th May 2009

URL shortening services such as tinyURL have been around for some time now. Originally developed to overcome the problem of links wrapping over several lines in email clients they are now more prevalent because of social media applications such as Twitter

With only 140 characters per 'tweet', if you want to include a link to your website or an article on another site you don't want the URL to take up most of the message.

This is where URL shortening services like tinyURL can be useful. It is no longer alone however. A quick Google search shows there is no shortage of other services.

The top 4 services used on twitter are tinyurl.com, bit.ly, ustre.am and cli.gs.

Each of these tries to offer something unique over the other. For example a short url on tinyurl is 25 characters long and on bit.ly it is 20 characters but yet another service r.im offers short urls that are 14 characters long.

So if you need to squeeze an extra few characters into your message then r.im could be for you.

Is this the only reason for choosing a URL shortening service though?

For me there are two other essentials

  1. To be able to track clicks on a URL
  2.  To be able to use an API in order to create the shortened URLs.

Many of the current services do offer both of these features and there is one that I've found - tr.im - that even has the statistics available through their API.

Usually the API is very simple - you tack the URL you want to shorten onto a URL for the API and the return value is the shortened URL.

For example for tinyURL the address is:

http://tinyurl.com/api-create.php?url=[[url]]

where [[url]] is the URL to shorten. In fact it works both ways - if [[url]] is a tinyURL (ie shortened URL) it returns the original URL.

We've now wrapped several of these APIs into a function call and made a Widget to shorten URLs in our CMS.

Of course it is not difficult to create your own URL shortening if you have access to a database and server side scripting.

  • Create a table in the database to store the original URL and a unique ID for this URL.
  • Add some server side code to take a URL and check whether it already exists in the table - if it does, grab the unique ID - if it doesn't create a new record then grab the unique ID.
  • Replace the original link with a link to a new page (with a short name like link.php) and pass as a parameter the unique ID.
  • This page then looks up the original URL associated with this ID and redirects the browser.
  • As an added bonus the page can also record visits to the page (and hence clicks on the shortened URL).

You can even go further and add a userID field to the table so that each unique ID is now for the user/URL combination meaning that you can track clicks by individuals.

For more information, contact me using the contact page.

Of course unless you've got a very short domain name in the first place you are never going to match any of the above services but you will have more control.

jquery form validation and IE6

Added on: Friday 17th April 2009

The validation plugin for jquery is a simple way to add sophisticated validation to any forms on your website. However, I recently had a problem with IE6.

The validation on the website I was working on was fine in Firefox and Opera and indeed Internet Explorer 8 but in IE6 the Submit button went straight to the next page without checking the form.

I noticed there was also a javascript error just before the page submitted.

A quick search on Google took me straight to the jQuery Validation website where I quickly found that this was a problem with the character encoding of the page.

It seems that if the character encoding is set to UTF-8 then there is a regular expression in the javascript that throws IE6. If the character encoding is ISO-8859-1 then all is fine.

The suggestion to resolve this was to track down the expression and removing the offending characters. However, as I was using the packed version of the file, I couldn't use this so I installed the unpacked version instead.

Obviously in the unpacked version the code is different as the validation worked fine in all browsers that I tested.

To summarise then - if you are using jQuery validation and your character encoding on the page is utf-8, use the unpacked version of the script.

I thought I'd post this as it saved me hours of messing around. And thanks to those before me who have found the solution to the problem.

Geotagging website content

Added on: Thursday 19th March 2009

It seems that nowadays every site must have a map to display content. Whether its a business search facility, a store locator or a list of attractions on a tourist trail users expect to be able to find information using maps.

It is easy enough to add a map to a website and display content (see a previous article on the Google Map module for our CMS) but you can only do this if the content has spatial data associated with it.

It is possible to convert a zip code or UK postcode into the latitude and longitude required for Google Maps but this is usually done in an external application and its a tedious job to copy and paste these coordinates back into your own application.

Also, especially for UK postcodes the conversion isn't very accurate in rural areas and my own postcode plots about 10 miles out on the map.

Another alternative for the UK is to convert an Ordnance Survey map reference to WGS84 latitude and longitude but this requires the reference to be more accurate than the standard six figures.

No, the most reliable and simplest way is to find the location on the map itself and mark it directly.

Having added the map module to the CMS, I thought that it needed an easy way to geotag any content on the site so I set about integrating a point and click method for positioning any data.

All content is added via a form and to keep things clean I decided the map should pop up rather than be inline which would take up too much room.

Geotagging data

The map is opened via the '...get position' link.

My favourite 'pop up' window isn't really a pop up at all but is the excellent thickbox which uses the DOM to display a window on a new layer. This in itself caused me a few problems trying to get the map to load into this window. However the article at dropstones helped me solve these.

When the link is clicked, the script loads the map into a hidden div an the thickbox tb_show method is called using the inline option to pull the content from the hidden div.

Google map in pop up window

The map has the usual controls so that you can zoom in for better accuracy and once a point is clicked on the map the lat and long fields on the form are updated.

So you can now add position information to any content on your website and I've set this up as a special field type so that the boxes and pop up link automatically appear on any form that requires a location field.

The code used for the process is contained in this text file.

The power of jquery

Added on: Wednesday 4th March 2009

With the recent rise in popularity of online applications and the subsequent drive to create rich user interfaces comes a host of powerful javascript libraries.

There are now numerous javascript libraries available to help simplify the more mundane tasks involved in writing your code.

I'm not going to recommend any particular library over another but as a developer I've decided to pick one and stick to it rather than trying to use the best features from each of them. This is a practical approach as I simply don't have the time to learn the syntax for each of them and keep abreast of developments.

I'm also not going to get into why I eventually chose jQuery but suffice to say that at the time it seemed to have everything that I wanted for the sort of projects I was working on.

Anyway, if you haven't looked at jQuery yet then it might be worth your while because once you get to grips with the syntax, I promise you it will take your javascript to new heights.

I'm writing this because yesterday I revisited some code (originally written with jQuery) for an application where I wanted to enhance the usability even further.

The application is based on our CRM software but has a module that creates a seating plan for members going to monthly lunch meetings.

The client is a networking organisation so one of the rules is that where possible no members should sit with someone they have sat with before. The software automatically checks back a certain number of lunches and tries to place people according to this rule.

It also takes into account any specific seating requests from members and ensures that no two people from the same business category are on the same table.

Once the calculation has been done the administrators are able to go in and use a drag and drop interface to make any final changes and this is where jQuery comes in handy.

Although there is a warning message whenever a person is dragged to a table and the resultant move violates any of the above rules, I wanted some visual feedback when the mouse hovers over the person to be moved.

To do this I got the server side PHP to create a table of which guests have sat with each other. When the page loads, the ids of each of the people a person has sat with are added to the class for the html element for that person.

So a typical class might look like class="2401 345 6750..."

I then created an 'over' class (red border) and got jQuery to apply this class to each element containing the class corresponding to the id of the element the mouse is over. The code is below:

$("li.block").mouseover(function(){
var el = $(this).attr("id");
$("."+el.addClass("over");
});

Just 4 lines of code (and thats to make it easy to read!) and all the people who have previously sat with a person are highlighted on the mouse over event.

(There is a corresponding mouse out event as well to remove the highlight).

Following some comments from users who said it would be nice to know not just that two people have sat together but also how long ago this was. I initially thought that this might just be something that pops up in the warning message rather than on mouse over but after a bit of thought I realised it was quite easy.

I altered the PHP code so that in addition to storing the ids it also stored how many lunches ago the two guests sat together. As the page loads this number is appended to the id and separated by an underscore so the class now looks more like - class="2401_2 345_1 6750_2..."

I then created the classes for the number of lunches ago that the clash occured - simply over1, over2 etc.  These could be styled with different borders or backgrounds but I've chosen to add an image of the number on the right hand side of the element.

The revised jQuery is just:

$("li.block").mouseover(function(){
var el = $(this).attr("id");
for(i=0;i<6;i++){
$("."+el+"_"+i).addClass("over"+i);
}
});

I'm sure this can be streamlined further. I think I can probably remove the for loop and use the jQuery each function but I'm still learning and this does the trick at the moment.

Pretty powerful stuff though and it just shows how easy it is to add some impressive functionality to your application.

Internet Explorer reserved word problem

Added on: Wednesday 25th February 2009

I was working on an update to some software the other day and while it worked fine in Firefox and Opera it kept throwing up a javascript error in Internet Explorer.

I am always hopeful that one day there will be a time that you can create a web application knowing that it will look and operate exactly the same across all browsers without the need for extensive testing in each one.

Unfortunately this won't happen soon so for the time being we are stuck with applying fixes for the various shortcomings and vagaries of modern browsers.

To cut a long story short, when I finally tracked down the error it had nothing to do with javascript at all.

The problem appears to be that I had an element on the page that I had given an id of 'tags' (because I was updating the tags field for a record via AJAX).

Simply changing the id of this element to something other than 'tags' solved the problem, so it seems that this is probably a reserved word for IE.

It was only afterwards that I did a search on Google (probably because I didn't know what I was looking for initially) and found some references to javascript print errors to do with an id of 'tags'. 

A hidden gem in PHP

Added on: Friday 20th February 2009

One of the most common tasks when developing websites or online applications is date manipulation. You might for example need to create a list of memberships due to expire within the next month.

Of course PHP has plenty of date/time manipulation functions you can use but there is one called strtotime (string to time) that is very underrated.

The description in the manual says - Parse about any English textual datetime description into a Unix timestamp

A timestamp is simply a numeric representation of a date and it is this value that can be used to compare two dates or add a number of days, weeks, years etc.

I have been using strtotime for years but up until recently it was mainly to convert dates pulled from a database (as they are retrieved as strings).

If I wanted to work out the date in a months time I would use mktime. For example:

$dt = mktime(0,0,0,date("m")+1,date("d"), date("Y"));

This is not too complicated but it still has three other function calls (to date()) within it.

Consider however this alternative:

$dt = strtotime("next month");

A bit simpler, eh?

As the description says you can use just about any English textual datetime description. I tracked down a reference giving some information on the date input formats but essentially you can use common phrases such as 'Monday', 'next week', 'last year', '1 month', '1 day ago'.

By default the function calculates the timestamp from the current date and time but you can also include as a second parameter a timestamp from which to calculate the new date from.

I discovered this whilst looking for an easy way for users to add tasks to our contact management software. I wanted a way for people who were away from their office to be able to add a task or reminder by email from their Blackberry or iPhone.

This had to be as simple as possible - the email is sent to a specific address with the due date of the task or reminder in the subject and the details in the subject.

I thought a nice touch would be to allow users to put tomorrow or next friday in the subject and I thought I'd have to write a function to process these into dates - but then I discovered that it was already done. Fantastic!

Sorting photos by date taken

Added on: Wednesday 4th February 2009

I had a bit of a disaster last night whilst trying to get all my digital photos into a web based gallery and needed a quick bit of code to fix it.

The gallery uses a MySQL database and has a facility to index new photos that have been downloaded into a new folder.

Once the images have been indexed by the database they are moved from the download folder and renamed to match the reference in the database.

I made a stupid mistake when using this and managed to rename all the existing photos so that they no longer matched the database reference.

Not a major problem you might think as it is easy to write a quick php script to rename all the files in a folder.

However, the database also contains metadata about the photos and the new filenames no longer sorted into date taken order as they would have done when first indexed.

Exif to the rescue

Luckily, php has a set of functions to extract the 'exif' data from a digital photo and using the read_exif_data function I was able to get the 'DateTimeOriginal' value for each photo.

It was easy enough to load these into an array, sort them and then loop through the array and rename the files.

So 10 minutes later I had my photo gallery back to the state it was in before I messed it up.

I've put the code in a text file for use by anyone who wants to do this.

Page has expired

Added on: Thursday 29th January 2009

On many websites with search forms, if you do a search and then go to another page to view the results, clicking the back button brings up a 'page has expired' message.

You can of course hit the Refresh button but many people want to go back to the page and have the original search criteria - perhaps you want to view each result in turn.

I've tried various ways in PHP to do this by changing headers etc but the single line of code that works perfectly every time is as follows:

header("Cache-Control: max-age=300, must-revalidate");

The max-age is in seconds so the above keeps the page 'alive' for 5 minutes. You can change this as you like.