Chocs To Go is here!

Chocs To Go The goal for students on the intermediate PHP and MySQL course that I’m running for IWA/HWG is to create a working shopping cart. The course starts in only three days, and I decided I’d better walk the walk and prove I could do it. So I have quickly mocked up a working version of the cart they are expected to build and put it online here. I think it will be helpful to students to see what they are aiming at, and it will make it easier for them to work out the logic if they can click through a real example. The design may not win any prizes, but the basics are all there.

All course materials now written and double-checked, quiz questions written and checked, SQL scripts generated and tested … just need to put week one’s materials up now!

Posted in Databases, PHP, Web development | Tagged | Leave a comment

Another handy SQL Server utility

Why doesn’t SQL Server come with a built-in method for generating SQL dump files so you can easily port your data to another database? Microsoft in its own little walled garden I suppose; while the facilities in Enterprise Manager for exporting to Excel, Access, ODBC, CSV etc. can be very useful, sometimes you just need a bunch of SQL statements <whisper>so you can export your data to Oracle</whisper>.

SQLDumper to the rescue; a handy little free utility that does just that, with a neat user interface that lets you select which database/tables you want to dump. Windows only, but hey, it’s a SQL server tool after all! Downside: you have to download and install the .NET framework if you don’t already have it, but the setup program handles that itself — just something to be aware of if you have a slow connection.

Update 2015: SQLDumper no longer exists. But SQL Server Management Studio now does have a means of exporting SQL without using a third-party utility: see this explanation. Or, if that doesn’t do quite what you want (it didn’t for me), try HeidiSQL (open source, donations welcomed). A trial suggests it’s quicker and more flexible.

Posted in Databases, Web development | Tagged , | Leave a comment

Intermediate PHP and MySQL course

Since 1999 I’ve been teaching online for the Open University. It may not pay brilliantly, but it’s regular income and I really enjoy doing it; OU students often overcome major barriers to achieve a qualification they missed out on earlier in life, for whatever reason, so they can be very rewarding to teach. And the fact that it’s online means I can conveniently fit it around other work. Currently I teach three courses in the Certificate in Web Application Development.

I can now announce that I’m also going to start teaching an online course with eClasses.org, the online learning branch of IWA/HWG. The course uses PHP and MySQL to build a shopping cart, and is aimed at intermediate PHP developers (you don’t have to know anything about MySQL, but it certainly helps if you do). I’m really lookng forward to this; you can see the full course description here, and registration is now open.

Posted in Databases, PHP, Web development | Tagged , | Leave a comment

Date handling in PHP and MySQL

It’s always a real headache manipulating dates between PHP and MySQL, because PHP’s date functions assume Unix timestamps, while MySQL has its own internal date format. They both have excellent built-in functions for handling dates, but they are fundamentally incompatible. I’ve torn my hair out before now trying to manipulate dates between the two, because pretty well every application I write needs to do date arithmetic.

Well, duh, I don’t know why I didn’t think of this before, but MySQL has a UNIX_TIMESTAMP function which sorts it all out for you by converting a MySQL date to a timestamp. Simon Willison explains it, so I don’t need to. So easy when you know how!

Posted in Databases, PHP, Web development | Tagged | Leave a comment

SQSH!

About the only reason I ever boot into Windows on the spare PC nowadays is because I need to use Enterprise Manager or Query Analyser to manage a SQL Server database. Today it was inconvenient to reboot, because I was in the middle of doing a lot of printing. So I googled “Linux SQL Server client” and came across sqsh. Installation and set up sounded a wee bit complicated (required compiling from source), but doable, so I put it on … the back burner, with a reminder to myself to do it sometime over the next week or so.

Then later on I happened to be sitting at the Mepis PC, so I pulled up Synaptic and typed sqsh into the search field. Hey presto! Two clicks and it was installed. “Still,” I thought, “I’m bound to have to fiddle about configuring it.”

Undeterred, I typed
sqsh -Umyusername -Pmypassword -Smyservername
at the shell prompt, and a sqsh prompt appeared. If Linux doesn’t give you an error message then nothing has gone wrong. So:
use mydatabasename
go

Still no error!
SELECT * FROM mylittletable
and there were the results! Fantastic — if possibly not all that secure when used remotely :-) Highly recommended if you need occasional access to a SQL Server or Sybase database from Linux.

Posted in Databases | Tagged , | Leave a comment

Downright Slippery Time

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

Posted in PHP, Web development | Leave a comment

Networked file sharing between Windows and Linux

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.

Posted in Linux | Tagged , , | Leave a comment

How to install ODBC on Linux

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 and 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 …

Posted in Databases, Linux | Tagged | Leave a comment

Scriptaculous without Javascript

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 …

Posted in Javascript, PHP | Tagged | Leave a comment

Neat tricks with Oracle

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:

ID Project Name Parent ID
1 The Mega Project
2 First Sub-Project 1
3 Second Sub-Project 1
4 Another Sub-Project 2

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!

Posted in Databases, Javascript, PHP, Web development | Tagged , | Leave a comment