Home arrow PHP arrow Page 3 - Database Abstraction With PHP

Sultans Of Swing - PHP

One of the nicest things about Perl - the DBI module - finallymakes an appearance in PHP. Take a look at the PEAR database abstractionlayer, by far one of the coolest PHP widgets out there.

  1. Database Abstraction With PHP
  2. Alphabet Soup
  3. Sultans Of Swing
  4. Independence Day
  5. Different Strokes
  6. The Number Game
  7. Preparing For The Long Haul
  8. Commitment Issues
  9. No News Is Good News
  10. Catch Me If You Can
  11. Once Again, The Headlines
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 64
February 13, 2002

print this article


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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: