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

Review: conditional SELECT queries with the active record pattern - 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

First I’d like to reintroduce the practical example created in the last installment of the series. It demonstrated how to build a MySQL abstraction class capable of performing both basic CRUD database operations and conditional SELECTS.

Originally, the aforementioned example, including the signature of the “MySQL” class looked 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;

}

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

}

}


try{

// connect to MySQL and select a database

$db=new MySQL('host','user','password','mydatabase');

// display users where ID > 5

$result=$db->fetchWhere('id>5','users');

foreach($result as $row){

echo $row['firstname'].' '.$row['lastname'].' '.$row['email'].'<br />';

}


/* displays the following


Amanda Bears amanda@domain.com
Jodie Foster jodie@domain.com
Laura Linney laura@domain.com
Alice Dern alice@domain.com
Jennifer Aniston jennifer@domain.com


*/


}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Now that you’ve examined the above hands-on example, you should be familiar with the logic implemented by the previous class to insert, update and delete rows in a specified MySQL table using the active record approach.

In addition to executing the task mentioned before, it’s worth noting that this sample class can run conditional SELECT queries by means of a highly-abstracted interface, in this case implemented via the “fetchWhere()” method.

At this point, and assuming that you've grasped how the “MySQL” class works, it’s time to continue extending its functionality. So, in accordance with the concepts deployed in the introduction, in the next few lines I’m going to show you how to adapt the class in question so it can work with SQL LIKE clauses.

This interesting topic will be discussed in the following section, so click on the link below and keep reading.



 
 
>>> 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: