Building the WHERE part of a query - PHP

Since in the previous part you learned how to implement a chainable method within the sample “MySQL” class, which was charged with creating the SELECT section of a SQL query, I’m going to define another method that will be tasked with building the WHERE modifier.

Of course, this new method will be chainable, and its implementation can be seen below. Here it is:

// create the WHERE part of the query

// chainable

public function where($where)

{

$this->query .= ' WHERE ' . $where;

return $this;

}

That’s not brain surgery, isn’t it? As show above, the previous “where()” method is responsible for creating the WHERE part of a SQL statement, and at the same time, it returns to calling code an instance of the MySQL abstraction class, which permits it to link it to other methods quickly and painlessly.

At this stage, assuming that you already understand how the “where()” method does its thing, it’s time to try the “MySQL” class by using the couple of chainable methods defined so far. But wait a minute! Since these methods build different parts of a “retrieval” SQL operation, it’s necessary to define another one that puts these parts together and executes that operation as well.

Based on this requirement, below I coded such a method, which is called “fetch().” It looks like 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 there’s a “fetch()” method that really runs the dynamically-generated query and returns a result set when applicable. So, now the signature of the MySQL abstraction class, after including the group of methods defined previously, will look like this:

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;

}

}

Definitely, at this point the MySQL abstraction class looks a bit more functional. It's now capable of performing SELECT statements by using three different methods, where two of them can be chained to each other. But the best way to see how they work is by means of an example. Thus, I developed a simple one for you, which retrieves some rows from a fictional MySQL table containing some real estate properties and displays the data on screen. Take a 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 'h%'")->fetch();

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

echo $property->title . $property->short_description . '<br />';

}

}

catch(Exception $e)

{

echo $e->getMessage();

exit();

}

There you have it. As you can see above, by using only a few lines of tight code, I created a simple application that fetches some properties from a sample MySQL table. Notice especially how both “select()” and “where()” methods have been chained in the proper sequence to retrieve the database rows, which are displayed on the browser afterward.

At this point, I've provided you with a real-world example that shows how to use method chaining for building a simple database-driven application with PHP 5. As usual, feel free to edit all of the code samples included in this tutorial and introduce your own enhancements to the MySQL abstraction class. You’ll have a great time, trust me.

Final thoughts

In this fourth installment of the series, I started building a basic MySQL abstraction class that implemented a few straightforward methods. Of course, the most relevant thing to consider in this particular case is that the methods in question can be easily chained to each other, which permits you to build different parts of a SELECT statement through a truly compact and readable API.

In the next part of the series, things will become even more interesting. I’m going to continue adding more methods to this sample class, which also will be completely chainable. Therefore, now that you’re aware of the topics that will be covered in the upcoming tutorial, you won't want to miss it!