The Bookworm Turns - PHP

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 FrancisXML and PHP - Vikram VaswaniWhere 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.

>>> More PHP Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus