Review: dynamically building SQL queries with method chaining - PHP

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.