Inserting database table rows with the active record pattern - PHP

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!