This article is the fourth part of a series of articles that aim to introduce the LAMP platform to Domino Developers. In the first three articles I've concentrated mainly on the MySQL database side of things. Now it's time to get our hands dirty and start doing some PHP coding. If you're reading this article and haven't read the first three already, you might want to. If however, you feel comfortable with RDBMS systems then you only need to download the table definition so that you can follow along with this article.

In this article we will be adding some dummy-data to our table and see how we go about retrieving this data and presenting it to the browser. It might seem like we're doing things the wrong way round. Should we not look at forms before we look at views? Well, yes and no. If you want to learn SQL you will almost always start by learning how to query data in a database before you look at how to add or update that data. There are other reasons it makes sense to do it this way round. It's an gentler approach to both PHP and SQL. It also give me more space in the article to introduce database connection methods and still have room to talk about some Apache configuration settings that are required.

From Notes Views to PHP views:

As a Domino Developer you've had it easy this far. Creating a view, like the one below from our journal.nsf, is a piece of cake. Not only is it easy but you end up with a view that can present your documents to both the Notes client and to the Web browser as HTML. Notes Views can be a good thing. However, the easiest way is definitely not always the best way!



Notes views may well be easy but they give web developers very little say over how they behave and often they just don't. Behave, that is. We have to resort to tricks and hacks to get our sites acting as we want them to. The one thing I always do with a Notes view is turn on "Treat contents as HTML", create HTML in each of the columns and then nest it on a form between open and close <table> or <div> tags. This lends us some flexibility but there are still ways in which we remain at the mercy of the Domino server.

With PHP, having to create the HTML yourself is a given. What may seem like a pain and some extra work is in fact a highly refreshing approach. Instead of a view that does what Domino wants it to, we have some HTML that does whatever we want it to. The possibilities are endless.

As an example of something Domino can't do that PHP can, imagine this. You want users to navigate a view using links at the bottom. Next Page, Previous Page kind of thing. In Domino you can add them using some simple @Commands. What you can't do is tell whether you are at the beginning or end of the view and hence if there is in fact a previous or next page to go to. With PHP this is a doddle. You know what page number you're on, how many documents are on each page and, most importantly, how many documents are in the table (i.e. view) in total. Your user won't click on a next page link on to be told there were "No Documents Found".

Another drawback to the Notes View is that there are never enough of them. Every user always seems to want their own personal view of things. Databases can end up with dozens and dozens of views, each requiring an index, which leads to an increased file size and a decrease in performance. With PHP, assuming a well designed backend database, this should never be the case. From our PHP scripts we can tailor our SQL queries to the strangest of user requests. Creating a new query doesn't alter the size or performance of the database. In effect we can have as many "views" of the data as we like.

Anyway, that's enough of that. Without arguing over which is the better technology any further, let's get in to some code. First things first, we need to connect our PHP code to the MySQL database.

Connecting to the database:

First thing you need to do in a PHP page that connects to a backend database is initialise the connections. You need to do this on all these pages, every time they run. The code to do it is:

<?php
$server_name = "localhost";
$user_name = "root";
$password = "";
$database = "journal";
$connection = mysql_connect( $server_name, $user_name, $password ) or die ( "Can't connect to the MySQL Server" );
mysql_select_db( $database, $connection ) or die ( "Can't access the MySQL Database" );
?>

This code starts by defining some variables (anything beginning with $) to hold the values required for a successful connection. What these are should be fairly obvious from the names I've given them. The end result after the two function calls is that the rest of our PHP script remains connected to this database. From anywhere else in the script we can call functions that query this database without having to reconnect.

The username and password above aren't the same as having an average user log in. This is the super user that we talked about in the first article. For this reason it's important to keep these details top secret. To do this we add them in to a separate file, all of their own, and call it db.inc. This has the added benefit of keeping the variables in one place. Should the password need to change, you only have to do it in one file. When you want to have a PHP page with a database connection, you simply add the following line at the beginning:

