PHP Application Development With ADODB (part 1) - The Bookworm Turns (
Page 3 of 10 )
Before we get into the code, you
might want to take a quick look at the database table I'll be using throughout
this article. Here it is:
mysql> SELECT * FROM library;
+----+-------------------+----------------+
| id | title | author |
+----+-------------------+----------------+
| 14 | Mystic River | Dennis Lehane |
| 15 | For Kicks | Dick Francis |
| 16 | XML and PHP | Vikram Vaswani |
| 17 | Where Eagles Dare | Jack Higgins |
+----+-------------------+----------------+
As you might have guessed, the "library" table contains a list of all the
books currently taking up shelf space in my living room. Each record within the
table is identified by a unique number (the geek term for this is "foreign key",
but you can forget that one immediately).
Now, let's suppose I want to
display a list of my favourite books on my personal Web site. Everything I need
is stored in the table above; all yours truly has to do is write a script to
pull it out and massage it into a readable format. Since PHP comes with
out-of-the-box support for MySQL, accomplishing this is almost as simple as it
sounds.
<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");
// open connection to database
$connection = mysql_connect("localhost", "john", "doe") or die ("Unable
to connect!");
// select database
mysql_select_db("db278") or die ("Unable to select database!");
// execute query
$query = "SELECT * FROM library";
$result = mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
// iterate through rows and print column data
// in the form TITLE - AUTHOR
while ($row = mysql_fetch_row($result))
{
echo "$row[1] - $row[2]\n";
}
// get and print number of rows in resultset
echo "\n[" . mysql_num_rows($result) . " rows returned]\n";
// close database connection
mysql_close($connection);
?>
Here's what the output looks like:
Mystic River - Dennis Lehane
For Kicks - Dick Francis
XML and PHP - Vikram Vaswani
Where Eagles Dare - Jack Higgins
[4 rows returned]
The process here is fairly straightforward: connect to the database,
execute a query, retrieve the result and iterate through it. The example above
uses the mysql_fetch_row() function to retrieve each row as an integer-indexed
array, with the array indices corresponding to the column numbers in the
resultset; however, it's just as easy to retrieve each row as an associative
array (whose keys correspond to the column names) with mysql_fetch_assoc(), or
an object (whose properties correspond to the column names) with
mysql_fetch_object().
The problem with this script? Since I've used
MySQL-specific functions to interact with the database, it's going to crash and
burn the second I switch my data over to PostgreSQL or Oracle. Which is where
the database abstraction layer comes in.
/ 7 
