"Linux Gazette...making Linux just a little more fun!"

---

Web Applications with Database Connectivity

- getting started -

By Marius Andreiana

---

Abstract

Almost any company with a presence on the web uses some kind of database to ease the content maintaining. This article will introduce a way to develop database-backed web sites using open-source software : Linux, Apache, PostgreSQL, PHP3.

Although this is written for a Red Hat Linux system, most of it ( if not all ) applies to other distributions.

Contents

• Linux
• Apache
• PostgreSQL
• PHP3
• Putting it all together
• Resources

Linux

We all know about Linux... You could also check my Introduction to Linux

Apache

Apache is the most used web server on the internet. Originally based on NCSA, it evolved into a far superior system which can rival (and probably surpass) almost any other UNIX based HTTP server in terms of functionality, efficiency and speed. Apache is an entirely volunteer effort, completely funded by its members, not by commercial sales.

Your Linux distribution includes Apache. Install it ( if it isn't already installed ) and start the HTTP daemon. It's a very simple installation, so it won't be covered here. Test it by typing in your web browser : http://localhost/. You should see a page which tells you that the installation was successful.

PostgreSQL

PostgreSQL is an advanced Object-Relational database management system (DBMS) that supports almost all SQL constructs (including transactions, subselects and user-defined types and functions). Postgres, developed originally in the UC Berkeley Computer Science Department, pioneered many of the object-relational concepts now becoming available in some commercial databases. It provides SQL92/SQL3 language support, transaction integrity, and type extensibility. PostgreSQL is a public-domain, open source descendant of this original Berkeley code.

The easiest way to set up PostgreSQL is from RedHat's 6.1 RPMs. Install everything beginning with postgres.

I recommend not to store your database files in the default location, /var/lib/pgsql/, but in /home/postgresdb ( or somewhere in /usr/local ). That way, if you have to reinstall the system, you woun't lose the data ( you do have a separate partition for /home, don't you ? ).

So how do you do this ? After installing the RPMs, user postgres is the database administrator ( not root !). Logged in as root, set up a password for postgres :

# passwd postgres

Next, create the directory and change it's owner to be the PostgreSQL administrator :

# mkdir /home/postgresdb
# chown postgres.postgres /home/postgresdb

The location of database files is specified by PGDATA environment variable. You should set this up so it's available to all users. Add this at the end of /etc/profile file :

PGDATA=/home/postgresdb
PGLIB=/usr/lib/pgsql

export PGDATA PGLIB

In order to use the database, the postmaster must be running. You'll probably want it to run all the time, so let's add it to the start-up daemons :

# setup

Select Sytem services, then make sure postgresql is marked with a *.

Okay, one more thing. We must modify in /etc/rc.d/init.d/postgresql ( which is run at start-up ) the database directory. Replace every occurence of /var/lib/pgsql with /home/postgresdb

Now you are ready to go. Start the postgresql service :

# /etc/rc.d/init.d/postgresql start

Being the first time it is run, the base database files will be created. Then the postmaster will be started ( by default, TCP/IP communication is enabled ).

It's time to test the instalation. Create a user pguser :

# adduser pguser
# passwd pguser

Logged in as postgres, create the same user for PostgreSQL :

$ createuser pguser

Answer to the questions by not allowing this user to create databases, don't let it be a superuser and create a database for him.

Log in as pguser and run psql. Create a table and insert a few records :

pguser=> create table friends ( name varchar(30), email varchar(90) );
pguser=> insert into friends values ( 'John', 'john@johnny.com' );
pguser=> insert into friends values ( 'Mary', 'mary@linuxmail.com' );
pguser=> select * from friends;

You could do this from PgAccess ( included in Postgres ) if you want a graphical user interface, but for now try the command line ( and don't forget the semi-colon at the end ).

Time to move forward.

PHP3

PHP is a server-side HTML-embedded scripting language with a strong database integration layer. Writing a database-enabled web page is incredibly simple. Supported databases include PostgreSQL, MySQL, Oracle, Sybase, Informix.

Again, the easiest way to set up PHP is from RedHat's 6.1 RPMs. Install php, php-manual, php-pgsql.

Now let's tell Apache about PHP. Uncomment the follwing lines in /etc/httpd/conf/httpd.conf

LoadModule php3_module modules/libphp3.so
AddModule mod_php3.c

and /etc/httpd/conf/srm.conf

AddType application/x-httpd-php3 .php3
AddType application/x-httpd-php3-source .phps

You could also add to DirectoryIndex index.php3 ( in srm.conf )

Choose a place which will hold our work. For start you could use /home/httpd/html/ ( if that's your DocumentRoot defined in srm.conf ).

Let's test with a simple document named test.php3 ( the extension is very important ) with a single line : <?php phpinfo() ?>

Access this file from your web browser from http://localhost/test.php3
You should see lots of information about PHP, your server ...

Putting it all together

Everything is set, let's get some results !

Our friends John and Mary are still there, waiting for you. Create a file named friends.php3. Here it is, with lots of comments.

<?php

//define variables used with the database
//usually this goes in an include file
$PG_HOST= "localhost";
$PG_PORT=5432;
$PG_DATABASE= "pguser";
$PG_USER= "pguser";

$PG_PASS= "";

//let's open the database
$dbconn=pg_connect(  "dbname=$PG_DATABASE host=$PG_HOST port=$PG_PORT user=$PG_USER password=$PG_PASS" );
if ( ! $dbconn ) {
    echo  "Error connecting to the database !<br> " ;
    printf( "%s", pg_errormessage( $dbconn ) );
    exit();
}

//the database handle is $dbconn
//run a sql command to insert another record
$sqlcom= "insert into friends values ('Marius', 'marius@marius.com')";

$dbres = pg_exec($dbconn, $sqlcom );
if ( ! $dbres ) {
    echo  "Error : " + pg_errormessage( $dbconn );
    exit();
}

//let me know I've been added to the database by sending me an email
mail( "marius@marius.com",  "Lucky winner !", 
"You've just won a row in our database. Congratulations !",  "From : boss@db.com");
//yes, it's that simple
//( sending an email I mean, it's not so easy to win :)

//what do we have now in the database ?

$sqlcom= "select * from friends";

$dbres = pg_exec($dbconn, $sqlcom );
if ( ! $dbres ) {
    echo  "Error : " + pg_errormessage( $dbconn );
    exit();
}

//and interpret the results
$row=0;
$rowmax=pg_NumRows($dbres);

while ($row<$rowmax)
{
    $do = pg_Fetch_Object($dbres, $row);
    
    $s= "<p>$do->name | $do->email\n";
    printf( "%s", $s);
    
    $row++;
}

//close the database
pg_close( $dbconn );

//That's all. This isn't a tutorial to PHP, I wanted to show you how
//it can be done. As you can see, it isn't hard at all
?>

You can find more on the web. All of this is open-source, remember ? You'll easily find scripts made by various people doing lots of things and more open-source projects ( go get your shopping cart :)

Resources

• Apache
• PostgreSQL
• PostgreSQL: Introduction and Concepts
• SQL Tutorial
• The PHP Home

Well, that's it. You are now able to start developing database-enabled web applications. Of course, you should learn more. Much more.

---

Copyright © 2000, Marius Andreiana
Published in Issue 50 of Linux Gazette, February 2000

---