PostgreSQL - BeKnowledge

pridefulauburnData Management

Dec 16, 2012 (4 years and 7 months ago)

214 views

PostgreSQL
Y
ou might find that MySQL or even simple text files meet all your
data storage and retrieval requirements. Nothing about a simple
flat data structure in a small quantity demands a relational database
model. However, as we mentioned earlier in this book, you do have
choices when it comes to databases. In the next chapter, we’ll look at
a commercial offering, Oracle. In this chapter, we’ll look at what is
possibly the granddaddy of the free/open source database alterna-
tives, PostgreSQL (pronounced “post-gress-q-l”).
Why Choose PostgreSQL?
This is the part where the open-source purists start waving their
hands in the air and yelling with uncontrolled excitement! And the
excitement is easy to understand—PostgreSQL is a true open-source
database, made available under the simple and portable BSD license.
You can read the almost vanishingly short text of the license at
www.postgres.org/licence.html.
Are you back yet? See, we told you it was short. So reason number
one is not so much the license itself as the freedom from an 85 page
EULA laced with sneaky provisions that nobody alive really under-
stands, and that, in many cases, you can’t even see until you get the
box open. By then it’s too late —all the money’s gone. Which brings
us to reason number two: PostgreSQL is free. We don’t mean “free on
Mondays, Wednesdays, Fridays and the vernal equinox,” nor do we
mean “free to the right people,” nor even the more conventional and
arguably understandable “free for non-commercial use.” PostgreSQL
is completely and totally free (unless the developers change their
minds).
The term free applies to more than just the cost. Like the GNU
General Public License, you can alter, repackage, and redistribute
PostgreSQL as a standalone product or with your own applications.
Arguably better than the GPL for businesses, using and distributing
PostgreSQL will not “infect” all your code with the copyleft.
Finally, PostgreSQL supports some nifty special features and ele-
ments of the ANSI SQL92 and SQL99 standards that simply aren’t
available or fully developed in other databases, as well as the ability
to work with object and hierarchical data.
Of course, there are some disadvantages as well. First, consider
PostgreSQL’s ability to work with object and hierarchical data. Wait a
minute; didn’t we just sell that one as a feature? To the PostgreSQL
34
34
C H A P T E R
✦ ✦ ✦ ✦
In This Chapter
Why PostgreSQL
Installation and setup
Administration
PostgreSQL
✦ ✦ ✦ ✦
624
Part IV ✦ Connections
lovers in the audience, it may seem odd to sell these “features” as disadvantages. Don’t flame
us yet. We’re just taking a moment to expound on the Keep It Simple Stupid (KISS) philoso-
phy. You don’t need a sledgehammer to drive in a picture nail, and you don’t need an object
relational database to store addresses and phone numbers.
Some folks will debate this point, but simply put, PostgreSQL is not as fast as MySQL under
low-load circumstances. At some point, PostgreSQL’s more robust design will offer perfor-
mance advantages in really large datasets, but on average, MySQL is just that little bit perkier
for lots of reads. Again, if you have simpler data storage and retrieval needs, there’s no need
for you to go swimming around in the PostgreSQL waters.
Finally, PostgreSQL is more complicated. Permissions management, for one, is not as clear-
cut as it is with MySQL. PostgreSQL also offers some features that may cause the novice’s
eyes to glaze over —Schemas and Stored Procedures, while useful, aren’t strictly necessary
and you may find them to be clutter. Some people work much better when there are no
nonessentials on their desks; the same principle may hold true here.
All these things said, on balance PostgreSQL is a great tool —a superlative tool even—for
many jobs. Its userbase may be smaller than MySQL’s, but its devotees tend to be very loyal.
We can’t cover it very comprehensively and still stay within the focus of this book; but if
you’ve gotten this far, weighed all the benefits and disadvantages, and you choose
PostgreSQL, the rest of this chapter should set you off in the right direction.
Why Object-Relational Anyway?
Object relational databases (ORDBMS) are a relatively new class of product compared to the
relational database model that was developed in the early 1970s. In addition to implementing
the relational model discussed in Chapter 13, ORDBMS borrow from pure object databases,
which excel at handling media objects, spatial, and series style data. An ORDBMS implements
object properties on the components of a relational database in order to have the benefit of
both worlds. This, in turn, facilitates interaction with the object features of PHP. Because
PHP’s object model is significantly more powerful in version 5, there’s never been a better
time to use these two tools together.
This means that choosing PostgreSQL potentially offers the developer greater extensibility.
It’s fairly easy to define and add custom data types, operators, functions, and indexing meth-
ods. This has far reaching implications for fine-tuning performance; especially when working
with particularly complicated data structures.
At the data structure level, PostgreSQL tables and objects can benefit from either static or
dynamic inheritance. That is to say, a child object created from its parent can be made to
adopt characteristics identical to those of the parent —either just once when it is created,
or perpetually (as changes are made to the parent, they are passed on to the children).
Installing PostgreSQL
PostgreSQL is known to run on Linux, most Unix variants, and with the help of the Cygwin
framework (http://cygwin.com/), can even made to be run on Windows —although if per-
formance is your priority, this arrangement is suboptimal. In keeping with the focus of this
book, we’re going to follow the steps for a typical Linux installation. The procedure for the
625
Chapter 34 ✦ PostgreSQL
various Unixes will be very comparable. Windows users take heart —if you are determined to
run PostgreSQL on Windows, you have a couple of options:
✦ Wait for 7.5 or 8.0. If you’re not in a hurry, the developers suggest native windows sup-
port could be available as early as the first quarter of 2004 if 7.5 is on time or late 2004
if support slips to version 8.0. Check the site at www.postgresql.org/ for the latest.
✦ Try PowerGres. Software Research Associates has released a beta of its native windows
port based on PostgreSQL 7.3. You can find it at http://powergres.sra.co.jp/s/ja/,
but the site is in Japanese so prepare to do some blind clicking.
✦ The Cygwin option is not officially supported in the PostgreSQL documentation,
but Olivier Nano and Ed Wolpert have a compiled a nice FAQ/procedure for this at
http://colors.unice.fr/postgresql_win_setup.html. This should get you
up and running quickly.
Linux installation
If you are a Red Hat, Debian, or SuSe user, you can easily find a prepackaged binary version
for your distribution. Some of these are available on the PostgreSQL site, but you may fare
better getting a version from your actual distribution vendor. These packages tend to be
more current.
If you are a bare knuckles type, you’ll probably want to compile from source; there are usually
some advantages to doing so. We’ll cover this procedure briefly here. First, you’ll need to
download a source distribution from http://www.postgresql.org/. A variety of mirrors
are offered. Do the right thing and choose one close to you.
Unzip and untar the distribution in an out of the way place (your filename may be slightly
different),
tar –xzvf postgresql-7.x.tar.gz
and change into the created directory
cd postgresql-7.x
Next, you’ll want to run the configure script. You can obtain an exhaustive list of configure
options by typing ./configure --help, but we can’t cover all the options here. If you have a par-
ticular use in mind, it might be worth your time to review the list and decide what else you
want in your final installation. At a minimum, you’ll want to specify a path and consider the
--with-tcl, --with-perl and –with-python flags. No special flags are required for PHP
use, but these other languages are common on most Unix-like systems so this small consider-
ation is an easy way to add a level of flexibility that can pay large dividends down the road.
Also, if you plan to work with any non-English data, --enable-locale is considered essen-
tial. So our minimum recommended configure line is composed as follows:
./configure –-prefix=/usr/local/postgresql -–with-perl \
–-with-tcl –-with-python –-enable-locale
The next two steps are fairly routine and won’t vary much from system to system.
make
make install
626
Part IV ✦ Connections
For command-line usage, you’ll want to add the PostgreSQL binary directory to your search
path. Exactly how to do this will depend on the combination of configure options and the sys-
tem you are using.
Just as with MySQL, PostgreSQL likes to run with its own dedicated user id. You can accom-
plish this with the adduser or useradd commands. The name postgres is common, but by no
means required. The name should, of course, conjure up some idea of the user’s purpose. You
don’t want some zealous sys admin deleting a user from the system because he didn’t recog-
nize the name.
Next up is to choose a location for storing data. This directory must be owned by the postgres
user you just created; we can then initialize it as the data directory with the command:
initdb –D /path/to/your/data
There is no hard specification as to where this data directory must reside, but some common
choices are:
<postgres install directory>/data
/var/data
/var/pgdb
The PosgreSQL server process and binary are called postmaster,which shows up in a process
list by that name. Postmaster, however, takes a wide array of options, and the utility pg_ctl
is thoughtfully provided to simplify working with the postmaster daemon. So now we’re
ready to fire up PostgreSQL:
pg_ctl -D /data/path –o “-i” start
A word or two about the preceding command: The -D switch should be fairly obvious: it
points to an already initialized data directory. If you point to an invalid data directory, startup
will fail. The –o switch indicates that everything following is to be passed directly to the post-
master daemon. In this case, we’re passing -I to tell PostgreSQL to start up with a TCP/IP
socket enabled. This is necessary because Postgres will use Unix domain sockets by default,
an option we will not be using with PHP.
We don’t need to type this whole command over again to stop our server. The pg_ctl com-
mand will accept simple stop and restart command-line flags. Note that if you want to
restart with different options, you must use stop and then start. You cannot pass new
parameters to restart.
But is it a database yet?
Not quite. Like MySQL, Postgres features some command-line utilities in addition to a unified
interactive client for working with PostgreSQL databases. In this section, we’ll use some of
the command-line utilities stored in the Postgres binary directory to get started. Let’s start
by inspecting what’s already there by using the following command:
psql –l
which should return something like the following:
List of databases
Name | Owner | Encoding
------------+---------+-----------
template0 | pgsql | SQL_ASCII
template1 | pgsql | SQL_ASCII
(2 rows)
627
Chapter 34 ✦ PostgreSQL
Because a database is a mandatory argument to the interactive client and we don’t want to
work directly on one of our template databases, we’ll start by creating a single database that
we’ll use for the rest of this chapter:
createdb sample
That’s all there is to creating a new database, but of course, this is just a blank slate at this
point —a minimalist canvas based on the template database and on which we will paint a
structure to our exacting specifications.
Now we can use the interactive client to work with our database:
psql sample
This will open a copy of sample and drop us at a prompt along with some instructions. The
prompt inside the interactive client is different from the shell prompt, which can help you
distinguish where you are exactly. This is especially useful on a Linux system where multiple
shells open at one time is an everyday occurrence. The prompt will take the form
<databasename>=#, such that our prompt will look like:
sample=#
If you issue the \?command from this prompt, you’ll get a very long list of everything you
can do from this prompt, exclusive of SQL specifics, which of course are also supported here.
We can’t offer exhaustive coverage here, but a few key commands are worth exploring.
✦ The \h command lists available help for all of the supported SQL constructs such as
SELECT, DELETE, GRANT, and so on.
✦ The \d command, along with one of its accepted parameters, will display information
about your database or specific objects in it. Of specific interest are \dt, which will list
all tables in the current database, and \d <tablename>, which will show the structure
of the specified table.
✦\H turns on HTML output, which is handy for exporting data quickly to the Web. In con-
junction with the \T command you can customize the html output somewhat, and you
can use \o to send it all to a file.
Incidentally, you can call all of these options on the command line when starting psql.
Simply substitute a hyphen for the slash and psql will execute the commands in sequence
and then exit. For even greater utility, you can group these commands in a text file and read
them in from the command line.
Down to Real Work
Let’s build a simple structure inside our sample database. This example is necessarily abbre-
viated, but it is designed to give you a quick but useful familiarity with the Postgres and its
SQL syntax. If you have already exited from our previous example, get back in to the sample
database:
psql sample
Let’s start by defining a simple table to hold the names of some cartoons we really like:
CREATE TABLE cartoons(id serial, cartoon varchar(30));
628
Part IV ✦ Connections
The following command allows us to check our result:
\d cartoons
Table “public.cartoons”
Column | Type | Modifiers
---------+-----------------------+-----------
id | integer |
cartoon | character varying(30) |
Just so we can say we did something relational, we’ll create a second table to hold the names
of some of the characters in these cartoons.
CREATE TABLE characters(id int4, character varchar(15));
Postgres offers an astonishing 47 data types, so obviously our example barely even scratches
the surface. To be clear, some of these types are really other types with increasing data speci-
ficity built in. (You may already be familiar with this concept, commonly called an input mask
in other database tools.)
Now we’ll use some SQL to insert a record into the cartoons table. Note that because the
serial type is just an integer with an auto-increment flag attached, we do not need to specify
anything for ID:
INSERT INTO cartoons (cartoon) values(‘Scooby Doo’);
The absence of an error message suggests our efforts have met with success, but it’s a good
idea to check this with a SELECT statement anyway, first to be sure and second because, after a
number of records have been entered, we may mentally lose track of where the auto-increment
value stands. We’ll need the number to define a relationship with our characters table.
SELECT * from cartoons;
id | cartoon
----+------------
1 | Scooby Doo
(1 row)
Of course, we’d be in pretty bad shape if we managed to mess that one up. Let’s also put in a
character or two into the characters table.
INSERT INTO characters(id, character) VALUES(1, ‘Shaggy’);
INSERT INTO characters(id, character) VALUES(1, ‘Daphne’);
So we’ve built a database, created a couple of tables, and added a couple of records, just to
make sure things are going well. Before we make a Web application out of this, however, we
need to create a minimally privileged user —one who can access the tables in our sample
database, write to them, and read from them, but not modify them or investigate any other
aspect of the system.
In MySQL, users that did not already exist were implicitly created by the GRANT command.
While Postgres also uses the GRANT syntax, we must explicitly create a user first:
CREATE USER cartoonfan PASSWORD ‘secretword’;
After creating the user, we must give cartoonfan some privileges:
GRANT SELECT, INSERT, UPDATE, DELETE on cartoons to cartoonfan;
GRANT SELECT, INSERT, UPDATE, DELETE
629
Chapter 34 ✦ PostgreSQL
on characters to cartoonfan;
GRANT SELECT, INSERT, UPDATE, DELETE
on cartoons_id_seq to cartoonfan;
Note that we must issue this command at the table level. For security reasons, the wildcard
character does not function in this context. The last command given is necessary for the
serial field type we selected in the cartoons table.
PHP and PostgreSQL
Table 34-1 itemizes the PHP functions for working with PostgreSQL databases. There are
many more functions than we can possibly elaborate on here. Many of them will make sense
only after you have gained more familiarity with Postgres. Also, many of the function names
for Postgres changed with PHP version 4.2. Because this is a PHP5 book, we’re going to con-
centrate on the new names rather than the old.
Table 34-1: Common PostgreSQL Functions in PHP
Function Behavior
pg_connect() and Takes a single connection string as an argument, enumerating
pg_pconnect() connection parameters such as host, database, port, user and password.
pg_pconnect() is the persistent version. Returns a connection resource.
See the listings below for usage examples.
pg_query() This is the standard pass-through mechanism for sending basic SQL to the
server. In earlier versions, it was called pg_exec(), but this name has
been deprecated. Although optional, pg_query() likes to see a
connection resource, followed by a comma before the actual SQL.
pg_fetch_row() Each of these functions takes at least a query-result resource as an
pg_fetch_assoc() argument and returns varying results depending on the function chosen
pg_fetch_array() and how it is called. Each of these except pg_fetch_all() used to
pg_fetch_object() require a counter argument if you wished to iterate through the returned
pg_fetch_result() rows. This argument is still available but is not necessary. These functions
pg_fetch_all() differ primarily in the results they return, which in the same order as they
are listed are: 1) a numerically indexed array starting at an offset of 0; 2)
an associative array with field names as indices; 3) returns both a numeric
and an associative array; 4) returns the rows and values in object
notation; 5) returns a specific row and column offset; and 6) returns a
multidimensional array of the entire result set.
pg_affected_rows() Returns the number of tuples (rows) affected by an INSERT, UPDATE, or
DELETE query.
pg_free_result() Frees the memory used by a query result.
pg_num_fields() Returns the number of fields in a query result. Use with SELECT statements.
pg_num_rows() Returns the number of rows in a query result. Use with SELECT statements.
pg_close() Closes the PostgreSQL Connection. Takes a connection resource as an
argument.
630
Part IV ✦ Connections
The Cartoons Database
We’d like to be able to add some cartoons and characters to our database using a handy Web-
entry system. We’ve deliberately oversimplified our example to get through the key concepts
quickly, so it should be fairly easy to put together a system that allows us to achieve this
task. Listing 34-1 shows a welcoming page to our Cartoons database.
Listing 34-1:index.php
<html>
<head>
<title>Cartoons Database</title>
</head>
<body>
<h1>Cartoons and Characters Database</h1>
<p>Welcome to the cartoons and characters database. Existing
entries are provided below. Use the provided functions to get
more details and to edit, add or delete entries.</p>
<?php
$connect_parameters = “host=localhost dbname=sample
user=cartoonfan password=secretword”;
if ($link = pg_connect($connect_parameters)) {
$sSql = “select * from cartoons”;
$sResult = pg_query($link, $sSql);
if (pg_num_rows($sResult) > 0) {
print(“<table border=\”1\”>”);
print(“<tr><th>ID</th><th>Cartoon</th>
<th>Characters</th></tr>”);
while ($sRow = pg_fetch_object($sResult)) {
print(“<tr><th>$sRow->id</th>
<td>$sRow->cartoon</td>”);
$tSql = “select * from characters where
id = ‘$sRow->id’”;
$tResult = pg_query($tSql);
print(“<td>”);
while ($tRow = pg_fetch_object($tResult)) {
print(“$tRow->character “);
}
print(“</td></tr>”);
}
print(“</table>”);
} else {
print(“<p>There are not currently any records in the
cartoon database.</p>”);
}
631
Chapter 34 ✦ PostgreSQL
} else {
print(“<p>Connection to the cartoons database has
failed</p>”);}
?>
</body>
</html>
Notice how different our connect parameters are from what we’d use with MySQL. They
aren’t even comma separated! This function is even insensitive to the order in which they
are supplied—you simply have to use the appropriate parameter name and supply the
corresponding value. Additional recognized parameters for this function are options, tty,
and port.
The rest of this script reads much like a similar script would for MySQL. A query is defined,
we get the results of that as an object, issue a second query to get the character data, and
use the ubiquitous print statement to put it all into a nice html table.
This is fine as far as it goes, but we don’t yet have a way to insert, edit, and delete records.
In Listing 34-2, we create a new form for the purpose of inserting records.
Listing 34-2:insert.php
<html>
<head>
<title>Cartoons Database</title>
</head>
<body>
<h1>Cartoons and Characters Database</h1>
<?php
if ($_POST[action] == “Insert”) {
$connect_parameters = “host=localhost dbname=sample
user=cartoonfan password=secretword”;
$link = pg_connect($connect_parameters);
$iSql = “insert into cartoons(cartoon)
values(‘$_POST[cartoon]’)”;
if (pg_query($link, $iSql)) {
$jSql = “select currval(‘cartoons_id_seq’) as oid”;
$jResult = pg_query($jSql);
$j_id = pg_fetch_result($jResult, 0, ‘oid’);
$characters_array = explode( “\n”, $_POST[characters]);
for($i=0;$i<count($characters_array);$i++) {
$char = trim($characters_array[$i]);
$cSql = “insert into characters(id, character)
values($j_id, ‘$char’)”;
pg_query($cSql);
}
Continued
632
Part IV ✦ Connections
Listing 34-2 (continued)
print(“<p>Your submission was successfully inserted.
You can submit another, if you wish</p>”);
} else {
print(“<p>We were unable to insert the records as
submitted. You can try again, if you wish</p>”);
}
} else {
print(“<p>Welcome to the cartoons and characters database.
Enter the”);
print(“name of your favorite cartoon below, and choose
submit.</p>”);}
?>
<form action=”insert.php” method=”post”>
<p>Enter the name of a favorite cartoon<br>
<input type=”text” name=”cartoon”></p>
<p>Enter the name of some characters from the cartoon.
(You can enter more later). Use a hard return to
separate each name.<br>
<textarea cols=”15” rows=”8” name=”characters”>
</textarea></p>
<input type=”submit” name=”action” value=”Insert”>
</form>
<p><a href=”index.php”>Return to the main page.</a></p>
</body>
</html>
This script is doing a lot, so let’s review it. Note first the separation of the PHP and HTML ele-
ments. All of the conditional code appears at the top, and the conditional display require-
ments are set up such that we don’t have to weave in and out of PHP to get the job done.
We’re going to display a form even if a submission has just been made so that the user can
submit entries one right after the other without an intermediate step. The first conditional, at
the top of the page, checks to see if the page is being called from the form. If not, we print a
simple instruction set. If the page is the result of a form submission, the fun begins.
We start with a connection to our database. This item is not tested as we just connected on
our index page, so we will, perhaps perilously, assume a valid connection. The next step is to
generate the SQL for the cartoon table from the POST data and insert a new record into the
database. The next bit is designed to get the insert id of the inserted cartoon. Actually, PHP
offers pg_last_oid() for this purpose, but as of this writing, it did not yield the desired
result and is currently deemed unreliable.
Once we have the correct id, we do some manipulation on the contents of the textarea that
contains our characters. Refer to Chapter 21 on arrays for more on explode(). Basically,
we’re splitting the field each place we find a line break and popping the resulting elements
633
Chapter 34 ✦ PostgreSQL
into an array. Next we call trim() to get rid of the superfluous space character left behind by
explode(). This is done because Windows systems submit an additional \r wherever a line
break occurs. If we explode on this as a separator, explode will fail when the form is submit-
ted from Linux, for example, because the character is not there.
Finally, we iterate through the resulting array, putting each character into the characters
table with its own insert query, and we return a message of success or failure.
When we went back to test our results, we found a few problems with index.php and so we
present a revised version in Listing 34-3. We could have just changed the original listing, but
this so nicely illustrates the debugging process that we’ve included it this way to point out
the improvements.
Listing 34-3:index.php (improved)
<html>
<head>
<title>Cartoons Database</title>
</head>
<body>
<h1>Cartoons and Characters Database</h1>
<?php
if ($_POST[action] == “Insert”) {
$connect_parameters = “host=localhost dbname=sample
user=cartoonfan password=secretword”;
$link = pg_connect($connect_parameters);
$iSql = “insert into cartoons(cartoon)
values(‘$_POST[cartoon]’)”;
if (pg_query($link, $iSql)) {
$jSql = “select currval(‘cartoons_id_seq’) as oid”;
$jResult = pg_query($jSql);
$j_id = pg_fetch_result($jResult, 0, ‘oid’);
$characters_array = explode( “\n”, $_POST[characters]);
for($i=0;$i<count($characters_array);$i++) {
$char = trim($characters_array[$i]);
$cSql = “insert into characters(id, character)
values($j_id, ‘$char’)”;
pg_query($cSql);
}
print(“<p>Your submission was successfully inserted.
You can submit another, if you wish</p>”);
} else {
print(“<p>We were unable to insert the records as submitted.
You can try again, if you wish</p>”);
}
} else {
print(“<p>Welcome to the cartoons and characters database.
Enter the”);
print(“name of your favorite cartoon below, and choose
Continued
634
Part IV ✦ Connections
Listing 34-3 (continued)
submit.</p>”);}
?>
<form action=”insert.php” method=”post”>
<p>Enter the name of a favorite cartoon<br>
<input type=”text” name=”cartoon”></p>
<p>Enter the name of some characters from the cartoon.
(You can enter more later). Use a hard return to
separate each name.<br>
<textarea cols=”15” rows=”8” name=”characters”>
</textarea></p>
<input type=”submit” name=”action” value=”Insert”>
</form>
<p><a href=”index.php”>Return to the main page.</a></p>
</body>
</html>
First, we added a link to our now-finished insert form. Second, we are now passing the action
parameter via our submit button, purely for the sake of orderliness. We also encountered a
problem with our characters display. When we submitted a character with a space in its
name (okay, we admit, we were trying to submit Wonder Woman), it was unclear where one
character ends and the next one begins. Using a little regex and string concatenation, we’ve
now caused this to display as a comma-separated list.
Now we need a form for editing records. Listing 34-4 is what we’ve come up with.
Listing 34-4:edit.php
<html>
<head>
<title>Cartoons Database</title>
</head>
<body>
<h1>Cartoons and Characters Database</h1>
<?php
$connect_parameters = “host=localhost dbname=sample
user=cartoonfan password=secretword”;
$link = pg_connect($connect_parameters);
if ($_POST[action] == “Update”) {
$sSql = “update cartoons set cartoon = ‘$_POST[cartoon]’
where id = ‘$_POST[f]’”;
if (pg_query($sSql)) {
635
Chapter 34 ✦ PostgreSQL
$dSql = “delete from characters where id = ‘$_POST[f]’”;
pg_query($dSql);
$characters_array = explode( “\n”, $_POST[characters]);
for($i=0;$i<count($characters_array);$i++) {
$char = trim($characters_array[$i]);
if($char <> ‘’) {
$cSql = “insert into characters
(id, character)
values($_POST[f], ‘$char’)”;
pg_query($cSql);
}
}
print(“<p>Your edits were successfully posted.</p>”);
} else {
print(“<p>Update of record $_POST[f] failed.</p>”);
}
print(“<p><a href=\”index.php\”>Return to the main
page.</a></p>”);
} else {
$sSql = “select * from cartoons where id = $_GET[f]”;
$sResult = pg_query($sSql);
$sRow = pg_fetch_object($sResult);
print(“<form action=\”edit.php\” method=\”post\”>”);
print(“<p>Edit the name of a favorite cartoon<br>”);
print(“<input type=\”hidden\” name=\”f\”
value=\”$_GET[f]\”>”);
print(“<input type=\”text\” name=\”cartoon\”
value=\”$sRow->cartoon\”></p>”);
print(“<p>Edit the name of some characters from
the cartoon. “);
print(“(You can enter more later). Use a hard return to “);
print(“separate each name.<br>”);
print(“<textarea cols=\”15\” rows=\”8\”
name=\”characters\”>”);
$cSql = “select * from characters where id = $_GET[f]”;
$cResult = pg_query($cSql);
while ($cRow = pg_fetch_object($cResult)) {
print(“$cRow->character\r\n”);
}
print(“</textarea></p>”);
print(“<input type=\”submit\” name=\”action\”
value=\”Update\”>”);
print(“</form>”);
print(“<p><a href=\”index.php\”>Return to the main
page.</a></p>”);
}
?>
</body>
</html>
636
Part IV ✦ Connections
Like insert.php, edit.php is a recursive action form. The form post is sent to the same
script and the action taken depends, essentially, on the contents of a hidden variable,
$action.In the absence of this variable, we just retrieve the records from the cartoons and
character tables and drop them back in the form much as they appeared in the original insert
form. To parse the characters back into their original positions, note that we have essentially
reversed the explode function we used in insert.php, but we’ll shift it back into forward
when we go to post the updates.
One thing you might find strange is that before we post the updates to the characters table,
we delete all the entries. This is because we haven’t created a unique key for each character,
so there isn’t a way to conveniently refer to an individual record unambiguously. Yes, this is
a design flaw. In a bigger project, it would be a substantial design flaw. But there are some
advantages to the way we’ve done this. A form to update both characters and cartoons in the
same action is much easier to do in this scenario. It would be quite a bit more code intensive,
though certainly feasible, to add a serial id to the characters table, retrieve all three fields, set
up a multidimensional array in our form, retrieve it, and process it in our script such that
multiple records are updated in a single operation. You get the idea. Sometimes it’s okay to
opt for simplicity.
Finally, we’ve made a few changes to index.php, which we’re going to show you in Listing
34-5. All we’ve done is added a way to get at the edit functions and a simple routine for delet-
ing a record, parent and children, in one operation.
Listing 34-5:index.php (final)
<html>
<head>
<title>Cartoons Database</title>
</head>
<body>
<h1>Cartoons and Characters Database</h1>
<p>Welcome to the cartoons and characters database. Existing
entries are provided below. Use the provided functions to get
more details and to edit, add or delete entries.</p>
<?php
$connect_parameters = “host=localhost dbname=sample
user=cartoonfan password=secretword”;
if ($link = pg_connect($connect_parameters)) {
if($_GET[action] == “d”) {
$dSql = “delete from characters where id = ‘$_GET[f]’”;
pg_query($dSql);
$dSql = “delete from cartoons where id = ‘$_GET[f]’”;
pg_query($dSql);
}
$sSql = “select * from cartoons”;
$sResult = pg_query($link, $sSql);
637
Chapter 34 ✦ PostgreSQL
if (pg_num_rows($sResult) > 0) {
print(“<table border=\”1\”>”);
print(“<tr><th>ID</th><th>Cartoon</th>
<th>Characters</th><th></th></tr>”);
while ($sRow = pg_fetch_object($sResult)) {
print(“<tr><th>$sRow->id</th>
<td>$sRow->cartoon</td>”);
$tSql = “select * from characters where id = ‘$sRow->id’”;
$tResult = pg_query($tSql);
print(“<td>”);
$character_string = “”;
while ($tRow = pg_fetch_object($tResult)) {
$character_string .= “$tRow->character, “;
}
$new_character_string = ereg_replace(“(, )$”, “”,
$character_string);
print(“$new_character_string</td>”);
print(“<td><a href=\”edit.php?f=$sRow->id\”>Edit</a> |”);
print(“ <a href=\”index.php?f=$sRow->id&action=d\”>
Delete</a></td></tr>”);
}
print(“</table>”);
} else {
print(“<p>There are not currently any records in the
cartoon database.</p>”);
}
print(“<p><a href=\”insert.php\”>Add a Record</a></p>”);
} else {
print(“<p>Connection to the cartoons database has
failed</p>”);}
?>
</body>
</html>
Summary
PostgreSQL is an interesting and powerful database tool. Although we did not comprehen-
sively cover all of its utility here, we have shown you enough basics to get started with it.
Check the PHP documentation at www.php.net/pgsql for a comprehensive listing of
PostgreSQL functions.
✦ ✦ ✦