Working with ORDER BY clauses - PHP

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.