The Back Burner

Want to add one (or more) days to a date in PHP? Timestamps are simply numbers of seconds, so it seems so easy and obvious to do this:
$mydate = (60*60*24)+$mydate;
or even just
$mydate = 86400+$mydate;
Don’t be tempted! Twice a year, because of daylight saving, the day doesn’t have 24 hours in it; it has 23 (spring) or 25 (autumn). So all of a sudden you are likely to find your code behaving in a manner which is downright weird. Been there, done that. The approved way is just as simple and far less likely to result in hair-tearing:
$mydate = strtotime("+1 day", $mydate);

Edit: here’s a function for calculating differences between times. Untested for resistance to DST, but could be useful in some situations. Ironically, the post he links to about calculating date difference more precisely in PHP (also untested) looks as if it does not take this problem into account. Go figure

The scenario: a mixed network of Windows and Linux PCs (2 Windows XP, one Xandros, one SimplyMEPIS) which need access to edit files in a directory on one of the Linux servers. Here’s what I did: I’m not sure all the steps were necessary, but none of them is sufficient on its own!

It is taken as read that the PCs are all in the same workgroup, and Samba is installed and running on the Linux PC with the files to be shared.

1. Create users with the same name and password on all the PCs. Create a group on the Linux PCs and add the users to it (in this example the group is called editors). Note, the numeric user and group ids on the Linux boxes must be the same. If you are using a GUI interface to create users and groups, check the numbers they end up with. If they are not the same you need to use e.g.
groupmod -g 1002 editors
to change one of them to match the other. This might invalidate the group of any files belonging to users in that group, so check and reset the group if necessary, using chgrp:
chgrp -R editors www

You can make editors the default group for the users if you like, but it’s not necessary.

2. Create the directory to be shared (www in this example); it should be owned by one of the users created earlier and have the group set to editors. Use chmod to make it writable by owner and group. Now, set the sticky bit to force files to inherit the group permissions:
chmod g+s www

3. You can edit /etc/profile.d/profile (as root; the exact path of this file may vary) and change the umask line to read
umask 002

This globally forces new files to be created with group write permissions.

The steps so far have ensured that users in the editors group on the Linux box can edit files in the www directory and its subdirectories. Now you need to share the directory using Samba. Use a GUI tool if you like to set up the share (SimplyMEPIS has a nice one). But ultimately you will need to edit /etc/samba/smb.conf (as root of course) to make sure it’s set up correctly. This file is not difficult to understand, but make a backup copy first in case you trash anything. The minimum needed is the following:

4. In the [global] section, under authentication, set
security = share
This is not the most secure way, but it’s OK on a suitably small and private network. I tried using security = users, but the Xandros PC didn’t seem to like this at all; the file manager hung on opening when any of the shares were mounted.

5. In the section for the www share, force the user to be one of the users you set up earlier:

[www]
public=yes
browseable=yes
path=/opt/www
writeable=yes
force user=veronica


You might have other stuff in there, but I think this is the minimum (it was the “force user” that finally got it to work for me). If you want you can restrict access by IP address to make it more secure, e.g.:
hosts allow = 192.168.1.0/24

Don’t forget to stop and restart Samba if you have edited the smb.conf file! As root:
/etc/init.d/samba restart

6. Test by mounting the share on another PC and creating, editing, or deleting a file on the share.

I have been looking for a while for a tool to create Entity Relationship Diagrams (ERDs) on Linux. On Windows I used Dezign for Databases, an excellent tool which I would still be using if it was available for Linux. Linux tools that do as much as it does are few and far between. DBDesigner4 is open-source but wouldn’t run at all on my system and doesn’t seem to have any visible means of support. After much searching I ended up evaluating a commercial (i.e. non-free) product, Data Architect, from theKompany. After a few false starts trying to install the demo from an RPM, I managed to get it to work by the simple expedient of downloading and unzipping the tar archive. I had to edit the shell script to replace ksh with bash, but then it worked.

However, it requires ODBC to talk to databases (not an essential feature if you are just doing design amd then generating the necessary SQL scripts, but definitely nice to have). Being a Microsoft invention, ODBC is not that easy to get working on Linux. It took me all morning, but I succeeded in the end. So here’s how to do it on Xandros Home Premium:

