Building a MySQL Abstraction Class with Method Chaining

In this fourth part of a 12-part series on method chaining, I start building a basic MySQL abstraction class that implements a few straightforward methods. Of course, the methods can be easily chained to each other, which permits us to build different parts of a SELECT statement through a truly compact and readable API.

Put it in a simple way, method chaining is a handy programming methodology that allows you to create classes whose methods can be easily linked to each other, thus building a highly compact API.

Building chainable methods is not a proprietary approach supported by a particular programming language. It can be applied to multiple languages. In the case of PHP 5, it is very simple to implement, even if you have an average background in the object-oriented paradigm.

Therefore, if you’re a truly passionate PHP developer who wants to learn the basics of building chainable methods and how to use them in real-world applications, then don’t hesitate any longer; start reading this series of articles now!

Right now I’m going to review the topics that were discussed in the previous part. In that tutorial I finished building a simple string processor class, which could apply different filters to an incoming string.

Of course, apart from the simplistic logic implemented by the methods of this sample class, the most important aspect to stress here is that these methods were completely chainable, thus making it possible to invoke them by writing only a few lines of short, tight code.

However, this class was built only as an introductory example, handy for learning the fundamentals of applying method chaining in PHP 5. But this approach can be used for developing more useful applications. So, with that idea in mind, in the next few lines I’m going to explain how to create a simple MySQL abstraction class whose methods will be chainable.

Now it’s time to see how to use method chaining to abstract access to MySQL. Let’s get going!

{mospagebreak title=Building a MySQL abstraction class}

The first step that I’m going to take in building a simple MySQL abstraction class whose methods will be entirely chainable will consist of defining and initializing its properties, as well as implementing its constructor.

Having said that, the starting definition of this class, which I decided to call simply “MySQL,” looks 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());

}

}

}

Well, if you pay close attention to the signature of the above “MySQL” class, you’ll surely notice that it’s extremely easy to grasp. For the time being it only defines the constructor, which in this case is tasked with connecting to the database server and throwing an exception if this process fails for whatever reason.

In addition, it’s fair to point out that I decided to use the “mysqli” PHP extension to perform all of the tasks related to MySQL, but you can use the old MySQL extension to do the same things if you feel more comfortable working with it.

So far, everything looks pretty good, right? At this stage, I built a PHP 5 class that’s only capable of establishing a connection to the MySQL server. So, the question now is, where do chainable methods come in? Well, don’t worry; in the following segment I’m going to add another method to the previous “MySQL” class, which will be tasked with building the SELECT part of a SQL query — and  will be chainable as well.

To see how this method will be implemented, click on the link below and keep reading.

{mospagebreak title=Defining a chainable method to perform SELECT statements}

In the previous section, I defined and implemented the constructor of the MySQL abstraction class, a process that should be pretty understandable to you. Now, it’s time to extend the class’s current functionality by adding to it a brand new chainable method for creating the SELECT portion of a query.

The implementation of this method, called “select(),” is show below. Take a look at it:

// create the SELECT part of the query

// chainable

public function select($table, $fields = ‘*’)

{

$this->query = ‘ SELECT ‘ . $fields . ‘ FROM ‘ . $table;

return $this;

}

See how simple it is to define a method that builds the SELECT part of a SQL query? I guess you do. In this specific case, the method not only will create that portion of the query according to the specified fields, but since it returns an instance of the MySQL class, it can be linked easily to others as well.

But I’m getting ahead of myself; please pay attention to the following code sample, which shows the signature of the sample “MySQL” class, this time including the “select()” method:

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;

}

}

The above “MySQL” class is beginning to take shape, even though in its current state it is still quite basic. However, this is about to change; in the next section I’m going to add to it yet another chainable method, which will be responsible for building the WHERE modifier of a SQL query.

To learn how this will be accomplished, please click on the link below and read the next few lines.

{mospagebreak title=Building the WHERE part of a query}

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!

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

chat sex hikayeleri