Home arrow PHP arrow Page 3 - Running Conditional Select Statements with the Active Record Pattern

Performing conditional SELECTS through the active record pattern - PHP

If you’re a PHP programmer who wants to learn how to implement the active record pattern within a MySQL abstraction class to make your database-driven applications more robust and maintainable, then look no further. Welcome to the third part of a series focusing on the active record pattern. Made up of seven articles, this series explains how to take advantage of the functionality provided by this popular design pattern to build an extensible MySQL-accessing class. This class will allow you to perform several queries without explicitly writing any SQL statements.

TABLE OF CONTENTS:
  1. Running Conditional Select Statements with the Active Record Pattern
  2. Review: performing CRUD operations through the active record approach
  3. Performing conditional SELECTS through the active record pattern
  4. Running conditional SELECTS using the sample MySQL class
By: Alejandro Gervasio
Rating: starstarstarstarstar / 3
March 17, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

In reality, providing the previous “MySQL” class with the ability to perform conditional SELECT statements using the active record pattern only involves coding a brand new method, which will carry out this task behind the scenes. Basically, the method will look very similar to the “fetch()” that you learned before, except that in this case, it’ll append a WHERE clause to the end of the SELECT command.

However, the best way for you to understand how this method will work is by showing you its corresponding signature, which looks like this:


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

}


Didn’t I tell you that this new method would be very simple to grasp? And if you study its implementation, you’ll have to agree with me on this. But let me explain how it works, in case you have some trouble understanding its logic.

As you can see, it looks nearly identical to the “fetch()” method, except that this one aggregates a WHERE clause to the end of the SQL query, in this way constructing the conditional SELECT.

Of course, if you examine in detail how this method functions, then you’ll realize that it can be improved, but its current functionality is more than enough to demonstrate how simple it is to define a method like this.

At this stage, I’m sure that you now understand how the above “fetchWhere()” method does its thing, so it’s time to list the enhanced version of the “MySQL” class, this time including this method. Here it is:


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

}

}


At this point, the “MySQL” class is indeed quite useful, since it’s capable not only of performing CRUD database operations, but also has the ability to execute conditional SELECTS. Even so, there’s one thing that remains undone; it’s necessary to develop an example that shows the actual functionality of the  “fecthWhere()” method.

The last section of this tutorial will be dedicated to setting up such an example, so click on the link that appears below and keep reading. We’re almost finished!



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