Article
The PHP Anthology Volume I, Chapter 3 - PHP and MySQL
With that in place, we can use this script (which must be run from the command line) to generate the classes:
Example 3.34. 22.php
<?php
// Builds the DataObjects classes
$_SERVER['argv'][1] = 'db_dataobject.ini';
require_once 'DB/DataObject/createTables.php';
?>
This script automatically creates the class files we need in order to access the database. Here's an example developed for the articles table:
Example 3.35. ExampleApps/DataObject/Articles.php (in SPLIB)
<?php
/**
* Table Definition for articles
*/
require_once 'DB/DataObject.php';
class DataObject_Articles extends DB_DataObject
{
###START_AUTOCODE
/* the code below is auto generated do not remove the above tag
*/
var $__table = 'articles'; // table name
var $article_id; // int(11) not_null primary_key auto_increment
var $title; // string(255) not_null multiple_key
var $intro; // blob(65535) not_null blob
var $body; // blob(65535) not_null blob
var $author; // string(255) not_null
var $published; // string(11)
var $public; // string(1) not_null enum
/* ZE2 compatibility trick*/
function __clone() { return $this;}
/* Static get */
function staticGet($k,$v=NULL) {
return DB_DataObject::staticGet('DataObject_Articles',$k,$v); }
/* the code above is auto generated do not remove the tag below */
###END_AUTOCODE
}
?>
Let's now use this class to access the articles table:
Example 3.36. 23.php
<?php
// Include the DataObjects_Articles class
require_once 'ExampleApps/DataObject/Articles.php';
// Parse the database ini file
$dbconfig = parse_ini_file('db_dataobject.ini', true);
// Load Database Settings
// (note main PEAR class is loaded by Articles.php)
foreach ($dbconfig as $class => $values) {
$options = &PEAR::getStaticProperty($class, 'options');
$options = $values;
}
// Instantiate the DataObject_Articles class
$articles = new DataObject_Articles();
// Assign a value to use to search the 'Author' column
$articles->author = 'Kevin Yank';
// Perform the query
$articles->find();
echo 'Kevin has written the following articles:<br />';
// Loop through the articles
while ($articles->fetch()) {
echo ' - ' . $articles->title . ', published: ' .
date('jS M Y', $articles->published) . '<br />';
}
?>
First of all, where's the SQL? There isn't any—great! The parse_ini_file function is provided by PHP (see Chapter 4, Files for details) and deals with getting the variables from our db_dataobject.ini configuration file. The foreach loop makes the required variables available to DB_DataObject when we instantiate its auto-generated subclass DataObject_Articles. By assigning a value to the author property of the $articles object, we prepare a WHERE condition that DataObject_Articles should use when it queries the database. The query is actually performed by calling the find method (see the DB_DataObject documentation for full details), which in turn executes the following query:
SELECT * FROM articles WHERE articles.author = 'Kevin Yank'
To loop through the results, we use the fetch method. When it's called, fetch populates the properties of the $articles object with the current row result. This allows us to access them again via the property names, as with $articles->title.
Further methods are provided to make the query more complex, for example, the whereAdd method:
Example 3.37. 24.php (excerpt)
// Instantiate the DataObject_Articles class
$articles = new DataObject_Articles();
// Assign a value to use to search the 'Author' column
$articles->author = 'Kevin Yank';
// Add a where clause
$articles->whereAdd('published > ' . mktime(0, 0, 0, 5, 1, 2002));
// Perform the query
$articles->find();
This allows us to add a further condition to the WHERE clause:
SELECT * FROM articles
WHERE published > 1020204000 AND articles.author = 'Kevin Yank'
There are other similar methods, so if these fail to provide what you need, you can use the query method to execute a hand-coded query. Note that if you find yourself needing to use the query method, it may be a good idea to create a subclass of the generated DataObject class, and wrap the query in a useful method name that describes it accurately.
DB_DataObject also deals effectively with table joins, which, although slightly more detailed than the example above, is certainly a time saver when compared with writing complex join queries by hand.
That concludes our short introduction to DB_DataObject, but this section should have given you a taste of what it can do for you. The big advantage is that it makes querying your database with SQL far less exhausting and error-prone. Also, by centralizing access to a particular table in a single class, it helps simplify dealing with changes to the table structure.
Further Reading
- Beginning MySQL This article provides a solid summary of how to use SQL with MySQL.
- Give me back my MySQL Command Line! Kevin Yank shows how to put together a PHP script which can be used to simulate the MySQL command line via a Web page.
- Optimizing your MySQL Application This handy tutorial discusses the use of indexes in MySQL and how they can be used to improve performance.
- Generating PHP Database Access Layers This article provides an overview of persistence layers and related code generation with pointers to some useful tools.
- Zend Tutorial on Fulltext Searches This tutorial provides a detailed look at FULLTEXT searches.
- Getting Started with MySQL Fulltext Searches This good tutorial delivers another detailed look at FULLTEXT searching.
- Backing Up with MySQLDump This tutorial that explores the ins and outs of the mysqldump utility.
Look out for more chapters from The PHP Anthology on SitePoint in coming weeks! If you can't wait, download the sample chapters, or order your very own copy now!