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

Working with ORDER BY clauses - 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 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.



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