Hi, I am

Ngô Tôn

I am a programmer.

Home / Programming / Web Development / PHP / A PDO Active Record Class for PHP

A PDO Active Record Class for PHP

Trước khi đọc bài này, các bạn nên tham khảo trước bài Tự xây dựng PHP Framework mô hình MVC.
Và cần nắm kiến thức về Cơ sở dữ liệu SQL để tạo ra được 1 PDO Active Record.

Nếu bạn là một PHP Developer, chắc hẳn bạn đã rất quen thuộc với việc truy xuất Database (Cơ sở dữ liệu) bằng các extensions MySQLMySQLi. Từ PHP 5.1 ta có một cách thức tối ưu hơn đó là sử dụng PHP Data Objects. PDO cung cấp các cơ chế Prepared StatementsStored Procedures và giúp bạn thao tác với database thông qua các Object (đối tượng) làm cho công việc trở nên hiệu quả, dễ dàng hơn.

Trong khuôn khổ bài viết này, mình sẽ không giới thiệu về PDO mà sẽ hướng dẫn các bạn làm việc với PDO để tạo 1 Active Record Class.

/application/model_base.class.php


<?php

class baseModel {

private static $instance;



public $host;

public $db_name;

public $db_username;

public $db_password;

public $dbh;



/*

* Khởi tạo kết nối database

*/

function __construct($config = array('host'=>DB_SERVER,'db_name'=>DB_DATABASE,'db_username'=>DB_USERNAME,'db_password'=>DB_PASSWORD))

{

if(is_array($config))

{

$this->host = $config['host'];

$this->db_name = $config['db_name'];

$this->db_username = $config['db_username'];

$this->db_password = $config['db_password'];

}

try

{

$this->dbh = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->db_username, $this->db_password);

$this->dbh->exec("SET CHARACTER SET utf8");

}

catch (PDOException $e)

{

throw new Exception($e->getMessage());

}

}



/*

* Lấy tên Model

*/

public function get($name){

$file = __SITE_PATH.'/model/'.str_replace("model","",strtolower($name))."Model.php";



if(file_exists($file))

{

include ($file);

$class = str_replace("model","",strtolower($name))."Model";

return new $class;

}

return NULL;

}



public static function getInstance() {

if (!self::$instance)

{

self::$instance = new baseModel();

}

return self::$instance;

}

Hàm thêm dữ liệu vào CSDL.


/*

* Thêm vào CSDL

*/

public function insert($table, $insert)

//Timestamp always set unless otherwise specified

{

// Filter out fields that don't exist

$insert = $this->filter($insert, $table);

//End Filter





$keys = implode(', ', array_keys($insert));

$table_values = implode(", :", array_keys($insert));

$sql = "INSERT INTO $table ($keys) VALUES(:$table_values)";

$query = $this->dbh->prepare($sql);

$new_insert = array();

foreach($insert as $key=>$value)

{

if($value==null)

{

$value = '';

}

$new_insert[":" . $key] = $value;

}

$query->execute($new_insert);

//to check that there is an id field before using it to get the last object

if($this->dbh->lastInsertId())

{

$stmt = $this->dbh->query("SELECT * FROM $table WHERE {$this->getPrimaryKey($table)}='" . $this->dbh->lastInsertId() . "'");

return $stmt->fetch(PDO::FETCH_OBJ);

}

else

//if there isn't, just get the object by fields

{

return $this->getByWhere($table, $insert);

}

}

Hàm cập nhật CSDL.


/*

* Cập nhật CSDL

*/

public function update($table, $insert, $object)

{

$tmp = array();

$conditions = array();

$primaryKey = $this->getPrimaryKey($table);

$insert = $this->filter($insert, $table);



foreach($insert as $key=>$value)

{

$tmp[] = "$key=?";

}

$str = implode(', ', $tmp);



$object = $this->filter($object, $table);



foreach($object as $key=>$value)

{

$conditions[] = "$key=$value";

}

$where = implode(' AND ', $conditions);



$sql = "UPDATE $table SET $str WHERE $where";



$query = $this->dbh->prepare($sql);

$query->execute(array_values($insert));



return $this->dbh->exec($sql);

}

Hàm xóa CSDL.


/*

* Xóa CSDL

*/

public function delete($table, $data)

{



$data = $this->filter($data, $table);

$conditions = array();

foreach($data as $key=>$value)

{

if($value==null)

{

$conditions[] = "$key IS NULL";

unset($data[$key]);

}

else

{

$conditions[] = "$key=?";

}

}

$str = implode(' AND ', $conditions);



$sql = "DELETE FROM $table WHERE $str";

$query = $this->dbh->prepare($sql);

$query->execute(array_values($data));

//var_dump($sql);

return $this->dbh->exec($sql);

}

Hàm truy vấn CSDL.


public function query($sql){

//var_dump($sql);

$query = $this->dbh->prepare($sql);

$query->execute();

return $query->fetchAll(PDO::FETCH_OBJ);

}

Hàm lấy tất cả dữ liệu.


/*

* Lấy tất cả

*/

public function fetchAll($table,$data = array('where','order_by','order','limit'),$join = array('table','where')){

//var_dump($data['order_by']);die();

$where = null;

$order_by = null;

$order = null;

$limit = null;



$table_join = null;

$join_where = null;



if (isset($data['where'])) {

$where = 'WHERE '.$data['where'];

//

}

if (isset($data['order_by'])) {

$order_by = 'ORDER BY '.$data['order_by'];

//

}

if (isset($data['order'])) {

$order = $data['order'];

//

}

if (isset($data['limit'])) {

$limit = 'LIMIT '.$data['limit'];

//

}

if (isset($join['table'])) {

if (!isset($data['where'])) {

$join_where = $join['where'];

$table_join = ', '.$join['table'].' WHERE ';

}

else{

$table_join = ', '.$join['table'];

$join_where = ' AND '.$join['where'];

}



//

}

$sql = "SELECT * FROM $table $table_join $where $join_where $order_by $order $limit";

//var_dump($sql);

$query = $this->dbh->prepare($sql);

$query->execute();

return $query->fetchAll(PDO::FETCH_OBJ);

}

Hàm lấy record cuối cùng.


/*
* Lấy ID cuối cùng
*/
public function getLast($table)
{
$sql = "SELECT * FROM $table ORDER BY ".$table."_id DESC LIMIT 1";
$query = $this->dbh->prepare($sql);
$query->execute();
return $query->fetch(PDO::FETCH_OBJ);
}

Hàm lấy dữ liệu theo ID.


/*
* Lấy theo ID
*/
public function getByID($table, $id)
{
return $this->getByField($table, $this->getPrimaryKey($table), $id);
}

Hàm lấy dữ liệu theo tên cột.


/*
* Lấy theo cột
*/
public function getByField($table, $field, $value, $options = false)
{
$data = array($field=>$value);
return $this->getByWhere($table, $data, $options);
}

Hàm lấy 1 dòng dữ liệu theo điều kiện.


/*

* Lấy 1 dòng theo điều kiện

*/

public function getByWhere($table, $data, $options = false)

{

$data = $this->filter($data, $table);

$conditions = array();

foreach($data as $key=>$value)

{

if($value==null)

{

$conditions[] = "$key IS NULL";

unset($data[$key]);

}

else

{

$conditions[] = "$key=?";

}

}

$str = implode(' AND ', $conditions);

$sql = "SELECT * FROM $table WHERE $str";

if($options)

{

$sql .= ' ' . $options;

}

$query = $this->dbh->prepare($sql);

$query->execute(array_values($data));

return $query->fetch(PDO::FETCH_OBJ);

}

Hàm lấy tất cả dữ liệu theo điều kiện.


/*

* Lấy tất cả theo điều kiện

*/

public function getAllByWhere($table, $data, $options = false)

