Dynamically creating the LIKE part of a query - PHP

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.