1) Use Xandros Networks to search for and install unixODBC, the package that provides ODBC support (or use apt-get, or whatever package manager you use, or download direct from unixodbc.org).
2) As far as I could tell this did not install the GUI front-end that allows you to manage ODBC data sources in a similar way to the ODBC manager on Windows. This is not essential because you can simply edit the relevant configuration files if you know what you are doing. Not knowing this, I visited unixodbc.org and downloaded unixODBC-gui-qt-2.2.11-1.i386.rpm, which I then installed using Xandros networks.
3) Great, ODBC support is now available and the GUI works! However, I don’t have any drivers. I want to use it with MySQL, so it’s off to MySQL to download the necessary driver. Again I downloaded an RPM and installed using Xandros networks. I quickly found that this didn’t work with Xandros. So a quick Google sent me off here for a Debian package. Goodness knows why MySQL don’t provide a Debian version.
4) Now I again ran the GUI front end installed in step 2, and hey presto, the driver was there. The bad news: when I tried to set up and connect to a data source I got a message saying that the driver couldn’t find /var/mysqld/mysqld.sock. Not surprising, since this file did not exist. More googling and a poke around my file system established that the file was actually at /opt/lampp/var/mysql/mysql.sock. 5) So I needed to edit the odbc.ini file to tell it where to find the socket:

[MySQL-test]
Description = MySQL database test
Driver = MySQL
Server = localhost
Database = test
Port = 3306
Socket = /opt/lampp/var/mysql/mysql.sock
Option =
Stmt =

Progress! Now I can connect, but when I try to authenticate with a username and password, “Client does not support authentication protocol requested by server; consider upgrading MySQL client.” Aha, I’ve encountered this before; it’s to do with the password hashing in newer versions of MySQL being incompatible with clients expecting older version. So, simple solution:
6) Set up a user for ODBC purposes, with the appropriate privileges, and issue the command:

SET PASSWORD FOR odbcuser@localhost = OLD_PASSWORD('mypassword');

7) Try again, and yes, I can browse the databases and tables now! Nice added bonus: ODBC is also available in OpenOffice, so I can access my databases from there too. Let’s just hope that DataArchitect does the job …

Scriptaculous is one of the better-known Javascript/AJAX libraries, with useful built-in objects for adding Web 2.0 functionality to your pages. I’ve tinkered with it but hadn’t found a real-life application for it until I needed to allow a client to reorder a grid of thumbnail photos by dragging and dropping them. Added complication: she uses Safari.

Oh how I hate coding Javascript! I got something kind-of working, including an Ajax callback to update the database, but it wasn’t very satisfactory. So I put it aside for a while and looked at it again when I had some spare time. This time I came across Greg Neustaetter’s PHP wrapper class for Scriptaculous lists. Aha, the power of Scriptaculous without the pain of Javascript! With the aid of his well-documented examples it only took me about half an hour to create a proof-of-concept. So Web 2.0 here I come …

A handy reminder today of the incredible potential for both bad and good represented by the Internet.

The good:
ConceptShare, a new online collaboration tool for graphic designers to work collaboratively, viewing and marking up designs online. A rare example of excellent use of Flash to do something genuinely useful.

The bad:
Stanford University says that if we were designing the Internet today we wouldn’t start from here.

We believe that the current Internet has significant deficiencies that need to be solved before it can become a unified global communication infrastructure. Further, we believe the Internet’s shortcomings will not be resolved by the conventional incremental and ‘backward-compatible’ style of academic and industrial networking research.

The ugly: new exploits using Javascript inserted on unsuspecting third-party websites to at best show unsolicited adverts and at worst download and install malware on Windows computers. An interesting post from Ethan Zuckerman explains, and proves the point that if you accept input from users and display it on your website for others to see, you should filter it for unwanted HTML and script tags first! Interesting that Google is now detecting these exploits and warning site visitors.

Hat-tip for pointers to all of these to John Naughton’s eclectic Memex.

