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 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.
Next: Anatomy Class >>
More PHP Articles
More By icarus, (c) Melonfire
/ 7 
