PHP Data Driven Table Maintainer
Copyright (C) 2002 by Steve Litt
Contents
Introduction
This article was inspired by Brian Ashe's PHP presentation given at Linux
Enthusiasts and Professionals. Brian showed off an almost entirely data driven
app. I made it a little more data driven, and here it is.
PHP has so many database functions that you can retrieve almost any database
information or meta information, including column names. Thus, given a table
name and the database connection information, you can write a function to
list all the table's records, sorted by any field. You change the sort by
clicking on the column's heading. Each row has links to add, change or delete.
The first version uses links because they're easier. Then we'll make a more
practical version using a form with buttons and POST page to page communication
suitable for maintaining state information.
This entire tutorial uses the PostgreSQL DBMS. PostgreSQL (often called Postgres)
is a professional grade, full featured DBMS that ships with most Linux distributions,
and is freely available. It was chosen because of its completeness, and the
fact that you will not need to spend any money to obtain it.
Because PHP so thoroughly abstracts database functionality, changing the
DBMS would be primarily a task of changing PHP database access function names
from a prefix of pg_ to the prefix of the desired DBMS, plus changing
the connect and exec calls slightly.
This document assumes you have computer programming knowledge and that you
know your way around Linux and UNIX.
Verifying Your PHP Installation
Find a directory that can be viewed via the http protocol with a browser.
In other words, it must be viewable as http://whatever.com/dirname/,
not file:///dir1/dir2/dirname/. Within that directory, create the
following test_install.php:
Once saved, view it with a browser, using the proper http url. The result
should look like this:
If you got the preceding screen, you know that PHP is installed, running and
working. If not, troubleshoot. First, find out if you can access ANYTHING
in that directory with an http url. In the same directory, create the following
file, called test.htm:
<html><head></head><body>
Hello World
</body></html>
|
Access that file with http://mydomain/mydir/test.htm, where mydomain
is the domain name mapping to the directory you're working in, or even the
IP address mapping to that directory, and mydir is the directory path from
the document root of that name or IP containing the test.htm file.
If that doesn't work, try adding doctype information:
<!DOCTYPE doctype PUBLIC "-//w3c//dtd html 4.0 transitional//en">
<html><head></head><body>
Hello World
</body></html>
|
If neither of those work, your problem is a basic http problem, not necessarily
a PHP problem. If you can access test.htmfrom a browser using a
file:/// protocol, you have a basic problem accessing that directory with
your web server. Whatever, fix your http problem.
Once your browser can access test.htm with an http URL, try test_install.php
again. If it still doesn't work, troubleshoot your PHP installation, and make
sure PHP is installed as an Apache module (assuming you're using Apache as
your web server).
Once you have test_install.php running, you can learn from its output...
Gaining System Info from
phpinfo
The Apache information is especially important. Scroll down about half way
in order to see it. The following screenshot shows the Apache info:
First notice the User/Group row. In this case the username is apache.
This is very important, because this is the user you must use to connect to
the database. Also notice the loaded modules include mod_php4. If
that module were not included, you would have a very hard time performing
these exercises, as you'd need to use PHP as a CGI tool, and that's much harder,
less secure, and more brittle.
Creating a Test Database
Before you can work with PHP's database capabilities you must create a test
database with a table. While it's theoretically possible to have a PHP page
create the table, why would you do that? In real life, the last thing you'd
want your web app doing is changing the structure of a database, including
the addition of tables. Assuming you have telnet or ssh connectivity to your
web host (and for a data enabled app that's HIGHLY desirable), it's much better
to do this work at the command line.
Before you can create the database, it must be verified that PostgreSQL is
running and properly configured. If you have the root password on the server,
or if your system administrator gives you the password for user postgres,
you can do it yourself. Otherwise your system administrator must verify that
PostgreSQL is properly set up. The basic procedure is:
- Verify that postmaster is running using ps ax | grep postmaster
- Obtain the password for user postgres by running passwd
as root or obtaining it from your sysadmin
- Log in as user postgres, or su - postgres
- Verify the home directory for user postgres (usually /var/lib/pgsql)
- Verify the $PGDATA and $PGLIB environment variables
by echoing them. Typically (but check with your documentation or sysadmin),
$PGLIB should be set to user postres home directory,
while $PGDATA should be set to the data directory below
user posgres home directory.
- If the preceding two environment variables are not set, or if
they're set wrong (check with your documentation or sysadmin to make sure),
correct their settings within the bash_profile script in user postgres
home directory.
- AS USER postgres, Initialize the PostgreSQL database with
the initdb command.
Next you must actually create the database. As user postgres you must perform
the following command:
createdb mydb
If you don't have the password for user postgres, ask your system administrator
to do it. If he or she won't, ask him or her for an alternative tutorial.
If it isn't given, get yourself a Linux box so you can perform this material
unencumbered.
Before going on, this is a good time to enable the PL/pgSQL language. PL/pgSQL
is PostgreSQL's language for constructing triggers and stored procedures
(functions), and if you do not enable it, you'll get a "Unrecognized language
specified in a CREATE FUNCTION: 'plpgsql'." error when trying to install
PostgreSQL functions. To enable PL/pgSQL, perform the following command,
as user postgres, from the command prompt:
createlang plpgsql mydb
As you can see from the preceding command, you must enable PL/pgSQL for each
database created.
Next you must set up the database and a single table (mytable).
This can be done as user postgres, or, if your system administration
has granted PostgreSQL administration priveleges to your logon, as yourself.
Here's what you do:
First run the following command:
psql mydb
If it gripes the createdb probably failed. Verify this by trying psgl
template1, as the template1 database typically ships pre-created.
DO NOT CHANGE ANYTHING IN THE template1 database. Once you can open
the database in psql you'll see something like the following:
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
mydb=#
|
The pound sign at the bottom is the prompt, and the various backslash commands
tell you how to operate within the psql environment.
At the prompt, create your new table with the following command:
create table people (pid integer, lastname char(16), firstname char(12), email char(40));
Then insert a row with this command:
insert into people (pid, lastname, firstname, email) values (1, 'Stallman', 'Richard', 'rms@killersoftware.cxm');
Insert these two other rows:
insert into people (pid, lastname, firstname, email) values (2, 'Torvalds', 'Linus', 'linus@windowscompetition.cxm');
insert into people (pid, lastname, firstname, email) values (3, 'Allison', 'Jeremy', 'jeremy@bestserver.cxm');
Now view the results:
select * from people;
The select statement should yield results something like this:
mydb=# select * from people;
pid | lastname | firstname | email
-----+------------------+--------------+------------------------------------------
1 | Stallman | Richard | rms@killersoftware.cxm
2 | Torvalds | Linus | linus@windowscompetition.cxm
3 | Allison | Jeremy | jeremy@bestserver.cxm
(3 rows)
mydb=#
|
Once you have a result like the preceding, you're ready to do a little PHP
database work
Connecting to the Database
Before you can connect, you must find out which user Apache uses. There are
2 ways to find out. The easy way is to look at the User/Group line in the
Apache part of the phpinfo() command you used in the Gaining System Info from phpinfo
article. Another way is to create the following checkuser.php:
<?php print "User is:" . `whoami` . "\n" ?>
|
And then run it from a browser. The result will be the username.
Once you have that username, you must enable that user to access the people
table you made earlier. As user postgres or any other user with
admin rights in PostgreSQL, run the psql program:
psql mydb
Assuming that user is apache, within the psql environment
run the following command:
grant all on table people to apache;
This gives user apache the priveleges necessary to select, insert,
update, delete, rule, access references, or triggers. Later you can
cut back on the priveleges and give them on a "need to know" basis, but for
now, make it easy on yourself by granting all. Naturally, if Apache runs as
a user other than apache, grant that user all priveleges.
Now you're ready to make the basic connection. Assuming user apache,
and assuming the typical PHP port of 5432, create the following view.php:
<?php
$connection = pg_Connect ("dbname=mydb port=5432 user=apache");
if($connection == 0)
{
die("Connection failed\n");
}
else
{
echo "<p>Connection succeeded</p>\n";
}
?>
|
If you get a "Connection failed" notice, check the pg_Connect call's
arguments, and troubleshoot. When you get a "Connection succeeded" notice,
you're connected to the database and it's time to actually view the data.
Obtaining Column Headings
Now that you can connect to the database, you'll use the connection to deduce
the column names for the table and display them in the top row of a table.
This is the crux of the data driven app.
Expand your view.php so it looks like the following, in which the
newly added code is red:
<?php
$connection = pg_Connect ("dbname=mydb port=5432 user=apache");
if($connection == 0)
{
die("Connection failed\n");
}
else
{
echo "<p>Connection succeeded</p>\n";
}
$sql = "SELECT * FROM people";
$result = pg_Exec($connection, $sql);
$numfields = pg_numfields($result);
$htmltablewidth=$numfields+1;
echo "<html><head></head><body>\n";
echo "<table border='1' cellpadding='5'>";
echo "<tr><td colspan=$htmltablewidth><center><b>$sql</b></center></td></tr>\n";
echo "<tr bgcolor='#CCCCCC'>";
echo "<td><font size='+1'><b>Action</b></font></td>\n";
for($fieldnum=0; $fieldnum < $numfields; $fieldnum++)
{
echo "<td>";
echo "<font size='+1'><b>";
echo pg_fieldname($result, $fieldnum);
echo "</b></font>";
echo "</td>\n";
}
echo "</tr></table>\n";
echo "</body></html>";
?>
|
Let's examine the new code in red. We start by defining a SQL statement with
which to retrieve data, and then retrieve the entire data set with the pg_Exec()
call, placing the data set in the $result variable.
Armed with $result, we use pg_numfiles() to acquire the
number of fields in the table, and then add one to calculate the width of
the HTML table, because the first column of the HTML table will eventually
contain links or buttons to delete or change the row.
Now we construct the table, and make the first row a single column spanning
5 normal columns. This row is a table heading containing the SQL statement
whose data it displays.
Then we construct the second row, with a column for actions, and then a column
for each database column. Through the magic of the pg_fieldname()
function, we can deduce the field names directly from the database. We have
a loop to create an HTML column for each database column.
Full Data Listing
The preceding article constructed the HTML table heading. This article adds
the actual table data. Once again, the additions to view.php are in red:
<?php
$connection = pg_Connect ("dbname=mydb port=5432 user=apache");
if($connection == 0)
{
die("Connection failed\n");
}
else
{
echo "<p>Connection succeeded</p>\n";
}
$sql = "SELECT * FROM people";
$result = pg_Exec($connection, $sql);
$numfields = pg_numfields($result);
$htmltablewidth=$numfields+1;
echo "<html><head></head><body>\n";
echo "<table border='1' cellpadding='5'>";
echo "<tr><td colspan=$htmltablewidth><center><b>$sql</b></center></td></tr>\n";
echo "<tr bgcolor='#CCCCCC'>";
echo "<td><font size='+1'><b>Action</b></font></td>\n";
for($fieldnum=0; $fieldnum < $numfields; $fieldnum++)
{
echo "<td>";
echo "<font size='+1'><b>";
echo pg_fieldname($result, $fieldnum);
echo "</b></font>";
echo "</td>\n";
}
$numrows = pg_numrows($result);
for($rownum=0; $rownum < $numrows; $rownum++)
{
$row = pg_fetch_row($result, $rownum);
echo "<tr>";
echo "<td>Reserved</td>\n";
for($fieldnum=0; $fieldnum < $numfields; $fieldnum++)
{
echo "<td>";
echo $row[$fieldnum];
echo "</td>\n";
}
echo "</tr>\n";
}
echo "</tr></table>\n";
echo "</body></html>";
?>
|
As you can see, the new code is a doubly nested loop that lists the data for
each column in each row. Based on the SQL statement contained in the$sql
variable, this script shows all data. But that's all it does.
We can have this script do much more, including custom sorting of the data,
and using it as a add/change/delete platform.
Subroutining the App
In order to have this table maintenance app be as self-contained as possible,
let's make it all one file. That's not to say it's a single web page -- it
can deliver one of many apps depending on arguments, which are delivered
via the URL (GET method). The first step is to encapsulate the listing function
into a subroutine. See the following tablemaint.php file:
<?php
function makeConnection($dbName)
{
$connection = pg_Connect ("dbname=$dbName port=5432 user=apache");
if($connection == 0)
{
die("Connection failed\n");
}
return($connection);
}
function listTable($connection, $tableName, $sortField)
{
$sql = "SELECT * FROM $tableName order by $sortField";
$result = pg_Exec($connection, $sql);
$numfields = pg_numfields($result);
$htmltablewidth=$numfields+1;
echo "<html><head></head><body>\n";
echo "<table border='1' cellpadding='5'>";
echo "<tr><td colspan=$htmltablewidth><center><b>$sql</b></center></td></tr>\n";
echo "<tr bgcolor='#CCCCCC'>";
echo "<td><font size='+1'><b>Action</b></font></td>\n";
for($fieldnum=0; $fieldnum < $numfields; $fieldnum++)
{
echo "<td>";
echo "<font size='+1'><b>";
echo pg_fieldname($result, $fieldnum);
echo "</b></font>";
echo "</td>\n";
}
$numrows = pg_numrows($result);
for($rownum=0; $rownum < $numrows; $rownum++)
{
$row = pg_fetch_row($result, $rownum);
echo "<tr>";
echo "<td>Reserved</td>\n";
for($fieldnum=0; $fieldnum < $numfields; $fieldnum++)
{
echo "<td>";
echo $row[$fieldnum];
echo "</td>\n";
}
echo "</tr>\n";
}
echo "</tr></table>\n";
echo "</body></html>";
}
$connection = makeConnection('mydb');
listTable($connection, 'people', 'lastname');
pg_close($connection);
?>
|
As you can see, the preceding code is pretty much a rewrite of the view.php
code (with the addition of a provision for sort order). However, the subroutine
encapsulation provides the hooks to convert this little script into a table
maintenance program.
Our next step is to define several states and create stub functions for each.
Challenges of a Full
Featured Data Manipulator
There are several challenges in creating a full featured data manipulator
script. Most of the challenges revolve around the fact that HTTP is a stateless
protocol. Statelessness means that when you transition from one web page to
another, all variable values are forgotten. This is true whether the page
is static or rendered by a script. Any variables -- any values, are forgotten.
As a programmer, a good way of envisioning this challenge is to imagine a
programming language without global variables. Any variable set in one web
page is out of scope in another.
There are three basic ways to retain variables and values between pages:
- Cookies
- Passing the values
- Database lookup
If you're interested in a detailed analysis of achieving statefulness in
a web app, see the PHP Data Persistence page.
[ Troubleshooters.com| Code Corner | Email Steve Litt ]
Copyright
(C)2002 by Steve Litt --Legal