Home arrow PHP arrow Page 3 - The LIKE Clause and the Active Record Pattern

Working with LIKE clauses - PHP

In most cases, the implementation of the active record pattern in PHP (and other programming languages too) is carried out through a few data mapper objects, which are used to perform CRUD operations on a group of targeted database tables. This seven-part article series describes the advantages of using the active record pattern in a variety of situations, and shows you how to do it.

TABLE OF CONTENTS:
  1. The LIKE Clause and the Active Record Pattern
  2. Review: conditional SELECT queries with the active record pattern
  3. Working with LIKE clauses
  4. The fetchLike() method in action
By: Alejandro Gervasio
Rating: starstarstarstarstar / 2
March 24, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

By using an approach similar to the one for performing conditional SELECT statements, it’s also possible to dynamically construct SQL queries that include a typical LIKE clause. In this case, to demonstrate this concept more clearly, I’m going to add a new method to the previous “MySQL” class. This will let you see how it can be used to execute LIKE statements in a concrete situation.

That being explained, please pay attention to the enhanced signature of this sample class, which now looks like this:


class MySQL{

private $result;

public function __construct($host='localhost',$user='user',$password='password',$database='database'){

// connect to MySQL and select database

if(!$conId=mysql_connect($host,$user,$password)){

throw new Exception('Error connecting to the server');

}

if(!mysql_select_db($database,$conId)){

throw new Exception('Error selecting database');

}

}

// run SQL query

public function query($query){

if(!$this->result=mysql_query($query)){

throw new Exception('Error performing query '.$query);

}

}

// fetch one row

public function fetchRow(){

while($row=mysql_fetch_array($this->result)){

return $row;

}

return false;

}

// fetch all rows

public function fetchAll($table='default_table'){

$this->query('SELECT * FROM '.$table);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// fetch rows using 'WHERE' clause

public function fetchWhere($where,$table='default_table'){

$this->query('SELECT * FROM '.$table.' WHERE '.$where);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// fetch rows using 'LIKE' clause

public function fetchLike($field,$like,$table='default_table'){

$this->query('SELECT * FROM '.$table.' WHERE '.$field.' LIKE '.$like);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}

// insert row

public function insert($params=array(),$table='default_table'){

$sql='INSERT INTO '.$table.' ('.implode(',',array_keys($params)).') VALUES (''.implode("','",array_values($params)).'')';

$this->query($sql);

}

// update row

public function update($params=array(),$where,$table='default_table'){

$args=array();

foreach($params as $field=>$value){

$args[]=$field.'=''.$value.''';

}

$sql='UPDATE '.$table.' SET '.implode(',',$args).' WHERE '.$where;

$this->query($sql);

}

// delete one or multiple rows

public function delete($where='',$table='default_table'){

$sql=!$where?'DELETE FROM '.$table:'DELETE FROM '.$table.' WHERE '.$where;

$this->query($sql);

}

}


As shown above, the “MySQL” class now implements a brand new method, called “fetchLike().” It is used for performing SELECT statements using the “LIKE” clause. The implementation of this method is included below:


// fetch rows using 'LIKE' clause

public function fetchLike($field,$like,$table='default_table'){

$this->query('SELECT * FROM '.$table.' WHERE '.$field.' LIKE '.$like);

$rows=array();

while($row=$this->fetchRow()){

$rows[]=$row;

}

return $rows;

}


In this case, the method permits you to run queries without having to directly write any SQL commands, thus abstracting the process considerably. Naturally, there’s plenty of room to improve the logic of the “fetchLike()” method since it’s  pretty simplistic, but the most important thing here is that you grasp how it functions.

So far, so good. At this point, you've hopefully learned how to use the active record approach (or a modified version of it, to be fair) to work with LIKE clauses. However, if you’re like me, you want to see a practical example where the method can be tested.

Therefore, in the following section I’ll be setting up this example for you, so please jump forward and read the next segment. It’s only one click away.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: