PHP for Domino Developers, Part 1, MySQL

Jake Howlett, 2 October 2003

Category: LAMP; Keywords: LAMP PHP MySQL database

Whether you like it or not you are going to have to entertain the idea that Domino may not be around for ever. What will become of it in the years to come is anybody's guess. Will it survive, but with a DB2 backend? Will it get swallowed up by the migty, yet cumbersome, WebSphere? These are questions I get asked all the time. So, for the record, let me say I have no idea whatsoever. Either way it goes without saying that we all need to take this in to account when we consider our future career plans. Personally, I have been spending quite some time trying to expand my skillset. In particular I have been getting to grips with the LAMP platform - Linux, Apache, MySQL and PHP.

Whilst I am aware that this probably isn't the best of career moves, financially speaking, there is something else that drives me. It is easy and free to setup a LAMP environment in your own home. All you need is a spare machine that you can install the likes of Red Hat Linux on. If you can't do this then there's a myriad of internet hosts out there that offer hosting packages at next-to-nothing prices. My favourite being Easily's Virtual Hosting package. Either way, it's a lot easier than trying to get yourself a working install of something like WebSphere.

Once you have yourself a working LAMP setup you can start to learn about a whole new side to web development. As Domino developers we have it easy. We don't need to worry about how data is stored and how we handle this data when a form is submitted over the web. It's all pretty much done for us. I would even go as far as to say my dad could create a fully-functioning website with Domino. Now, don't get me wrong, I think this is a great thing. What worries me is that when you step outside of Domino development you may be in for a shock. It's no longer simply a case of adding fields to a form, adding a submit button and then creating a view. Most other systems will, without doubt, use a relational database to store the submitted information. You need to plan, design and create these databases. You then need to actually program forms and buttons and write the routines to enter the data to the backend and to retrieve it for web presentation.

It might sound like a lot of hard work but it can also be extremely rewarding. When you are completely in control of all the logic you can do whatever you like. Domino does a lot of things and it does them well. However, there are things it can't do. There are also things it doesn't do so well and the control over which you have absolutely no power. Welcome to LAMP, where anything is possible.

This is the first of a series of articles I plan to write that will introduce this other side to web development. What's important is that I will approach it from the perspective of a Domino developer. Starting with this article that will try and describe how you take the first step of creating a database. Followed by articles that will discuss how to replicate the features we are so used to using - forms, subforms, views, ACLs, authentication, full-text searching, agents etc. Needless to say we've had it easy as Domino developers.

Assumptions:

First things first. This article is not going to tell you how to setup and install Linux. Nor will it tell you how to configure LAMP on the machine. If you install Red Hat 9 with the web server option and make sure you select the right components you should be all set. If you have any trouble there are loads of articles already out there and loads of forums where you can get any question at all answered. Alternatively just get yourself some cheap hosting.

The L of LAMP is obviously for Linux. Although you can easily add the combination of MySQL and PHP to a Windows machine, I will assume you are doing so on Linux. I will also assume that you did this on a remote machine and you will be developing on a separate, Windows, PC. I will also assume you know a little bit about SQL. Enough to feel comfortable with the ideas of databases, tables and datatypes.

It might sound like more hassle than it's worth, using Linux. The reason I favour this approach is that I think it's most likely what I would encounter in the real-world. Consider it best-practice. There's no harm in having your own Linux machine where you can get to grips to what is the most common OS of all web servers.

Just remember. If I can do it then there's no reason why any of you guys can't.

Getting started:

When developing your application you will need to interact with the MySQL server. Instead of logging on to the Linux server and issuing MySQL commands at the console I would recommend you download the MySQL Control Center. You can use this to write your own SQL commands or perform standard operations like creating databases, users and tables without needing to access the server.

Before you can start using it you will need to add your Windows machine to the list of hosts allowed to access the server. This does require you to login to the server. Laucn a Terminal window and, at the command line, type "mysql" before pressing enter. This will launch the MySQL Monitor where you can perform SQL operations on the backend databases. In this case we need to issue a GRANT statement to add a new user. We will give full privileges to a user called "root" from any machine ('%') to the all databases. The command is:

GRANT ALL ON *.* TO 'root'@'%';

Here's what it looks like on the server. Don't be worried about the message telling us no rows were altered. This is normal.

MySQL command line

The above command is for a super-user known as root and with no password. If you want to be a little more cautious and add a normal user with a password, whilst limiting the operations they can perform and on which database, you would use something like:

GRANT SELECT,INSERT,UPDATE,DELETE ON codestore.* TO 'jake'@'192.168.0.6' INDENTIFIED BY 'password';

Once done, you can check the user(s) is added using some basic SQL commands. Still at the MySQL command, switch to the "mysql" database by typing "use mysql;". Then type "SELECT * FROM user;". You should see a table of data. In there is the user you just added.

Note: Don't equate this step with ACLs and/or Domino Directory users. There is an extra level required to manage users of your website that we will get to much later.

This should be all you need to do on the server. Log out and return to your Windows PC. Install and launch the MySQL Control Center. When it's loaded click on the New Connection icon and enter the details in to the boxes, like below:

MySQL Control Center - new connection

In this case I am connecting to a server known as "epswebsvr01" and as the super-user we created called root, who has no password. Press the Add button once you're done and it will return you to the Console. Double-click on the new connection on the pane in the left. If all has gone well you will see a list of databases, as below. Double click a database and you will connect to it. In the shot below I am looking at the tables in the mysql database. Notice the table called "user" that we were just looking at. If you double-clicked this table you will also get see all the rows in the table.

MySQL Control Center - listing table

Before you carry on reading this, you might want to spend a few minutes getting to know MySQL CC. It's going to be your friend!

Creating a Database:

What's the first thing you do when you start a new Domino site? Create a new database! It couldn't be easier really could it. Simply click File - Database - New from the Domino Designer menu. Depending on how you choose to do it, creating a database with MySQL isn't much more complicated. You can do it with a simple SQL CREATE command:

CREATE DATABASE codestore;

Or, from the Control Center you can simply create a new database from the Actions menu:

MySQL Control Center - new database

Simple as that. Nothing there yet though. Just an empty database with nothing in it. No tables, no nothing. We will get to that in a future article.

Summary:

Ok, so this was supposed to introduce you to PHP and I've not even mentioned it yet. Hold your horses. One step at a time. Before we get started with PHP you have to have a working install of MySQL. Hopefully, having followed all the steps above, you now have one. In the next couple of articles I will introduce PHP coding to you and then build on this foundation and look at how we create a "form" in PHP. For this we will be creating both tables with SQL and pages with PHP.