{

$data = $this->filter($data, $table);

$conditions = array();

foreach($data as $key=>$value)

{

if($value==null)

{

$conditions[] = "$key IS NULL";

unset($data[$key]);

}

else

{

$conditions[] = "$key=?";

}

}

$str = implode(' AND ', $conditions);

$sql = "SELECT * FROM $table WHERE $str";

if($options)

{

$sql .= ' ' . $options;

}

$query = $this->dbh->prepare($sql);

$query->execute(array_values($data));

return $query->fetchAll(PDO::FETCH_OBJ);

}

Hàm kiểm tra cột trong table.


public function filter($insert, $table)

{

$columns = $this-&gt;dbh-&gt;query(&quot;SHOW COLUMNS FROM <code>$table</code>&quot;)-&gt;fetchAll();

$fields = array();

foreach($columns as $row)

{

$fields[$row['Field']] = true;

}



foreach($insert as $key=&gt;$value)

{

if(!isset($fields[$key]))

{

unset($insert[$key]);

}

}



if(count($insert)===0)

{

throw new Exception('At least one field must be passed as data.  Check to make sure fields exist in Database');

}

return $insert;

}

Hàm lấy khóa chính.


public function getPrimaryKey($table)

{

$sql = "SHOW KEYS FROM $table WHERE Key_name = 'PRIMARY'";

$stmt = $this->dbh->query($sql);

$res = $stmt->fetch(PDO::FETCH_OBJ);

return $res->Column_name;

}

 

Và cuối cùng ta được file hoàn chỉnh như sau:

&lt;/pre&gt;
&lt;?php

class baseModel {

private static $instance;



public $host;

public $db_name;

public $db_username;

public $db_password;

public $dbh;



/*

* Khởi tạo kết nối database

*/

function __construct($config = array('host'=&gt;DB_SERVER,'db_name'=&gt;DB_DATABASE,'db_username'=&gt;DB_USERNAME,'db_password'=&gt;DB_PASSWORD))

{

if(is_array($config))

{

$this-&gt;host = $config['host'];

$this-&gt;db_name = $config['db_name'];

$this-&gt;db_username = $config['db_username'];

$this-&gt;db_password = $config['db_password'];

}

try

{

$this-&gt;dbh = new PDO(&quot;mysql:host=&quot; . $this-&gt;host . &quot;;dbname=&quot; . $this-&gt;db_name, $this-&gt;db_username, $this-&gt;db_password);

$this-&gt;dbh-&gt;exec(&quot;SET CHARACTER SET utf8&quot;);

}

catch (PDOException $e)

{

throw new Exception($e-&gt;getMessage());

}

}



/*

* Lấy tên Model

*/

public function get($name){

$file = __SITE_PATH.'/model/'.str_replace(&quot;model&quot;,&quot;&quot;,strtolower($name)).&quot;Model.php&quot;;



if(file_exists($file))

{

include ($file);

$class = str_replace(&quot;model&quot;,&quot;&quot;,strtolower($name)).&quot;Model&quot;;

return new $class;

}

return NULL;

}



public static function getInstance() {

if (!self::$instance)

{

self::$instance = new baseModel();

}

return self::$instance;

}



/*

* Thêm vào CSDL

*/

public function insert($table, $insert)

//Timestamp always set unless otherwise specified

{

// Filter out fields that don't exist

$insert = $this-&gt;filter($insert, $table);

//End Filter





$keys = implode(', ', array_keys($insert));

$table_values = implode(&quot;, :&quot;, array_keys($insert));

$sql = &quot;INSERT INTO $table ($keys) VALUES(:$table_values)&quot;;

$query = $this-&gt;dbh-&gt;prepare($sql);

$new_insert = array();

foreach($insert as $key=&gt;$value)

{

if($value==null)

{

$value = '';

}

$new_insert[&quot;:&quot; . $key] = $value;

}

$query-&gt;execute($new_insert);

//to check that there is an id field before using it to get the last object

if($this-&gt;dbh-&gt;lastInsertId())

{

$stmt = $this-&gt;dbh-&gt;query(&quot;SELECT * FROM $table WHERE {$this-&gt;getPrimaryKey($table)}='&quot; . $this-&gt;dbh-&gt;lastInsertId() . &quot;'&quot;);

return $stmt-&gt;fetch(PDO::FETCH_OBJ);

}

else

//if there isn't, just get the object by fields

{

return $this-&gt;getByWhere($table, $insert);

}

}



/*

* Cập nhật CSDL

*/

public function update($table, $insert, $object)

{

$tmp = array();

$conditions = array();

$primaryKey = $this-&gt;getPrimaryKey($table);

$insert = $this-&gt;filter($insert, $table);



foreach($insert as $key=&gt;$value)

{

$tmp[] = &quot;$key=?&quot;;

}

$str = implode(', ', $tmp);



$object = $this-&gt;filter($object, $table);



foreach($object as $key=&gt;$value)

{

$conditions[] = &quot;$key=$value&quot;;

}

$where = implode(' AND ', $conditions);



$sql = &quot;UPDATE $table SET $str WHERE $where&quot;;



$query = $this-&gt;dbh-&gt;prepare($sql);

$query-&gt;execute(array_values($insert));



return $this-&gt;dbh-&gt;exec($sql);

}



/*

* Xóa CSDL

*/

public function delete($table, $data)

{



$data = $this-&gt;filter($data, $table);

$conditions = array();

foreach($data as $key=&gt;$value)

{

if($value==null)

{

$conditions[] = &quot;$key IS NULL&quot;;

unset($data[$key]);

}

else

{

$conditions[] = &quot;$key=?&quot;;

}

}

$str = implode(' AND ', $conditions);



$sql = &quot;DELETE FROM $table WHERE $str&quot;;

$query = $this-&gt;dbh-&gt;prepare($sql);

$query-&gt;execute(array_values($data));

//var_dump($sql);

return $this-&gt;dbh-&gt;exec($sql);

}



public function query($sql){

//var_dump($sql);

$query = $this-&gt;dbh-&gt;prepare($sql);

$query-&gt;execute();

return $query-&gt;fetchAll(PDO::FETCH_OBJ);

}



/*

* Lấy tất cả

*/

public function fetchAll($table,$data = array('where','order_by','order','limit'),$join = array('table','where')){

//var_dump($data['order_by']);die();

$where = null;

$order_by = null;

$order = null;

$limit = null;



$table_join = null;

$join_where = null;



if (isset($data['where'])) {

$where = 'WHERE '.$data['where'];

//

}

if (isset($data['order_by'])) {

$order_by = 'ORDER BY '.$data['order_by'];

//

}

if (isset($data['order'])) {

$order = $data['order'];

//

}

if (isset($data['limit'])) {

$limit = 'LIMIT '.$data['limit'];

//

}

if (isset($join['table'])) {

if (!isset($data['where'])) {

$join_where = $join['where'];

$table_join = ', '.$join['table'].' WHERE ';

}

else{

$table_join = ', '.$join['table'];

$join_where = ' AND '.$join['where'];

}



//

}

$sql = &quot;SELECT * FROM $table $table_join $where $join_where $order_by $order $limit&quot;;

//var_dump($sql);

$query = $this-&gt;dbh-&gt;prepare($sql);

$query-&gt;execute();

return $query-&gt;fetchAll(PDO::FETCH_OBJ);

}



/*

* Lấy ID cuối cùng

*/

public function getLast($table)

{

$sql = &quot;SELECT * FROM $table ORDER BY &quot;.$table.&quot;_id DESC LIMIT 1&quot;;

$query = $this-&gt;dbh-&gt;prepare($sql);

$query-&gt;execute();

return $query-&gt;fetch(PDO::FETCH_OBJ);

}



/*

* Lấy theo ID

*/

public function getByID($table, $id)

{

return $this-&gt;getByField($table, $this-&gt;getPrimaryKey($table), $id);

}



/*

* Lấy theo cột

*/

public function getByField($table, $field, $value, $options = false)

{

$data = array($field=&gt;$value);

return $this-&gt;getByWhere($table, $data, $options);

}



/*

* Lấy 1 dòng theo điều kiện

*/

public function getByWhere($table, $data, $options = false)

{

$data = $this-&gt;filter($data, $table);

$conditions = array();

foreach($data as $key=&gt;$value)

{

if($value==null)

{

$conditions[] = &quot;$key IS NULL&quot;;

unset($data[$key]);

}

else

{

$conditions[] = &quot;$key=?&quot;;

}

}

$str = implode(' AND ', $conditions);

$sql = &quot;SELECT * FROM $table WHERE $str&quot;;

if($options)

{

$sql .= ' ' . $options;

}

$query = $this-&gt;dbh-&gt;prepare($sql);

$query-&gt;execute(array_values($data));

return $query-&gt;fetch(PDO::FETCH_OBJ);

}



/*

* Lấy tất cả theo điều kiện

*/

public function getAllByWhere($table, $data, $options = false)

{

$data = $this-&gt;filter($data, $table);

$conditions = array();

foreach($data as $key=&gt;$value)

{

if($value==null)

{

$conditions[] = &quot;$key IS NULL&quot;;

unset($data[$key]);

}

else

{

$conditions[] = &quot;$key=?&quot;;

}

}

$str = implode(' AND ', $conditions);

$sql = &quot;SELECT * FROM $table WHERE $str&quot;;

if($options)

{

$sql .= ' ' . $options;

}

$query = $this-&gt;dbh-&gt;prepare($sql);

$query-&gt;execute(array_values($data));

return $query-&gt;fetchAll(PDO::FETCH_OBJ);

}



public function filter($insert, $table)

{

$columns = $this-&gt;dbh-&gt;query(&quot;SHOW COLUMNS FROM <code>$table</code>&quot;)-&gt;fetchAll();

$fields = array();

foreach($columns as $row)

{

$fields[$row['Field']] = true;

}



foreach($insert as $key=&gt;$value)

{

if(!isset($fields[$key]))

{

unset($insert[$key]);

}

}



if(count($insert)===0)

{

throw new Exception('At least one field must be passed as data.  Check to make sure fields exist in Database');

}

return $insert;

}



public function getPrimaryKey($table)

{

$sql = &quot;SHOW KEYS FROM $table WHERE Key_name = 'PRIMARY'&quot;;

$stmt = $this-&gt;dbh-&gt;query($sql);

$res = $stmt-&gt;fetch(PDO::FETCH_OBJ);

return $res-&gt;Column_name;

}



function __destruct() {

try {

$this-&gt;dbh = null; //Closes connection

return $this-&gt;dbh;

} catch (PDOException $e) {

throw new Exception($e-&gt;getMessage());

}

}

}
&lt;pre&gt;

Ứng dụng:

Ta thử viết cho blogModel như ở bài trước.

/model/blogModel.php


<?php



Class blogModel Extends baseModel {

protected $table = "blog";



public function getAllBlog($data = null,$join = null)

{

return $this->fetchAll($this->table,$data,$join);

}



public function createBlog($data)

{



return $this->insert($this->table,$data);

}

public function updateBlog($data,$id)

{

if ($this->getBlogByWhere($id)) {



return $this->update($this->table,$data,$id);

}



}

public function deleteBlog($id){

if ($this->getAccount($id)) {

return $this->delete($this->table,array('blog_id'=>$id));

}

}

public function getBlog($id){

return $this->getByID($this->table,$id);

}

public function getBlogByWhere($where){

return $this->getByWhere($this->table,$where);

}

public function getLastBlog(){

return $this->getLast($this->table);

}

public function queryBlog($sql){

return $this->query($sql);

}

}

?>

Chúc các bạn thành công!

 

About ngoton

Ngô Tôn is a programmer with passion for tailored software solutions. Comes with 7+ years of IT experience, to execute beautiful front-end experiences with secure and robust back-end solutions.

Check Also

Upload Multi File in PHP with Ajax – Nhật ký lập trình [Chapter 4]

Mục lục Cấu trúc cơ bản của AjaxUpload multi file in PHP with Ajax Sử …

Leave a Reply

avatar
  Subscribe  
Notify of