The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

Fetching with Classes

Now that you're happy with the basics of fetching data from MySQL, it's time to build some more on the MySQL class from the last solution.

First, let's add a method to run queries from the class:

Example 3.7. Database/MySQL.php (in SPLIB) (excerpt)    
   
 /**    
  * Returns an instance of MySQLResult to fetch rows with    
  * @param $sql string the database query to run    
  * @return MySQLResult    
  * @access public    
  */    
 function &query($sql)    
 {    
   if (!$queryResource = mysql_query($sql, $this->dbConn)) {    
     trigger_error('Query failed: ' . mysql_error($this->dbConn)    
                   . ' SQL: ' . $sql);    
     return new MySQLResult($this, $queryResource);    
   }    
 }

What this new method does is accept a variable containing an SQL statement, run it, then build a new object from another class, MySQLResult (described below). It then returns this object to the point where query was called.

Here's the code for that new class, MySQLResult:

Example 3.8. Database/MySQL.php (in SPLIB) (excerpt)    
   
/**    
* MySQLResult Data Fetching Class    
* @access public    
* @package SPLIB    
*/    
class MySQLResult {    
 /**    
  * Instance of MySQL providing database connection    
  * @access private    
  * @var MySQL    
  */    
 var $mysql;    
   
 /**    
  * Query resource    
  * @access private    
  * @var resource    
  */    
 var $query;    
   
 /**    
  * MySQLResult constructor    
  * @param object mysql   (instance of MySQL class)    
  * @param resource query (MySQL query resource)    
  * @access public    
  */    
 function MySQLResult(&$mysql, $query)    
 {    
   $this->mysql = &$mysql;    
   $this->query = $query;    
 }    
   
 /**    
  * Fetches a row from the result    
  * @return array    
  * @access public    
  */    
 function fetch()    
 {    
   if ($row = mysql_fetch_array($this->query, MYSQL_ASSOC)) {    
     return $row;    
   } else if ( $this->size() > 0 ) {    
     mysql_data_seek($this->query, 0);    
     return false;    
   } else {    
     return false;    
   }    
 }    
   
 /**    
  * Checks for MySQL errors    
  * @return boolean    
  * @access public    
  */    
 function isError()    
 {    
   return $this->mysql->isError();    
 }    
}

Now, hold your breath just a little longer until you've seen what using these classes is like:

Example 3.9. 5.php    
   
<?php    
// Include the MySQL class    
require_once 'Database/MySQL.php';    
   
$host   = 'localhost'; // Hostname of MySQL server    
$dbUser = 'harryf';    // Username for MySQL    
$dbPass = 'secret';    // Password for user    
$dbName = 'sitepoint'; // Database name    
   
// Connect to MySQL    
$db = &new MySQL($host, $dbUser, $dbPass, $dbName);    
   
$sql = "SELECT * FROM articles ORDER BY title";    
   
// Perform a query getting back a MySQLResult object    
$result = $db->query($sql);    
   
// Iterate through the results    
while ($row = $result->fetch()) {    
 echo 'Title: '  . $row['title']  . '<br />';    
 echo 'Author: ' . $row['author'] . '<br />';    
 echo 'Body: '   . $row['body']   . '<br />';    
}    
?>

If you're not used to object oriented programming, this may seem very confusing, but what's most important is to concentrate on how you can use the classes, rather than the detail hidden inside them. That's one of the joys of object oriented programming, once you get used to it. The code can get very complex behind the scenes, but all you need to concern yourself with is the simple "interface" (API) with which your code uses the class.

About APIs

It's common to hear the term API mentioned around classes. API stands for Application Programming Interface. What it refers to is the set of methods that act as "doors" to the functionality contained within a class. A well-designed API will allow the developer of the class to make radical changes behind the scenes without breaking any of the code that uses the class.

Compare using the MySQL classes with the earlier procedural code; it should be easy to see the similarities. Given that it's so similar, you may ask, "Why not stick to plain, procedural PHP?" Well, in this case, it hides many of the details associated with performing the query. Tasks like managing the connection, catching errors, and deciding what format to get the query results in are all handled behind the scenes by the class. Classes also make the implementation of global modifications (such as switching from MySQL to PostgreSQL) relatively painless (i.e. you could just switch to a PostgreSQL class that provided the same API).