Object-oriented query building can sometime seem like more of a hassle than raw SQL, but once mastered, it can be much more powerful and sometimes lead to code that is easier to understand. In addition to escaping parameters automatically as they are added, a single Query object can be used to select, delete, or count rows that match its conditions.

Basic Select Statement



require_once('config.php');

//Create new instance of Query
$q = new Query;

//Add search criteria
$q->add('Author',$_POST['Author']);

//Retrieve all the books from the database that match the query criteria
$allBooks = Book::doSelect($q);

//Loop through the array and print the book Titles
foreach($allBooks as $b){
	echo $b->getTitle()."";
}

?>

Advanced Select Statement Using the Condition class

This demonstrates how to use the Query and Condition classes to create an advanced SELECT statement with specific blocks surrounded by (). In this example the Query will be in the form of a && ( b || c ), where a, b and c are equality conditions (i.e. Title=’1′).



require_once('config.php');

//Create new instance of Query
$q = new Query;

//Add search criteria
$q->add('Author', 'Michael Crichton', 'LIKE');

//Create new instance of Condition
$c = new Condition;

//Add search criteria to condition
$c->add('Title', 'Jurassic Park');
$c->addOr('Title', 'Congo');

//Add the condition to the Query
//The condition will be wrapped in ( ) when added to the Query.
//This can be done with add() or addOr().
$q->add($c);

/*
 * Query WHERE clause should now be something like the following:
 * "WHERE `Author` LIKE 'Michael Crichton' AND (`Title`='Jurassic Park' OR `Title`='Congo')"
 */

//Retrieve all the books from the database that match the query criteria
$allBooks = Book::doSelect($q);

//Loop through the array and print the book Titles
foreach($allBooks as $b){
	echo $b->getTitle()."";
}

?>

You can add criteria to the Condition object the same way you add it to the Query object. In fact, an instance of the Query class is just a wrapper for an instance of the Condition class. When you use the add() or addOr() methods of a Query object, it just passes the parameters to its own Condition object. When a Condition is passed to the add() or addOr() of a Condition object, it simply takes the SQL from that Condition, wraps it in (), and adds it to it’s own SQL. This enables you to create very complex conditions using objects.

Joining Tables



require_once('config.php');

//Create new instance of Query
$q = new Query('Book b');

//Join the Author table
$q->join('Author a', 'a.AuthorID = b.AuthorID');

//Add search criteria
$q->add('a.FirstName', 'Jimmy')

//Retrieve all the books from the database that match the query criteria
$allBooks = Book::doSelect($q);

?>

(503) 746-9116 | 17933 NW Evergreen Parkway, Suite 220 | Beaverton, Oregon 97006