297 lines
16 KiB
HTML
297 lines
16 KiB
HTML
<!--startcut ==============================================-->
|
|
<!-- *** BEGIN HTML header *** -->
|
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
|
|
<HTML><HEAD>
|
|
<title>Web Applications with Database Connectivity: getting started LG #50</title>
|
|
</HEAD>
|
|
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#0000AF"
|
|
ALINK="#FF0000">
|
|
<!-- *** END HTML header *** -->
|
|
|
|
<!-- *** BEGIN navbar *** -->
|
|
<A HREF="index.html"><IMG ALT="[ Table of Contents ]"
|
|
SRC="../gx/indexnew.gif" WIDTH=163 HEIGHT=60 ALIGN=bottom ></A>
|
|
<A HREF="../index.html"><IMG ALT="[ Front Page ]"
|
|
SRC="../gx/homenew.gif" WIDTH=163 HEIGHT=60 ALIGN=bottom></A>
|
|
<A HREF="lg_tips50.html"><IMG ALT="[ Prev ]" SRC="../gx/back2.gif" WIDTH=41 HEIGHT=60 ALIGN=bottom></A>
|
|
<A HREF="../faq/index.html"><IMG ALT="[ Linux Gazette FAQ ]"
|
|
SRC="./../gx/dennis/faq.gif"WIDTH=163 HEIGHT=60 ALIGN=bottom></A>
|
|
<A HREF="advani.html"><IMG ALT="[ Next ]" SRC="../gx/fwd.gif" WIDTH=41 HEIGHT=60 ALIGN=bottom ></A>
|
|
<!-- *** END navbar *** -->
|
|
|
|
<!--endcut ============================================================-->
|
|
|
|
<H4>
|
|
"Linux Gazette...<I>making Linux just a little more fun!</I>"
|
|
</H4>
|
|
|
|
<P> <HR> <P>
|
|
<!--===================================================================-->
|
|
|
|
<center>
|
|
<font color="maroon">
|
|
<H1>Web Applications with Database Connectivity</H1>
|
|
<h2>- getting started -</h2>
|
|
</FONT>
|
|
<H4>By <a href="mailto:mandreiana@yahoo.com">Marius Andreiana</a></H4>
|
|
</center>
|
|
<P> <HR> <P>
|
|
|
|
<!-- END header -->
|
|
|
|
|
|
|
|
|
|
<p><h3>Abstract</h3>
|
|
<p>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.<p>
|
|
Although this is written for a Red Hat Linux system, most of it ( if not all )
|
|
applies to other distributions.
|
|
<p>
|
|
<h3>Contents</h3>
|
|
<UL>
|
|
<LI><a href="#linux">Linux</a>
|
|
<LI><a href="#apache">Apache</a>
|
|
<LI><a href="#postgres">PostgreSQL</a>
|
|
<LI><a href="#php3">PHP3</a>
|
|
<LI><a href="#all">Putting it all together</a>
|
|
<LI><a href="#res">Resources</a>
|
|
</UL>
|
|
|
|
<h2><p><a name="linux">Linux</a></h2>
|
|
<p>We all know about Linux... You could also check my
|
|
<a href="http://www.linuxstart.com/~marius/intro/">Introduction to Linux</a>
|
|
|
|
<p>
|
|
<h2><p><a name="apache">Apache</a></h2>
|
|
<p>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.
|
|
<p>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 : <b>http://localhost/</b>. You should see a page which tells
|
|
you that the installation was successful.
|
|
<p>
|
|
|
|
<h2><p><a name="postgres">PostgreSQL</a></h2>
|
|
<p>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.
|
|
<p>The easiest way to set up PostgreSQL is from
|
|
<a href="ftp://ftp.redhat.com/pub/RedHat/6.1/RPMS/">RedHat's 6.1 RPMs</a>.
|
|
Install everything beginning with <b>postgres</b>.
|
|
<p>I recommend not to store your database files in the default location,
|
|
<b>/var/lib/pgsql/</b>, but in <b>/home/postgresdb</b> ( or somewhere
|
|
in <b>/usr/local</b> ). That way, if you have to reinstall the system, you
|
|
woun't lose the data ( you do have a separate partition for <b>/home</b>,
|
|
don't you ? ).
|
|
<p>So how do you do this ?
|
|
|
|
After installing the RPMs, user <b>postgres</b>
|
|
is the database administrator ( not <b>root</b> !). Logged in as root, set
|
|
up a password for <b>postgres</b> :
|
|
<p><b># passwd postgres</b>
|
|
<p>
|
|
Next, create the directory and change it's
|
|
owner to be the PostgreSQL administrator :
|
|
<p>
|
|
<b># mkdir /home/postgresdb<br>
|
|
# chown postgres.postgres /home/postgresdb
|
|
</b>
|
|
|
|
<p>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 <b>/etc/profile</b> file :
|
|
<p><b>PGDATA=/home/postgresdb<br>
|
|
PGLIB=/usr/lib/pgsql<p>
|
|
export PGDATA PGLIB
|
|
</b><p>
|
|
In order to use the database, the <b>postmaster</b> must be running.
|
|
You'll probably want it to run all the time, so let's add it to the
|
|
start-up daemons :
|
|
<p>
|
|
<b># setup</b>
|
|
<p>Select <b>Sytem services</b>, then make sure <b>postgresql</b> is
|
|
marked with a *.
|
|
<p>
|
|
Okay, one more thing. We must modify in <b>/etc/rc.d/init.d/postgresql</b>
|
|
( which is run at start-up ) the database directory. Replace every
|
|
occurence of <b>/var/lib/pgsql</b> with <b>/home/postgresdb</b>
|
|
<p>Now you are ready to go. Start the <b>postgresql</b> service :
|
|
<p><b>
|
|
# /etc/rc.d/init.d/postgresql start
|
|
</b>
|
|
<p>
|
|
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 ).
|
|
<p>
|
|
It's time to test the instalation. Create a user <b>pguser</b> :
|
|
<p><b>
|
|
# adduser pguser<br>
|
|
# passwd pguser
|
|
</b><p>
|
|
Logged in as <b>postgres</b>, create the same user for PostgreSQL :
|
|
<p><b>
|
|
$ createuser pguser
|
|
</b><p>
|
|
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.
|
|
<p>
|
|
Log in as <b>pguser</b> and run <b>psql</b>. Create a table and insert
|
|
a few records :
|
|
<p><b>
|
|
pguser=> create table friends ( name varchar(30), email varchar(90) );<br>
|
|
pguser=> insert into friends values ( 'John', 'john@johnny.com' );<br>
|
|
pguser=> insert into friends values ( 'Mary', 'mary@linuxmail.com' );<br>
|
|
pguser=> select * from friends;<br>
|
|
</b>
|
|
<p>You could do this from <b>PgAccess</b> ( 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 ).
|
|
<p>
|
|
Time to move forward.
|
|
|
|
<p>
|
|
<h2><p><a name="php3">PHP3</a></h2>
|
|
<p>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.
|
|
<p>Again, the easiest way to set up PHP is from
|
|
<a href="ftp://ftp.redhat.com/pub/RedHat/6.1/RPMS/">RedHat's 6.1 RPMs</a>.
|
|
Install <b>php</b>, <b>php-manual</b>, <b>php-pgsql</b>.
|
|
<p>
|
|
Now let's tell Apache about PHP. Uncomment the follwing lines in
|
|
<b>/etc/httpd/conf/httpd.conf<p>
|
|
LoadModule php3_module modules/libphp3.so<br>
|
|
AddModule mod_php3.c
|
|
<p></b> and <b>/etc/httpd/conf/srm.conf<p>
|
|
AddType application/x-httpd-php3 .php3<br>
|
|
AddType application/x-httpd-php3-source .phps
|
|
</b><p>
|
|
You could also add to <b>DirectoryIndex</b> index.php3 ( in <b>srm.conf</b> )
|
|
<p>
|
|
<p>Choose a place which will hold our work. For start you could use <b>/home/httpd/html/</b>
|
|
( if that's your DocumentRoot defined in <b>srm.conf</b> ).
|
|
<p>
|
|
Let's test with a simple document named <b>test.php3</b> ( the extension is
|
|
very important ) with a single line : <b><?php phpinfo() ?></b>
|
|
<p>
|
|
Access this file from your web browser from <b>http://localhost/test.php3</b>
|
|
<br>
|
|
You should see lots of information about PHP, your server ...
|
|
<p>
|
|
|
|
|
|
<h2><p><a name="all">Putting it all together</a></h2>
|
|
<p>Everything is set, let's get some results !
|
|
<p>Our friends John and Mary are still there, waiting for you.
|
|
Create a file named <b>friends.php3</b>.
|
|
<a href="misc/andreiana/friends.php.txt">Here it is</a>, with
|
|
lots of comments.
|
|
<p>
|
|
<FONT color=#000000><FONT color="#0000BB">
|
|
<?php<br><br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//define variables used with the database</FONT></I><FONT color="#0000BB">
|
|
<br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//usually this goes in an include file</FONT></I><FONT color="#0000BB">
|
|
<br>$PG_HOST=<FONT color="#DD0000">
|
|
<STRONG>"localhost"</STRONG></FONT><FONT color="#007700">;</FONT><br>$PG_PORT=5432<FONT color="#007700">;</FONT><br>$PG_DATABASE=<FONT color="#DD0000">
|
|
<STRONG>"pguser"</STRONG></FONT><FONT color="#007700">;</FONT><br>$PG_USER=<FONT color="#DD0000">
|
|
<STRONG>"pguser"</STRONG></FONT><FONT color="#007700">;</FONT><br><br>$PG_PASS=<FONT color="#DD0000">
|
|
<STRONG>""</STRONG></FONT><FONT color="#007700">;</FONT><br><br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//let's open the database</FONT></I><FONT color="#0000BB">
|
|
<br>$dbconn=pg_connect<FONT color="#007700">(</FONT> <FONT color="#DD0000">
|
|
<STRONG>"dbname=$PG_DATABASE host=$PG_HOST port=$PG_PORT user=$PG_USER password=$PG_PASS"</STRONG></FONT> <FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br><FONT color="#007700">if</FONT> <FONT color="#007700">(</FONT> ! $dbconn <FONT color="#007700">)</FONT> <FONT color="#007700">{</FONT><br> <FONT color="#007700">echo</FONT> <FONT color="#DD0000">
|
|
<STRONG>"Error connecting to the database !<br> "</STRONG></FONT> <FONT color="#007700">;</FONT><br> printf<FONT color="#007700">(</FONT><FONT color="#DD0000">
|
|
<STRONG>"%s"</STRONG></FONT>, pg_errormessage<FONT color="#007700">(</FONT> $dbconn <FONT color="#007700">)</FONT> <FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br> exit<FONT color="#007700">(</FONT><FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br><FONT color="#007700">}</FONT><br><br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//the database handle is $dbconn</FONT></I><FONT color="#0000BB">
|
|
<br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//run a sql command to insert another record</FONT></I><FONT color="#0000BB">
|
|
<br>$sqlcom=<FONT color="#DD0000">
|
|
<STRONG>"insert into friends values ('Marius', 'marius@marius.com')"</STRONG></FONT><FONT color="#007700">;</FONT><br><br>$dbres = pg_exec<FONT color="#007700">(</FONT>$dbconn, $sqlcom <FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br><FONT color="#007700">if</FONT> <FONT color="#007700">(</FONT> ! $dbres <FONT color="#007700">)</FONT> <FONT color="#007700">{</FONT><br> <FONT color="#007700">echo</FONT> <FONT color="#DD0000">
|
|
<STRONG>"Error : "</STRONG></FONT> + pg_errormessage<FONT color="#007700">(</FONT> $dbconn <FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br> exit<FONT color="#007700">(</FONT><FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br><FONT color="#007700">}</FONT><br><br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//let me know I've been added to the database by sending me an email</FONT></I><FONT color="#0000BB">
|
|
<br>mail<FONT color="#007700">(</FONT><FONT color="#DD0000">
|
|
<STRONG>"marius@marius.com"</STRONG></FONT>, <FONT color="#DD0000">
|
|
<STRONG>"Lucky winner !"</STRONG></FONT>, <br><FONT color="#DD0000">
|
|
<STRONG>"You've just won a row in our database. Congratulations !"</STRONG></FONT>, <FONT color="#DD0000">
|
|
<STRONG>"From : boss@db.com"</STRONG></FONT><FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//yes, it's that simple</FONT></I><FONT color="#0000BB">
|
|
<br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//( sending an email I mean, it's not so easy to win :)</FONT></I><FONT color="#0000BB">
|
|
<br><br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//what do we have now in the database ?<br></FONT></I><FONT color="#0000BB">
|
|
<br>$sqlcom=<FONT color="#DD0000">
|
|
<STRONG>"select * from friends"</STRONG></FONT><FONT color="#007700">;</FONT><br><br>$dbres = pg_exec<FONT color="#007700">(</FONT>$dbconn, $sqlcom <FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br><FONT color="#007700">if</FONT> <FONT color="#007700">(</FONT> ! $dbres <FONT color="#007700">)</FONT> <FONT color="#007700">{</FONT><br> <FONT color="#007700">echo</FONT> <FONT color="#DD0000">
|
|
<STRONG>"Error : "</STRONG></FONT> + pg_errormessage<FONT color="#007700">(</FONT> $dbconn <FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br> exit<FONT color="#007700">(</FONT><FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br><FONT color="#007700">}</FONT><br><br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//and interpret the results</FONT></I><FONT color="#0000BB">
|
|
<br>$row=0<FONT color="#007700">;</FONT><br>$rowmax=pg_NumRows<FONT color="#007700">(</FONT>$dbres<FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br><br><FONT color="#007700">while</FONT> <FONT color="#007700">(</FONT>$row<$rowmax<FONT color="#007700">)</FONT><br><FONT color="#007700">{</FONT><br> $do = pg_Fetch_Object<FONT color="#007700">(</FONT>$dbres, $row<FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br> <br> $s=<FONT color="#DD0000">
|
|
<STRONG>"<p>$do->name | $do->email\n"</STRONG></FONT><FONT color="#007700">;</FONT><br> printf<FONT color="#007700">(</FONT><FONT color="#DD0000">
|
|
<STRONG>"%s"</STRONG></FONT>, $s<FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br> <br> $row++<FONT color="#007700">;</FONT><br><FONT color="#007700">}</FONT><br><br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//close the database</FONT></I><FONT color="#0000BB">
|
|
<br>pg_close<FONT color="#007700">(</FONT> $dbconn <FONT color="#007700">)</FONT><FONT color="#007700">;</FONT><br><br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//That's all. This isn't a tutorial to PHP, I wanted to show you how</FONT></I><FONT color="#0000BB">
|
|
<br></FONT><I><FONT color="#FF8000" size="-1">
|
|
//it can be done. As you can see, it isn't hard at all</FONT></I><FONT color="#0000BB">
|
|
<br>?><br></FONT></FONT>
|
|
|
|
<p>
|
|
<p>
|
|
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 :)
|
|
|
|
<h2><a name="res">Resources</a></h2>
|
|
<UL>
|
|
<LI><a href="http://www.apache.org">Apache</a>
|
|
<LI><a href="http://www.postgresql.org">PostgreSQL</a>
|
|
<LI><a href="http://www.postgresql.org/docs/awbook.html">PostgreSQL: Introduction and Concepts</a>
|
|
<LI><a href="http://w3.one.net/~jhoffman/sqltut.htm">SQL Tutorial</a>
|
|
<LI><a href="http://www.php.net">The PHP Home</a>
|
|
</UL>
|
|
|
|
|
|
<p>
|
|
<p>Well, that's it. You are now able to start developing database-enabled
|
|
web applications. Of course, you should learn more. Much more.
|
|
<p>
|
|
<p>
|
|
|
|
</body>
|
|
</html>
|
|
|
|
|
|
|
|
|
|
<!-- *** BEGIN copyright *** -->
|
|
<P> <hr> <P>
|
|
<H5 ALIGN=center>
|
|
|
|
Copyright © 2000, Marius Andreiana<BR>
|
|
Published in Issue 50 of <i>Linux Gazette</i>, February 2000</H5>
|
|
<!-- *** END copyright *** -->
|
|
|
|
<!--startcut ==========================================================-->
|
|
<P> <HR> <P>
|
|
<!-- *** BEGIN navbar *** -->
|
|
<A HREF="index.html"><IMG ALT="[ Table of Contents ]"
|
|
SRC="../gx/indexnew.gif" WIDTH=163 HEIGHT=60 ALIGN=bottom ></A>
|
|
<A HREF="../index.html"><IMG ALT="[ Front Page ]"
|
|
SRC="../gx/homenew.gif" WIDTH=163 HEIGHT=60 ALIGN=bottom></A>
|
|
<A HREF="lg_tips50.html"><IMG ALT="[ Prev ]" SRC="../gx/back2.gif" WIDTH=41 HEIGHT=60 ALIGN=bottom></A>
|
|
<A HREF="../faq/index.html"><IMG ALT="[ Linux Gazette FAQ ]"
|
|
SRC="./../gx/dennis/faq.gif"WIDTH=163 HEIGHT=60 ALIGN=bottom></A>
|
|
<A HREF="advani.html"><IMG ALT="[ Next ]" SRC="../gx/fwd.gif" WIDTH=41 HEIGHT=60 ALIGN=bottom ></A>
|
|
<!-- *** END navbar *** -->
|
|
</BODY></HTML>
|
|
<!--endcut ============================================================-->
|