Review: creating ORDER BY SQL clauses with chainable methods - PHP

Before I explain how to add two chainable methods to the "MySQL" class created in the preceding article of the series, it'd be useful to recall how it looked originally, after implementing its "orderby()" method.

Having said that, here's the full source code of the sample abstraction class. Look at it, please:

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;

}

}

Now, does the signature of the above "MySQL" class ring any bells for you? Sure it does! So, it's time to see how it can be utilized for fetching a group of ordered records from a hypothetical "properties" database table by using some of its chainable methods.

The example that shows how to accomplish this specific task is as follows:

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();

}

From the previous code sample, it's clear to see how useful the method chaining approach can be for developing compact and tight APIs. In this particular case, some real estate data is fetched by chaining some methods of the "MySQL" class, which takes only two lines of code.

Nonetheless, it's fair to point out that the above sample appends within the "where()" method a LIKE clause to fetch all of the database records whose "description" field begins with a "c" character.

Obviously, it's possible to code a separate method that creates the LIKE part of a SELECT query, which will be chained to the others as well. So, with that idea in mind, in the next section I'm going to define a method like this, which will be very easy to grasp, trust me.

To learn how this brand new chainable method will be created, read the following segment. It's only one click away.