Building Dynamic Queries with Chainable Methods

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.

And now that you have been gently introduced to the main goal of this multi-part series, it’s time to quickly summarize the topics that were discussed in the last installment.

In that article I explained how to create a simple, yet useful MySQL abstraction class. It was composed of a few straightforward methods for performing SELECT statements, accompanied by common query modifiers, such as the WHERE clause.

Of course, the most relevant detail to stress here with reference to the way that this class was built is that many of its methods were chainable. This made it possible to create and execute dynamic retrieval queries simply by linking one method to another. Period.

However, it’s also possible to add more chainable methods to this sample MySQL abstraction class to extend its existing functionality. That’s exactly what I’m going to do in the next few lines: implement an additional chainable method that will permit you to easily couple other typical query modifiers, like the ORDER BY clause to a SELECT statement.

Now, it’s time to leave the preliminaries behind and continue turning the “MySQL” class mentioned previously into a more functional element. Let’s do it right now!

{mospagebreak title=Review: dynamically building SQL queries with method chaining}

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.

{mospagebreak title=Working with ORDER BY clauses}

In reality, defining a method for the previous “MySQL” class that permits us to build the “ORDER BY” part of a SELECT statement is a simple process that you’ll surely grasp with minor effort.

Below you’ll find the definition of the method, which not only allows you to create the mentioned section of a SELECT query, but guess what? It’s chainable too! Take a look at it, please:

// create the ORDER BY part of the query

// chainable

public function orderby($order = ‘id ASC’)

{

 $this->query .= ‘ ORDER BY ‘ . $order;

 return $this;

}

As you can see, the above “orderby()” method annexes an “ORDER BY” clause to an existing query, and also returns to client code an instance of the “MySQL” class. This allows it to link it with other methods very easily.

After implementing this brand new chainable method, the signature of the MySQL abstraction will be as follows:

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;

}

 

// create the ORDER BY part of the query

// chainable

public function orderby($order = ‘id ASC’)

{

 $this->query .= ‘ ORDER BY ‘ . $order;

 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;

}

}

Definitely, it must be admitted that the “MySQL” class shown above is starting to look much more functional and solid, even though it still can’t be used in production environments. Nonetheless, it does show in a nutshell how method chaining can be applied to developing a real-world PHP application.

So, with that idea in mind, I’m going to finish this tutorial of the series by coding another example that will demonstrate how to retrieve ordered records from a MySQL table by means of the “orderby()” method just defined.

To see how this last example will be developed, click on the link below and read the final section of this article.

{mospagebreak title=Fetching database rows with the chainable orderby() method}

As I said in the section that you just read, the best way to understand how to use the chainable “orderby()” method created in the previous section is by way of a concrete example. Therefore, below I included a simple code sample that shows how to fetch some ordered records from a MySQL table by using the aforementioned method. Look at it:

try

{

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

// fetch result set by chaining methods of MySQL class

$result = $db->select(‘properties’)->where("title LIKE ‘c%’")->orderby(‘title DESC’)->fetch();

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

echo $row->title . $row->description. ‘<br />’;

}

}

catch(Exception $e)

{

echo $e->getMessage();

exit();

}

As you can see, the previous code snippet first connects to MySQL, and then retrieves a group of rows from a “properties” MySQL table. Naturally, the most important detail to note here is that the retrieval query has been built by chaining three different methods of the “MySQL” class.

Hopefully, this short example will give you a clear idea of how useful method chaining can be for building tight and compact APIs in PHP 5. As with many of my articles on PHP development, feel free to tweak all of the code samples included in this tutorial, which surely will arm you with a more solid knowledge of how to use this programming approach.

Final thoughts

In this fifth part of the series, I extended the existing functionality of the sample MySQL abstraction class by adding to it another chainable method, for creating the ORDER BY portion of SELECT statements.

In the forthcoming installment, I’m going to finish building this class by defining two final methods. The first one will be tasked with creating the LIKE part of a query, while the second one will be responsible for returning Singletons of the class.

So here’s my recommendation: don’t miss the next tutorial!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan