Home arrow PHP arrow Page 2 - Building Dynamic Queries with Chainable Methods

Review: dynamically building SQL queries with method chaining - PHP

Welcome to the fifth part of a 12-part series focused on method chaining in PHP 5. Through a set of comprehensive and easy-to-follow tutorials, this series of articles shows you how to create and use chainable methods within your own classes. It also teaches you how to implement this useful programming methodology for developing real-world web applications.

TABLE OF CONTENTS:
  1. Building Dynamic Queries with Chainable Methods
  2. Review: dynamically building SQL queries with method chaining
  3. Working with ORDER BY clauses
  4. Fetching database rows with the chainable orderby() method
By: Alejandro Gervasio
Rating: starstarstarstarstar / 1
November 04, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In the previous article of the series, I demonstrated how to apply the method chaining approach to building a basic MySQL abstraction class. As a review, I’ve included its full source code below, along with an example of how to use it. This way, you'll quickly grasp its driving logic.

First, here’s the signature of the “MySQL” class:

class MySQL

{

private $host = '';

private $user = '';

private $password = '';

private $database = '';

private $query = '';

private $result = NULL;

private $link = NULL;

private static $instance = NULL;

 

// constructor

public function __construct($host, $user, $password, $database)

{

if (FALSE === ($this->link = mysqli_connect($host, $user, $password, $database)))

{

throw new Exception('Error : ' . mysqli_connect_error());

}

}

 

// create the SELECT part of the query

// chainable

public function select($table, $fields = '*')

{

$this->query = ' SELECT ' . $fields . ' FROM ' . $table;

return $this;

}

 

// create the WHERE part of the query

// chainable

public function where($where)

{

$this->query .= ' WHERE ' . $where;

return $this;

}

 

// fetch result set

public function fetch()

{

if (FALSE === ($this->result = mysqli_query($this->link, $this->query)))

{

throw new Exception('Error performing query ' . $this->query);

}

return $this->result;

}

}

And here’s a simple demonstration of how to use the class to retrieve some records from a MySQL table populated with some real estate properties:

try

{

$db = new MySQL('host', 'user', 'password', 'database');

// fetch result set by chaining methods of MySQL class

$result = $db->select('properties')->where("title LIKE 'h%'")->fetch();

while ($row = mysqli_fetch_object($result)) {

echo $row->title . $row->short_description . '<br />';

}

}

catch(Exception $e)

{

echo $e->getMessage();

exit();

}

Undoubtedly, the previous example speaks for itself about how compact and tight the code of a PHP application can be, when the power of method chaining is leveraged. The implementation of this approach allows you to create methods that are loosely coupled and much more modular.

At this point, having reviewed how to work with the MySQL abstraction class developed in the previous part of the series, it’s time to continue extending its  functionality. Thus, I’m going to define and implement another chainable method within the “MySQL” class, for adding an “ORDER BY” clause to a SELECT statement. You've probably done this hundreds of times before, but by means of a unique class method.

Therefore, if you’re interested in learning the full details for how this new chainable method will be created in a few simple steps, click on the link that appears below and read the section to come.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- 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: