Completing the MySQL Class with Method Chaining

Among the numerous features provided by PHP 5, there’s one that many developers find appealing. It permits the building of compact and modular programming interfaces. Yes, as you may have guessed, in this specific case I’m talking about method chaining, a programming approach can be easily mastered by those with an average background in the object-oriented paradigm. This is the sixth part of a 12-part series on method chaining.

Indeed, learning how to create chainable methods in PHP 5 class is a pretty straightforward process that will make your own classes more compact and modular as well. Therefore, I suggest you read this series of articles; in it, you’ll find an approachable guide that will show you the basics of using method chaining and how to implement this approach for developing real-world programs.

And now that you’re aware of the main goal of this series, it’s time to review the topics that were discussed in the last article, as a quick reminder. In that installment I showed how to add a simple chainable method to the MySQL abstraction class previously developed. It was used for creating the “ORDER BY” part of a SELECT statement.

Defining a method like this allowed us to retrieve database records by using only a few lines of compact code. Now I’d like to finish building this sample MySQL abstraction class by adding to it two final chainable methods. The first one will be charged with building the “LIKE” clause of SELECT queries, while the second method will be a factory responsible for returning to client code only singletons of this class.

Want to see how these two useful methods will be defined? Then click on the link that appears below and keep reading.

{mospagebreak title=Review: creating ORDER BY SQL clauses with chainable methods}

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.

{mospagebreak title=Dynamically creating the LIKE part of a query}

As I anticipated in the section that you just read, the next step that I’m going to take involves defining yet another chainable method within the MySQL abstraction class. It will be tasked with building the LIKE clause of a SELECT statement.

The implementation of this method will be a bit more complex than the others, but don’t worry, it’ll still be quite simple to grasp. So let’s see how this particular method looks. Here it is:

// create the LIKE part of the query

// chainable

public function like($match = ”, $wildcard = ‘both’)

{

if ($match != ”)

{

if ($wildcard === ‘both’)

{

$this->query .= ‘ LIKE ‘ . ”%’ . $match . ‘%”;

}

else if ($wildcard === ‘pre’)

{

$this->query .= ‘ LIKE ‘ . ”%’ . $match . ”';

}

else if ($wildcard === ‘post’)

{

$this->query .= ‘ LIKE ‘ . ”’ . $match . ‘%”;

}

}

return $this;

}

As depicted above, the “like()” method builds the LIKE clause of a query, and it takes two arguments: the string to be matched, and one that is used internally to specify the position of the wildcard within the clause. Not too hard to understand, right?

Now, after implementing this new method, the full source code of the “MySQL” abstraction class 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;

}

 

// create the ORDER BY part of the query

// chainable

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

{

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

 return $this;

}

 

// create the LIKE part of the query

// chainable

public function like($match = ”, $wildcard = ‘both’)

{

if ($match != ”)

{

if ($wildcard === ‘both’)

{

$this->query .= ‘ LIKE ‘ . ”%’ . $match . ‘%”;

}

else if ($wildcard === ‘pre’)

{

$this->query .= ‘ LIKE ‘ . ”%’ . $match . ”';

}

else if ($wildcard === ‘post’)

{

$this->query .= ‘ LIKE ‘ . ”’ . $match . ‘%”;

}

}

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 now that you have at your disposal the complete signature of the MySQL abstraction class after implementing the “like()” method, here’s an example of how to use 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’, ‘post’)->fetch();

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

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

}

}

catch(Exception $e)

{

echo $e->getMessage();

exit();

}

There you have it. Now, the “MySQL” class is capable of performing conditional SELECT queries by chaining three different, highly modular methods. Of course, it’s also feasible to create a few others, but I’ll leave this task as homework for you.

However, there’s a task that remains undone that I mentioned in the introduction. It will definitely be very useful to create a factory method within the MySQL abstraction class that returns singletons and that can be chained to other methods.

That will be the conclusion for this article. The details of this process will be discussed in the section to come. So, click on the link below and keep reading.

{mospagebreak title=Adding a chainable factory method to the MySQL class}

Certainly, a good epilogue for this article will be defining a factory method within the MySQL abstraction class that returns to client code only singletons and additionally, can be easily chained to other methods.

Below I listed the full source code corresponding to the “MySQL” class, this time including the factory method. Please look at it:

class MySQL

{

private $host = ”;

private $user = ”;

private $password = ”;

private $database = ”;

private $query = ”;

private $result;

private $link = NULL;

private static $instance = NULL;

 

// factory method returns a singleton of MYSQL class

public static function factory($host, $user, $password, $database)

{

if (self::$instance === NULL)

{

self::$instance = new MySQL($host, $user, $password, $database);

}

return self::$instance;

}

 

// 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;

}

 

// create the LIKE part of the query

// chainable

public function like($match = ”, $wildcard = ‘both’)

{

if ($match != ”)

{

if ($wildcard === ‘both’)

{

$this->query .= ‘ LIKE ‘ . ”%’ . $match . ‘%”;

}

else if ($wildcard === ‘pre’)

{

$this->query .= ‘ LIKE ‘ . ”%’ . $match . ”';

}

else if ($wildcard === ‘post’)

{

$this->query .= ‘ LIKE ‘ . ”’ . $match . ‘%”;

}

}

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;

}

 

// implement the destructor to free result set and close the database connection

function __destruct()

{

mysqli_free_result($this->result);

mysqli_close($this->link);

}

}

If you’re familiar with implementing the Factory pattern in PHP, you’ll find the previous “factory()” method pretty easy to follow. All it does is create a Singleton of the “MySQL” class; as mentioned, it can also be chained to other methods.

The following example shows how to retrieve records from the same “properties” MySQL table used in previous cases, by chaining the factory method to others:

try

{

// fetch result set by chaining methods of MySQL class

$result = MySQL::factory(‘host’, ‘user’, ‘password’, ‘database’)->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();

}

Hopefully, this simple example gave you an accurate idea of how to take advantage of method chaining in PHP 5 for abstracting MySQL-related tasks through a highly compact API. As always, feel free to edit all of the code samples shown in this tutorial, so you can arm yourself with a better understanding of how to work with this useful programming methodology.

Final thoughts

That’s all for the moment. In this sixth chapter of the series, I demonstrated how simple it is to add a couple of chainable methods within the previous “MySQL” class for dynamically creating LIKE clauses and for returning Singletons as well. Hopefully, the development of this class will help you understand more clearly how to use method chaining for building a real-world application.

Speaking of developing real-world applications, in the next part I’m going to explain how to use chainable methods for creating a custom library for CodeIgniter, the popular PHP framework.

Don’t miss the upcoming article!

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye