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

Review: performing CRUD operations through the active record approach - 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

You may wonder how we're going to provide the MySQL abstraction class built in the previous article with the ability for performing conditional SELECT statements using the active record pattern. Before I proceed to discuss that particular topic, however, it would be useful to list the signature of this class in its current state. As I mentioned, it was capable of performing only CRUD tasks (Create, Remove, Update, Delete).

That being said, here’s how this MySQL-accessing class looked originally:


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;

}

// 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 illustrated above, the previous “MySQL” class implements a few straightforward methods for fetching, inserting, updating and deleting rows in a specified MySQL table. As I said before, all of these operations are performed without the need to directly code any SQL commands, as you can see clearly in the code sample above.

Now that you've recalled how this basic “MySQL” class was defined, here’s a short example that shows how it can be used for consecutively adding and deleting one single row from a fictional “users” MySQL table:


try{

// connect to MySQL and select a database

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

// insert new row into sample MySQL table

$db->insert(array('firstname'=>'Kate','lastname'=>'Johanson','email'=>'kate@domain.com'),'users');

// delete row from sample MySQL table

$db->delete('id=4','users');

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


Undoubtedly, the above hands-on example demonstrates in a nutshell how to use the “MySQL” class for inserting a new row into a sample “users” MySQL table, and how to delete it later on, by means of its respective “insert()” and “delete()” methods. This process is actually very simple to follow.

Well, at this point you hopefully recalled how to use the previous class to perform CRUD operations via the active record approach. However, as I mentioned in the introduction, it’s perfectly possible to incorporate many other methods into the class to extend its existing functionality.

Bearing this idea in mind, in the section to come I’ll be adding a brand new method to the sample MySQL class. In doing so, it’ll be able to perform simple conditional SELECT queries, without coding any explicit SQL statements.

To learn the full details of how this new method will be defined, please click on the link that appears below and read the next few lines.



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