The change of software in my last post meant that not only the domain, but all the page names and the entire structure of my recipe site changed — a big no-no for search engine purposes! On the other hand, the traffic this application gets isn’t actually worth anything to me in monetary terms — quite the opposite, it just uses bandwidth — so I wasn’t hugely bothered if it lost rank in Google for a while. In fact it was a golden opportunity to test the best way of handling this situation.

I started off by buying a brand new domain name, setting up MovableType, importing all the data, fixing up the design, and testing it to see that it all worked OK. At this stage it was a secret from Google. Once I was happy, I wondered just how I was going to keep all that precious traffic to the old site. I didn’t want to devote a huge amount of time to it, so I decided I’d just use 301 redirects (this is a header sent by the web server that tells the page requester that the page has “moved permanently”; there is also a “temporary redirect” status).

Thank goodness for dynamic pages! The old system used a single page called ShowRecipe.asp which took the id of the recipe and displayed it. So I just recoded this page to take the id, look up the recipe title, and convert it into the new URL (MovableType uses “search-friendly” URLs constructed from the recipe title). Then it does a 301 redirect to the URL on the new site. I removed / renamed the few other pages on the old site.

I added the new site to my Google webmaster tools page and monitored the results. Google came and spidered a few pages from the site within a couple of days, and I was soon seeing almost the level of traffic I’d had on the old site. Within a week there were 23 pages in Google’s index, which seemed not bad for a brand-new domain registered less than a fortnight before and with no incoming links yet, apart from our own site. As of now, Google has spidered again and picked up over 140 pages.

