Home >Backend Development >PHP Tutorial >Useful MYSQL operation classes in PHP (Interpretation of design patterns 1)_PHP tutorial
Useful MYSQL operation classes in PHP (Interpretation of design patterns 1)_PHP tutorial
- WBOYOriginal
- 2016-07-13 17:51:46894browse
It is very simple to use PHP to operate a database. Even those who have started with PHPER can do it. However, when dealing with a large number of table operations, we are tired of many MYSQL statements, so we are eager to encapsulate a large number of database operations. stand up. So database object mapping appeared.
First we create a new interface.
singleton.class.php
[php]
/**
* @author tomyjohn
* @link
* @license
* @version 1.0
* @copyright Copyright 2010 tomyjohn - tomyjohn.gicp.net
* @package singleton
*/
/**
* Database object
*/
interface singleton{
/**
* Generate database object
* @returns object data object;
* @access public
*/
Public static function getInstance();
}
Create a new abstract class. This abstract class simply uses 5 methods to abstract all databases.
db.class.php
[php]
/**
* @author tomyjohn
* @link
* @license
* @version 1.0
* @copyright Copyright 2010 tomyjohn - tomyjohn.gicp.net
* @package db
*/
/**
*Abstract DB class
*/
abstract class db{
/**
* Factory mode
* @param string $type sql type
* @returns object
* @access public
*/
Public static function factory($type){
return call_user_func(array($type,'getInstance'));
}
/**
* Execute SQL statement
* @param string $query sql statement
* @return object resource or false;
* @access public
*/
abstract public function execute($query);
/**
* Get the array returned by the SQL statement
* @param string $query sql statement
* @return object resource or false;
* @access public
*/
abstract public function get_array($query);
/**
* Get the execution ID of the previous statement
* @param string $query sql statement
* @return integer number or false;
* @access public
*/
abstract public function insert_get_id($query);
/**
* Convert special characters
* @param string $string
* @return string processed string
* @access public
*/
abstract public function clean($string);
}
I believe that after reading this, you will all think of how to use the call_user_func method. Don’t worry, read on
mysql.class.php
[html]
/**
* @author tomyjohn
* @link
* @license
* @version 1.0
* @copyright Copyright 2010 tomyjohn - tomyjohn.gicp.net
* @package db
*/
/**
*MYSQL database object
*/
class mysql extends db implements singleton{
/**
* @var $instance object
* @access current class
*/
protected static $instance = null;
/**
* @var $link resource
* @access current class
*/
protected $link;
/**
* Database instance
* @return $self::instance object
*/
Public static function getInstance(){
If(is_null(self::$instance)){
self::$instance = new self();
}
return self::$instance;
}
/**
* Constructor
*/
protected function __construct(){
global $current_conf;
$user = $current_conf['DBUSER'];
$pass = $current_conf['DBPWD'];
$host = $current_conf['DBHOST'];
$db = $current_conf['DBNAME'];
$this->link = mysql_connect($host,$user,$pass);
mysql_set_charset($current_conf['DBCHARSET'] , $this->link);
Mysql_select_db($db);
}
/**
* Convert special characters
* @param string $string
* @return string processed string
* @access public
*/
Public function clean($string){
return mysql_real_escape_string($string,$this->link);
}
/**
* Execute SQL statement
* @param string $query sql statement
* @return object resource or false;
* @access public
*/
Public function execute($query){
return mysql_query($query,$this->link);
}
/**
* Get the execution ID of the previous statement
* @param string $query sql statement
* @return integer number or false;
* @access public
*/
Public function insert_get_id($query){
$this->execute($query);
return mysql_insert_id($this->link);
}
/**
* Get the array returned by the SQL statement
* @param string $query sql statement
* @return object resource or false;
* @access public
*/
Public function get_array($query){
$result = $this->execute($query);
$return = array();
If($result){
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$return[] =$row;
}
return $return;
}
}
[html] view plaincopy
$connection = db::factory('mysql');
$sql = "SELECT * FROM table";
$value_array = $connection->get_array($sql);
[html] view plaincopy
print_r($value_array);
Although this solves the problem of expansion and some repeated operations, it is not very convenient. We should go one step further and make the database table represented by objects, that is, database mapping
[html]
class dao{
/**
* @var $values array stores database objects
* @access current class
*/
protected $values = array();
/**
* @var $suffix array stores database objects
* @access public
*/
public $suffix = '';
/**
* Constructor
*/
public function __construct($qualifier = null){
global $current_conf;
$this->suffix = $current_conf['DBSUFFIX'];
if(!is_null($qualifier)){
$conditional = array();
if(is_numeric($qualifier)){
$conditional = array('id'=>$qualifier);
}
else if(is_array($qualifier)){
$conditional = $qualifier;
}
else{
throw new Exception('Invalid type of qualifier given!');
}
$this->populate($conditional);
}
}
public function __set($name , $value){
$this->values[$name] = $value;
}
public function __get($name){
if(isset($this->values[$name])){
return $this->values[$name];
}
else{
return null;
}
}
/**
* * Parse the parameters of the instance
* @param $conditional obj
*/
protected function populate($conditional){
$connection = db::factory('mysql');
$sql = "SELECT * FROM {$this->suffix}{$this->table} WHERE ";
$qualifier = '';
foreach($conditional as $column => $value){
if(!empty($qualifier)){
$qualifier .= ' AND ';
}
$qualifier .= "`{$column}`='" . $connection->clean($value) . "' ";
}
$sql .= $qualifier;
$value_array = $connection->get_array($sql);
if(!isset($value_array[0])){
$value_array[0] = array();
}
foreach($value_array[0] as $key => $value){
$this->values[$key] = $value;
}
}
/**
* Save data
*/
public function save(){
if(!$this->id){
$this->create();
}
else{
return $this->update();
}
}
/**
* Add data www.2cto.com
*/
public function create(){
$connection = db::factory('mysql');
$sql = "INSERT INTO {$this->suffix}{$this->table}(`";
$sql .= implode('`, `' , array_keys($this->values));
$sql .="`) VALUES('";
$clean = array();
foreach($this->values as $value){
$clean[] = $connection->clean($value);
}
$sql .= implode("', '" , $clean);
$sql .="')";
$this->id = $connection->insert_get_id($sql);
}
/**
* Update data
* @return returns the result of the operation
*/
public function update(){
$connection = db::factory('mysql');
$sql = "UPDATE {$this->suffix}{$this->table} set ";
$updates = array();
foreach($this->values as $key=>$value){
if($key!='id'){
$updates[] = "`{$key}`='" . $connection->clean($value) . "'";
}
}
$sql .= implode(',' , $updates);
$sql .= " WHERE id={$this->id}";
return $connection->execute($sql);
}
/**
* Delete data
* @return returns the result of the operation
*/
Public function delete(){
$connection = db::factory('mysql');
$sql = "DELETE FROM {$this->suffix}{$this->table} WHERE ";
$qualifier = 'id='.$this->id;
$sql .= $qualifier;
return $connection->execute($sql);
}
/**
* Convert object to array
* @return array
*/
Public function object_to_array(){
return $this->values;
}
}
I will write one
news.dao.class
class news extends dao{
protected $table = __CLASS__;
}