Database Abstraction With PHP - Sultans Of Swing
( Page 3 of 11 )

Before we begin, you might want to take a quick look at the tables I'll be using throughout this article. Here they are:

mysql> SELECT * FROM cds;
+----+-------------------+---------------+
| id | title             | artist        |
+----+-------------------+---------------+
| 16 | On Every Street   | Dire Straits  |
| 15 | Fever             | Kylie Minogue |
| 17 | Hell Freezes Over | The Eagles    |
+----+-------------------+---------------+
3 rows in set (0.06 sec)

mysql> SELECT * FROM tracks;
+----+----------------------+------+
| cd | track                | indx |
+----+----------------------+------+
| 17 | I Can't Tell You Why |    9 |
| 15 | More More More       |    1 |
| 17 | Hotel California     |    6 |
| 15 | Love At First Sight  |    2 |
| 16 | Sultans Of Swing     |    1 |
| 16 | Lady Writer          |    2 |
| 16 | Romeo And Juliet     |    3 |
+----+----------------------+------+
7 rows in set (0.00 sec)

As you can see, I've got my data split up into two tables in order to avoid duplicating information unnecessarily (geeks like to call this "normalization", simply because it sounds way cooler than "I'm really lazy"). The "cd" table contains a list of all the CDs currently taking up shelf space in my living room, while the "tracks" table lists the tracks on each CD. The two tables are linked to each by a unique CD identifier (again, the geek term for this is "foreign key", but you can forget that one immediately).

Using SQL, it's possible to easily link these two tables together in order to obtain a complete picture of the data contained within them:

mysql> SELECT title, artist, indx, track FROM cds, tracks WHERE cds.id =
tracks.cd ORDER BY cd, indx;
+-------------------+---------------+------+----------------------+
| title             | artist        | indx | track                |
+-------------------+---------------+------+----------------------+
| Fever             | Kylie Minogue |    1 | More More More       |
| Fever             | Kylie Minogue |    2 | Love At First Sight  |
| On Every Street   | Dire Straits  |    1 | Sultans Of Swing     |
| On Every Street   | Dire Straits  |    2 | Lady Writer          |
| On Every Street   | Dire Straits  |    3 | Romeo And Juliet     |
| Hell Freezes Over | The Eagles    |    6 | Hotel California     |
| Hell Freezes Over | The Eagles    |    9 | I Can't Tell You Why |
+-------------------+---------------+------+----------------------+
7 rows in set (0.00 sec)

You can do lots of other things with these two tables as well, but they're all completely useless so far as this article is concerned. So let's get started with some code - you can play with SQL on your own time.

Let's suppose I want to display a list of CDs I like on my personal Web page. The data's already there in my table; all I need to do is extract it and display it. 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 cds";
$result = mysql_query($query) or die ("Error in query: $query. " .
mysql_error());

// iterate through rows and print column data
// in the form TITLE - ARTIST
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:

On Every Street - Dire Straits
Fever - Kylie Minogue
Hell Freezes Over - The Eagles

[3 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