<?php include "db.inc"; ?>

In itself, giving the file the name "db.inc" is not making the code secure. We need to tell Apache to never let a browser have access to this or other files ending in .inc. How we do this is covered in the section called "Configuring Apache" later in this article.

Note: The above code includes the "die" method. If any of the database connection calls should fail then the script exits, presenting the browser with one the messages. Not pretty but there shouldn't be reason for this to happen unless MySQL crashes, the super-user account details changes or the database is dropped.

Presenting the view:

Ok, time for some real code. Without further ado, here's a simple code loop that create a table, as we would expect a view to appear:

<?
$entries=mysql_query("SELECT * FROM entries") or die ("Error querying journal entries");

echo "<table width=\"100%\">\n";
echo "<tr><td>Subject</td><td>Category</td><td>Created</td></tr>\n";
while ( $rows = mysql_fetch_array($entries) ) {
echo "<tr>";
echo "<td>".$rows[ 'subject' ]."</td>";
echo "<td>".$rows[ 'category' ]."</td>";
echo "<td>".date("F d, Y ",strtotime($rows[ 'time_created' ]))."</td>";
echo "</tr>\n";
}
echo "</table>";
?>

First thing the code does is issue an SQL query that returns all the rows in the "entries" table. This is as simple as using the mysql_query() function. Remember that we're already connected! We then start creating the table using the "echo" function. Think of this like you would "print" in LotusScript agents. It does nothing more than return text as part of the response to the browser. With the table already started, we add a row for each entry in the table. We can do this because the mysql_fetch_array() call will keep on returning an array of column values for each entry until there are none left. Once all rows have been printed out we simply close the table and the script is done.

On their own, these two bits of script don't constitute a page as such. All we've generated is a <table> element in HTML. What we need to do is mix these scripts in to some ordinary HTML. Something like this:

<!-- Database connection script goes here -->

<html>
<head>
<title>A simple PHP view</title>
</head>
<body>
<h1>All Journal Entries</h1>
<!-- Create table script goes here -->
</body>
</html>

You can download the whole PHP page here. Add this and an edited copy of the db.inc file to the same directory on your LAMP server. Add the dummy-data from this SQL dump file and open the PHP page. All being well you should have a nice table of all the Journal entries.

The .sql file I included does two things. First it drops (i.e. deletes) the table called "entries" if it's already there and then recreates it. Next it inserts 4 simple entries in to the table. If you use this file, be aware that you will lose any data you had previously added to the table.

We've kind of dived straight in to PHP here. There's not really the time to go over the finer details of the language. Instead, I will pass on little bits and bobs as and when they crop up. If you want to get to know PHP in more detail I highly recommend O'Reilly's Web Database Application with PHP & MySQL as a starting point.

An important coding concept used in the above snippet is string concatenation using the "period". You add one string to another like so: "Hello ".$name.", how are you?". There are other ways but this is the one I will be mainly using. That's enough PHP for the time being anyway.

Enhancing the View:

It's probably obvious that this isn't all the code required for a fully-functioning web view. There is one glaring omission - navigation. The above SQL returns ALL the rows in the table. What if there were 10,000? We get one very big page! What we need to do is split them down. It's quite easily done but I wanted to keep the first articles on PHP quite simple before I move on to "advanced PHP". So I've chosen to leave it out for now.

Say we did want to add it, the techniques are quite simple. First we could add a LIMIT to the SQL query, like so.

SELECT * FROM entries LIMIT 30

This would only return the first 30 rows of the view. To make it more advanced we could pass URL parameters to the page. We could then build our SQL query dynamically. Imagine using a URL such as /simple-view.php?start=60&count=30. From this we can create the following query which skips the first 60 documents and return rows 60 to 90:

SELECT * FROM entries LIMIT 60, 30

