Interfacing Relational Databases to the Web
To change field values in records, use UPDATE:
UPDATE tablename SET field-1 = value-n WHERE qualification
The WHERE is optional, but if you don't specify a WHERE clause, SQL will update all the records, which is clearly the “Wrong Thing”.
Let's say Fred Mbogo wants to change his shell. This script will do it:
UPDATE passwd SET shell = '/bin/tcsh' WHERE username = 'fred';
To delete records, simply use DELETE:
DELETE FROM tablename WHERE qualifier
Just like UPDATE, the WHERE is optional, but you probably want it anyway. Let's say Fred has offended his sysadmin one too many times:
DELETE FROM passwd WHERE username = 'fred';
The on-line PHP3 manual, http://www.php.net/manual/, is an excellent reference and will be necessary reading before you create your own database web application. Furthermore, it is a database-backed web site and has lots of user comments. Here, we will examine just the most basic PHP3 features.
Here is a simple PHP3 program, which demonstrates some basic features. Note the separate HTML and PHP3 blocks:
<title>Hello, world!</title> <body> <?php echo("Hello, world!\n"); echo("<p>\nWhat a <b>bold</b> move this is!\n"); ?> </body>
This program will send the following HTML to the remote browser:
<title>Hello, world!</title> <body> Hello, world! <p> What a <b>bold</b> move this is! </body>A similar program, which takes an argument, would look like this:
<title>Hello, world!</title> <body> <?php echo("Hello, $name!\n"); echo("<p>\nWhat a <b>bold</b> move this is!\n"); ?> </body>You would view this page (assuming you called it hello.php3) like any CGI script: http://yourhost.net/~fred/hello.php3?name=fred. This, of course, assumes you are named Fred and have put this file in your /public_html directory.
PHP3 provides a number of useful functions for connecting to databases; the best place to read up on these is www.php.net/manual/ref.pgsql.php3, and we shall examine a few of them.
int pg_connect(host, port, options, tty, dbname);
This function returns an integer, the “connection index”, which you will need for all operations on this connection. If a connection can't be established, it returns zero.
int pg_exec(conn, query); Executes the SQL query query on connection conn. Returns a result set index.
int pg_numrows(result); Returns the number of tuples in the result set result.
array pg_fetch_row(result,Returns an array of values corresponding to the row row of result set result.
void pg_close(conn); Closes the connection conn.
Our example application is an address book that one can access over the Internet. A user logs in with her name and password and is presented with a menu of options, including browsing and searching the address book and adding a new person. For each person in the address book, the database stores an arbitrary number of e-mail addresses, telephone numbers, URLs and postal addresses. This address book also has some nifty features like mailing passwords to new accounts and automatic mailto and href links for e-mail and web addresses.
Practical books for the most technical people on the planet. Newly available books include:
- Agile Product Development by Ted Schmidt
- Improve Business Processes with an Enterprise Job Scheduler by Mike Diehl
- Finding Your Way: Mapping Your Network to Improve Manageability by Bill Childers
- DIY Commerce Site by Reven Lerner
Plus many more.