1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
|
<!--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 ============================================================-->
|