The process is evidently not finished yet though, as old redirected pages are still showing high up in Google (#6 for “pitta bread recipe” for example). Of course if anyone clicks on those results, they will automatically end up on the new site anyway, because of the redirect.

I’m probably not going to bother going after links to the old site and asking people to change them, but I would if this was a commercial site. Otherwise old linked-to pages seem to hang around in Google forever.

So at present just doing the 301 redirect looks as if it is going to work pretty well. If you completely change the structure of your site it’s well worth doing a specific 301 redirect for every page I think, for the benefit of human visitors rather than search engines.

OK, so this blog doesn’t get updated very often, but I have three blogs to look after now! Currently it’s all change on the blogging front. When I logged into the Back Burner just now, Google insisted that I upgrade to the new Blogger. The process appeared to be painless, except that the post entry screen now refused to accept any input — I typed happily away only to realise my words were disappearing into the ether. Yet another application that wasn’t tested in Opera . I fiddled in the settings and switched off “Compose” mode, and all is well now. It’s a shame that none of these WYSIWYG editing tools works in Opera; I don’t know whether it’s Opera’s fault, or just developers who don’t care about this small segment of the browser market.

In the meantime, I decided recently that it was time to revamp the recipe database which is currently responsible for much of the traffic to our company site (see Embarrassed by Success). When we created this demonstration CMS back in 1999, database-driven sites were a lot less common than they are now. It seemed inappropriate to keep this demonstration module on our site when it no longer reflects what we are capable of (we are much cleverer than that now!). It also used an Access database, which is hardly state-of-the-art. On the other hand I still find it a very useful system for storing my favourite recipes, and it gets a fair amount of traffic.

But time to completely redevelop the system (in Ruby on Rails for example) was lacking. So rather than reinventing the wheel again, I thought I might as well simply extract the content from the database and pour it into a blog. I experimented briefly with the latest blogging poster child, WordPress, before deciding I felt more comfortable with my old favourite Movable Type. This surprised me slightly, since Wordpress is written in PHP, which I am much more comfortable with than the “old-technology” Perl behind Movable Type. But The Movable Type administration interface feels much more polished and flexible than Wordpress’s and its templating system is easy to understand and manipulate. Not only that, but the Movable Type architecture allows you to generate PHP pages, so you can have the best of both worlds by incorporating PHP into your templates to provide dynamic elements.

Movable Type has moved on since I last installed it back in the days of version 2.66. It now incorporates a whole new plugin architecture, allows you to tag posts as well as assign them to multiple categories, has improved comment handling, and includes a style switcher so you can easily apply a theme without having to dabble in templates. It also easily handles multiple blogs and authors. Its major downsides compared to WordPress are that it doesn’t cater sensibly for static pages outside the blog structure, and its image handling is pretty rudimentary. Oh — and some parts of the admin interface don’t work properly in Opera!

Anyway, having installed Movable Type, grabbed a theme from MT’s style contest, and done a lot of cutting-and-pasting of content, I decided that the shiny new version of my recipe database deserved its very own domain. To my amazement, both larecettedujour.org and recettedujour.org were available, so I snapped them both up, and pointed them temporarily at a subdirectory of our company site. So … drumroll … here is La Recette du Jour in all its glory!

The task: provide a neat hierarchical tree as a web interface to a project management system where any project might have a variable number of sub-projects. A tree structure in other words. So I ended up with this:

Nice and neat… the only problem is that on the face of it, relational databases aren’t very good at handling trees, and after a bit of experimentation I was ready to write a whole lot of procedural code in ASP to take a recordset and build a hierarchy from it. But of course I googled first. The initial responses included Joe Celko’s (in)famous nested-set tree structure which was far too complicated for my feeble mind to grasp and didn’t look as if it would provide a simple interface for users to add new projects.

And then I came across Philip Greenspun’s interpretation of the classic problem of an employee table in which some employees are managed by others who in turn are managed by others, all the way up to the big boss. He used the Oracle-specific CONNECT BY PRIOR which I’d come across elsewhere, but explained it in a way I could actually understand. Just the ticket! So with a single SQL statement I now had my neat hierarchical recordset.

Next: how to present it. Serendipitously, I happened to be looking at Matt Kruse’s Javascript Toolbox for another reason and noticed a DHTML Tree. I like his stuff, so I had a look. Magic! A simple bulleted list is transformed into an expanding and collapsing tree with a few lines of CSS and a sprinkling of Javascript. The nice thing is that in non-CSS and non-Javascript browsers it degrades to a simple list so it’s accessible too. My brain steamed lightly as I wrestled with the logic to get the opening and closing list tags in the right places as I read through the recordset, but a couple of hours’ work had given me an ideal solution and in another half hour I had a simple form for adding new projects at any level in the structure. So humble thanks to Philip and Matt for being far cleverer than I will ever be!

When I used Windows I had a host of small, unobtrusive utilities that made my life easier. I’m sorry to say that in most cases I haven’t found Linux replacements which work as well. But they might work for you, so here they are:

Time tracking

Even if you don’t bill by the hour, if you are selling your services you need to keep track of billable hours to make sure you are estimating/charging appropriately. I like to use a stopwatch-style time tracker that simply runs in the background. On Windows, I used an excellent little utility called Allnetic Time Tracker. It sat discreetly in the system tray and with a right-click I could quickly start, stop, and switch tasks. The program has configurable idle-time detection, so if you forget to stop it and go away, you’ll be greeted on your return with a dialog box asking whether you want to discard the idle time or log it (the latter being useful if the reason you went away was to go to a meeting or do some non-computer-based task that still needs logging).

You can set up projects with tasks and sub-tasks, and everything is displayed in a neat three-paned window, making it easy to get an overview of total time at project and task level. Each logged time period can have a note attached to it, useful for detailing what you did. Allnetic also has very comprehensive reporting options — you could theoretically generate invoices directly, although I never did this.

Well, there’s nothing like this for Linux as far as I can tell. I tried a few applications and ended up with the default KDE tool, KArm. It does basically the same thing as Allnetic Time Tracker, but in a less sophisticated way; all projects are displayed in a single long list, which makes it rather unwieldy if you have a lot of tasks. It too sits in the system tray, but to start, stop or switch tasks you need to restore the window rather than being able to do everything with a right-click. It only keeps totals of hours for each day, not the actual times that you worked, and reporting is very limited; all you can do is copy either the totals or history between two dates to the clipboard, in text format (which is not suitable for loading into a spreadsheet for further manipulation). Still, it does the basic job that I need.

To-Do list

I don’t like bloated Personal Information Manager software — all I want is something that will let me keep an easily-accessible list of to-dos (including recurring ones), with due dates, priorities, and configurable reminders, and Quick To-Do Pro did it for me. I think it’s expensive for what it does (I see it’s now $39.95) but it fitted my needs so perfectly that I grudgingly paid up. Again I couldn’t find anything that worked that well for Linux — so I ended up using KDE’s Personal Information Manager. It’s full of other stuff I don’t want, and the To-Do list is basic, but it’s better than nothing.

Clipboard enhancement

Who can live with a clipboard that only stores the last item you copied? I can’t, and a lot of people obviously feel the same, because there are loads of clipboard enhancers for Windows, most of them suffering from feature bloat. I used a small, free utility called CLCL. The usual cut/copy/paste shortcuts work as expected, but a simple Alt-C in any application pops up a menu of all the items on the clipboard (not just text, but images and objects as well), so you can select the one you want, and you can also set up permanent entries for frequently-used text.

There isn’t so much choice on Linux, but KDE comes with its own utility, Klipper, so that’s what I ended up with. It works, but the clipboard doesn’t always contain what I expect, and there’s no facility for permanent items.

Accented characters

Not essential for everyone, but I do enough typing in French on an English keyboard for it to be an issue for me — there’s no way I want to type some convoluted code or use Windows’ character map every time I want to type a word with an accent in it. At last, this is something that Linux does better than Windows: you can configure a trigger key that works the way the Compose key on old DEC terminals did — press the selected key (e.g. Alt), then type a two-key mnemonic, e.g. typing , and c will give you ç. It’s simple to guess what the mnemonic will be, even for rarely used characters.

Windows doesn’t have anything like this built in, but we have used the free utility Allchars for years — it implements the Compose key functionality, but it also supports macros so you can set up mnemonics for longer pieces of text or key sequences (if you’re not already using CLCL for that …).

I’ve been developing websites with PHP for about 5 years now and I blush to confess that I have only just discovered I can use bind parameters with SQL, instead of painstakingly building up my SQL statements by concatenating strings, while remembering to deal appropriately with all potentially dangerous characters to avoid the risk of crashes or hacks.

Bind parameters are an unreservedly Good Thing. Not only do they make your SQL more readable, maintainable, and portable, but they also protect against the dreaded SQL injection which is the scourge of badly coded PHP websites developed by people who don’t know any better. And as if that wasn’t enough they can improve performance too.

I am no fan of reinventing the wheel, so since I first started using PHP I have used the open-source ADODB database abstraction library for database access. I like it because it’s fast and lightweight compared to its better-known competitor PEAR::DB. Tutorials are thin on the ground though, and I confess I only discovered its support for bind parameters recently, by reading between the lines of the documentation while looking up something else. Since then I’ve been gradually refactoring existing code — whenever I need to edit some code for some other reason I have a look for any embedded SQL and add some data-binding goodness to it. This is simple to do with ADODB. For example:

Before:

$SQL = "SELECT column1, column2, column3 FROM Tablename WHERE column1 = '$myvalue'";
$Result = $DB->GetRow($SQL);

having first carefully cleaned the contents of $myvalue of course!

After:

$SQL = "SELECT column1, column2, column3 FROM Tablename WHERE column1 = ?";
$Result = $DB->GetRow($SQL, array($myvalue));

Note I didn’t need to quote the string in the SQL statement, and the database library deals with any escaping necessary, in the appropriate style for the database being used — no MySQL-specific code here! In addition the SQL statement is pre-compiled — if you are going to execute the same statement a number of times with different values each time (e.g. when doing multiple inserts), this can really boost performance.

So what’s not to like? I don’t know why this technique isn’t better known among PHP developers. For example here is a lengthy thread discussing SQL injection, involving some apparently quite knowledgeable developers, and yet bind parameters are never mentioned; everyone pins their hopes on the stupidly named PHP function mysql_real_escape_string. Perhaps this will change now that support for binding is built into the PDO database access layer in PHP 5. Even if you are still using PHP 4.x you owe it to yourself to try a database abstraction library instead of endlessly hard-coding for one specific database engine.

Of course I should add that none of this means you can dispense with proper data validation before writing it to the database: the golden rule still remains “Never trust user input”!