Home arrow PHP arrow Page 4 - Using the Active Record Pattern with PHP and MySQL

Inserting database table rows with the active record pattern - PHP

Using the active record pattern to develop a database-driven application offers many advantages. It can save PHP developers a wealth of time in coding and code maintenance, because you can take advantage of SQL abstraction. This first part of a series will walk you through the basics of using the active record pattern.

TABLE OF CONTENTS:
  1. Using the Active Record Pattern with PHP and MySQL
  2. Building a simple data mapper in PHP
  3. Building a basic MySQL abstraction class
  4. Inserting database table rows with the active record pattern
By: Alejandro Gervasio
Rating: starstarstarstarstar / 13
March 03, 2009

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Actually, providing the previous “MySQL” abstraction class with the ability to insert a new database row via the active record pattern is only a matter of coding a method that constructs internally an “INSERT” SQL statement and nothing else.

To illustrate this concept more clearly, below I included the signature of this class, this time including a method that inserts new records into a specified MySQL table. Take a look at it, please:


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

}

}


Definitely, things are getting more interesting now. As you can see, the above “MySQL” class incorporates a brand new method called “insert(),” which can be used for adding new rows to a specified MySQL table.

It’s clear to see how the method internally constructs an “INSERT” SQL statement, by using the array of input parameters that is passed into it, along with the specified MySQL table. Nonetheless, to dissipate any possible doubts that you might have regarding how the “insert()” method works, please take a close look at the following example:


try{

// connect to MySQL and select a database

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

// insert new row into selected MySQL table

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

}

catch(Exception $e){

echo $e->getMessage();

exit();

}


As shown above, that’s all the code required to add a new record to the sample “users” MySQL table by means of the active record pattern. In this case, the above “insert()” method hides from the outside all of the complexity demanded by the insertion operation, without dealing directly with SQL code.

With this last example, I’m finishing this introductory tutorial on using the active record pattern within a MySQL abstraction class. As I explained in the beginning, the approach that I used here to apply the pattern differs from using data mapper objects, but it can be easier to implement.

Final thoughts

In this first installment of the series, I demonstrated how to apply the active record pattern in PHP 5, by means of a basic MySQL abstraction class. In this case, the class not only abstracts common tasks, such as connecting to the server and selecting a specific database, but selecting and inserting rows, since all of these operations were handled behind the class API.

In the upcoming article, I’m going to extend the functionality of this sample class to provide it with the capacity for updating and deleting database records without having to write a single portion of SQL code. You won't want to miss the next tutorial!



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