Using these technique we can add the Previous and Next page links we talked about, at the bottom of the "view". Also, knowing the total number of rows in the table and using some simple maths, we can even work out exactly how many pages there are in total and how many links to display. This kind of navigation is what you're probably used to seeing on sites nowadays and have always dreamt of adding to Domino sites.

Configuring Apache:

Earlier I mentioned that we needed to secure the server and prevent access to the db.inc file. To do this we configure Apache not to allow access to any file ending in .inc. This means that any request on port 80 to the web server for a .inc file is rejected. It doesn't however mean that the PHP engine can't access it. It can, and that should be all that's necessary.

If you've followed my lead you have yourself your very own Linux server. If this is the case then you can configure Apache via its main httpd.conf file. However, most likely is that you have bought some space on a web server, with your own directory, and no access to this httpd.conf file. Fear not. The web host has probably enabled the setting called AllowOverride. If they have then you can add your own miniature conf files to your own directory. The settings you add to them will then apply to that directory and all its sub-directories. The file you need to add is called .htaccess and should be a plain text file.

Note: Files beginning with a . are hidden in Unix systems. You might be confused if you FTP this file to the server and it looks like it isn't there. It probably is but the FTP server doesn't show it to your FTP client.

In the case of the Journal application I added the following settings to the .htaccess file in the root of the /journal directory. You can download a copy of the file here.

ErrorDocument 403 /codestore/apps/journal/errs/403.php
ErrorDocument 404 /codestore/apps/journal/errs/404.php

DirectoryIndex entries.php

<Files ~ "\.inc$">
 Order allow,deny
 Deny from all
</Files>

The first two lines are simply to tell the server what page to deliver to the user when they encounter either a 403 or a 404 error. This is like adding $$ReturnAuthorizationFailure and $$ReturnGeneralError forms to a Domino database.

The third line tells Apache what file to use as the directory's index page. By default if a user accesses a directory from a URL and doesn't enter the name of the file the server looks for files like index.html, default.htm, index.php etc. In our case we only have two files and no index file. So, we can tell the server to use the entries.php file as the default index page. Now, if a user accesses the URL /journal/ and misses out the file name, Apache will deliver the entries "view".

The last part of the file is to block access to the db.inc file that contains our MySQL passwords. I won't go in to detail of how it works, although it's not that complex really. This part of the config causes a 403 (Forbidden) HTTP status error whenever a uses tries to access this file via a URL. Because of the earlier ErrorDocument settings in our .htaccess file, this means the user won't see the password but will see the 403.php page instead.

You can do some really powerful stuff with the directives available to you for use in these config files. For example, you can force certain file types to be treated as PHP files. Consider this line:

AddType application/x-httpd-php .nsf

This tells Apache that any files ending in .nsf are in fact PHP files. Using this we can rename our PHP pages to end in .nsf and fool people in to thinking they were looking at a Domino database. Here's an example.

Another trick is to remove the file extension altogether. Using the setting below we can remove the extensions of both our files and still make Apache treat them as PHP:

<Files ~ ( entries | entry )>
 ForceType application/x-httpd-php
</Files>

This is useful if you don't really want to tell users what technology you happen to be using. You could use the same technique to end the files in .html, .asp, .jake or whatever you like.

Note: Allowing and using .htaccess files on your Apache server is a performance hit. They work well because changes to the configuration don't require a restart of the server. However, it does mean that every time a page is requested the server has to look for and read this file.

Summary:

Hopefully your introduction to PHP coding has been quite easy-going so far. By leaving the Forms part of the equation until after the Views I think we've done ourselves a favour. What we should have by now is enough confidence to create and play with PHP a little more. The next article moves on to replacing Notes Forms. This is where things start to get a little more complicated. We really have got it easy as Domino Developers.

Although the view we've created is a simple table with no links or navigation, the site we are working toward will be a little more advances. As we progress it will get more and more advanced. If you want to see what we are aiming toward there's a working copy here. Which, in turn, is a copy of this notes template. Notice the lack of framesets and Java Applets in the version we are working toward ;o)