492
PHP PDO(mysql) 封裝類
PHP PDO(mysql) 封裝類
年初在開發站點統計，自己封裝的PHP PDO類，現在又改了一點，增加了一點，目前沒有加上事務處理，以後會有。
上肉，上綠色無汙染的肉（自家養殖場出的）：
/**錯誤函數 Feng.Fox
* @param $e 對象
* @param bool $debug
* @param string $message 錯誤信息
* @param string $sql 錯誤sQL
* @return bool
*/
function halt($e, $debug=true,$message = '', $sql = '') {
if ($debug) {
if (isset ( $e->errorInfo )) {
$errorInfo = $e->errorInfo;
$errorno = $e->getCode ();
$error = $e->getMessage ();
$errorFile = $e->getFile ();
$errorLine = $e->getLiNe ();
} elseif($e->getCode()) {
$errorLine= $e->getLine();
$errorno = $e->getCode();
$error = $e->getMessage();
$errorFile = $e->getFile();
} else {
$errorInfo = $e->errorInfo();
$errorno = $errorInfo [1];
$error = $errorInfo [2];
$errorFile = '';
}
$errormsg = "<b>MySQL Query : </b> $sql <br /><b> MySQL Error : </b>{$error} <br /> <b>";
$errormsg .= "MySQL Errno : </b>{$errorno} <br /><b>";
$errormsg .= "File : </b>{$errorFile} <br /><b>";
$errormsg .= "Lile : </b>{$errorLine} <br /><b> Message : </b> $message <br />";
$msg = $errormsg;
echo '<div ><span>' . $msg . '</span></div>';
exit ();
} else {
return false;
}
}
/**
* 數據庫工廠類 phpcms
*/
final class CDbFactory {
/**
* 當前數據庫工廠類靜態實例
*/
private static $db_factory;
/**
* 數據庫配置列表
*/
protected $db_config = array();
/**
* 數據庫操作實例化列表
*/
protected $db_list = array();
private $table=array();
private $db_setting = 'default';
/**
* 構造函數
*/
public function __construct() {
}
/**
* 返回當前終級類對象的實例
* @param $db_config 數據庫配置
* @param $db_setting
* @return object
*/
public static function getInstance($db_setting='default',$db_config = '') {
$db_factory=null;
if(!empty($db_config)&&!isset($db_config[$db_setting])) {
$db_setting = 'default';
}
if(self::$db_factory == ''||!is_object(self::$db_factory)) {
self::$db_factory = $db_factory= new CDbFactory();
}else{
$db_factory=self::$db_factory;
}
if(empty($db_factory->db_config)){
if(empty($db_config)) {
$db_config = F::loadConfig('database');
}
self::$db_factory->db_config=$db_config;
}else{
if(!empty($db_config)&& $db_config != $db_factory->db_config){
self::$db_factory->db_config = array_merge($db_config, $db_factory->db_config);
}
}
return $db_factory;
}
/**
* 獲取數據庫操作實例
* @param $db_setting 數據庫配置名稱
* @return CPDO
*/
public function getDatabase($db_setting) {
if(!empty($db_setting)&&isset($this->db_config[$db_setting])) {
}else{
$db_setting=$this->db_setting;
}
$object =null;
if(!isset($this->db_list[$db_setting]) || !is_object($this->db_list[$db_setting])) {
$object = new CPDO();
$object->open($this->db_config[$db_setting]);
$object->connect();
$this->db_list[$db_setting] = $object;
}else{
$object=$this->db_list[$db_setting];
}
return $object;
}
/**
* 獲取數據庫配置信息
* @param string $db_setting
* @param bool $isAll false單個$db_setting數據庫配置(默認) ; true全部
* @return array
*/
public function getDbConfig($db_setting='default',$isAll=false){
$db_config=$this->db_config;
if(!empty($db_setting)&&isset($db_config[$db_setting])) {
}else{
$db_setting=$this->db_setting;
}
if(empty($db_config)){
$db_config = F::loadConfig('database');
}
return $isAll ? $db_config : $db_config[$db_setting];
}
/**
* 加載數據庫驅動
* @param $db_setting 數據庫配置名稱
* @return object
*/
public function connect($db_setting) {
$object = new CPDO();
$object->open($this->db_config[$db_setting]);
$object->connect();
return $object;
}
/**
* 關閉數據庫連接
* @return void
*/
public function close() {
if(is_array($this->db_list)){
foreach($this->db_list as $db) {
$db=null;
}
$this->db_list=null;
}
}
/**
* 析構函數
*/
public function __destruct() {
$this->close();
}
}
/**
*
* @author Feng . Fox
*/
final class CPDOStatement
{
public $debug = false;
public $pdo = null;
private $st = null;
/**
* @param $st
* @param bool $debug
* @param $pdo
*/
function CPDOStatement($st, $debug = true, $pdo)
{
$this->st = $st;
$this->pdo = $pdo;
$this->debug = $debug;
}
/**
* @param array $input_parameters
* @return $this|bool
*/
public function execute(array $input_parameters = null)
{
try {
$this->st->execute($input_parameters);
return $this;
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* 輸出1行數據
* @param int $fetch_style
* @return array
*/
public function fetch($fetch_style = PDO::FETCH_ASSOC)
{
try {
return $this->st->fetch($fetch_style);
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* @param $parameter
* @param $variable
* @param null $data_type
* @param null $length
* @param null $driver_options
* @return $this|bool
*/
public function bindParam($parameter, &$variable, $data_type = null, $length = null, $driver_options = null)
{
try {
$this->st->bindParam($parameter, $variable, $data_type, $length, $driver_options);
return $this;
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* @param $column
* @param $param
* @param null $type
* @param null $maxlen
* @param null $driverdata
* @return $this|bool
*/
public function bindColumn($column, &$param, $type = null, $maxlen = null, $driverdata = null)
{
try {
$this->st->bindColumn($column, $param, $type, $maxlen, $driverdata);
return $this;
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* 綁定參數
*
* @param string $parameter 字符參數[:num]或數字（從1開始）;根據prepare中的SQL語句
* @param string $value 數據
* @param int $data_type 其他
* @return CPDOStatement boolean
*/
public function bindValue($parameter, $value, $data_type = null)
{
try {
$this->st->bindValue($parameter, $value, $data_type);
return $this;
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* 總記錄數
* @return int
*/
public function rowCount()
{
return $this->st->rowCount();
}
/**
* 獲取第一行的某列，默認第1列
* @param int $column_number 從0開始
* @return boolean
*/
public function fetchColumn($column_number = null)
{
try {
return $this->st->fetchColumn($column_number);
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* 輸出全部數據
* @param $fetch_style
* @return array
*/
public function fetchAll($fetch_style = PDO::FETCH_ASSOC)
{
try {
return $this->st->fetchAll($fetch_style);
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* @param null $class_name
* @param array $ctor_args
* @return bool
*/
public function fetchObject($class_name = null, array $ctor_args = null)
{
try {
return $this->st->fetchObject($class_name, $ctor_args);
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* @return mixed
*/
public function errorCode()
{
return $this->st->errorCode();
}
/**
* @return mixed
*/
public function errorInfo()
{
return $this->st->errorInfo();
}
/**
* @param $attribute
* @param $value
* @return $this|bool
*/
public function setAttribute($attribute, $value)
{
try {
$this->st->setAttribute($attribute, $value);
return $this;
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* @param $attribute
* @return $this|bool
*/
public function getAttribute($attribute)
{
try {
$this->st->getAttribute($attribute);
return $this;
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/*
* 結果集列數
*/
public function columnCount()
{
return $this->st->columnCount();
}
/**
* @param $column
* @return $this|bool
*/
public function getColumnMeta($column)
{
try {
$this->st->getColumnMeta($column);
return $this;
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* @param $mode
* @return $this|bool
*/
public function setFetchMode($mode)
{
try {
$this->st->setFetchMode($mode);
return $this;
} catch (PDOException $e) {
halt($e, $this->debug);
return false;
}
}
/**
* @return mixed
*/
public function nextRowset()
{
return $this->st->nextRowset();
}
/**
* @return mixed
*/
public function closeCursor()
{
return $this->st->closeCursor();
}
/**
* @return mixed
*/
public function debugDumpParams()
{
return $this->st->debugDumpParams();
}
/**
*
*/
public function __destruct()
{
$this->closeCursor();
}
}
/**
* PDO
* @author Feng . Fox 2013.11.11
*
*/
final class CPDO
{
/**
* 數據庫配置信息
*/
private $config = null;
/**
* 數據庫連接資源句柄
*/
public $pdo = null;
public function __construct()
{
}
/**
* 打開數據庫連接,有可能不真實連接數據庫
*
* @param $config 數據庫連接參數
* $config = [
* 'ms' => 'mysql',
* 'host' => 'localhost',
* 'prot' => '3306'
* 'dbname' => 'test',
* 'username' => 'root',
* 'password' => 'root',
* 'debug' => true,
* 'options'=>[
* PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'",
* PDO::ATTR_PERSISTENT => true,
* PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
* ]
* ];
* @return void
*/
public function open($config)
{
$this->config = $config;
if (isset ($config ['autoconnect']) && $config ['autoconnect'] == 1) {
$this->connect();
}
}
/**
* 真正開啟數據庫連接
* @return void
*/
public function connect()
{
try {
if (empty ($this->config ['dsn'])) {
$dsn = "{$this->config['ms']}:host={$this->config['host']};dbname={$this->config['dbname']}";
$dsn .= empty ($this->config ['port']) ? '' : ';prot=' . $this->config ['port'];
} else {
$dsn = $this->config ['dsn'];
}
$this->pdo = new PDO ($dsn, $this->config ['username'], $this->config ['password'], $this->config ['options']);
$this->database = $this->config ['dbname'];
return $this;
} catch (PDOException $e) {
halt($e, $this->config['debug'], '[PDOException] Can not connect to MySQL server');
return false;
}
}
/*
* 標明回滾起始點
*/
public function beginTransaction()
{
return $this->pdo->beginTransaction();
}
/*
* 標明回滾結束點，並執行SQL
*/
public function commit()
{
return $this->pdo->commit();
}
/*
* 獲取錯誤碼
*/
public function errorCode()
{
return $this->pdo->errorCode();
}
/*
* 獲取錯誤的信息
*/
public function errorInfo()
{
return $this->pdo->errorInfo();
}
/*
* 處理一條SQL語句，並返回所影響的條目數
*/
public function exec($sql)
{
try {
return $this->pdo->exec($sql);
} catch (PDOException $e) {
halt($e, $this->config['debug']);
return false;
}
}
/*
* 獲取一個“數據庫連接對象”的屬性
*/
public function getAttribute($attribute)
{
try {
return $this->pdo->getAttribute($attribute);
} catch (PDOException $e) {
return false;
}
}
/*
* 獲取有效的PDO驅動器名稱
*/
public function getAvailableDrivers()
{
return $this->pdo->getAvailableDrivers();
}
/*
* 獲取寫入的最後一條數據的主鍵值
*/
public function lastInsertId()
{
return $this->pdo->lastInsertId();
}
/**
* 處理一條SQL語句 返回結果集
*
* @param string $sql
* @return array
*/
public function query($sql)
{
try {
return $this->pdo->query($sql);
} catch (PDOException $e) {
halt($e, $this->config['debug']);
return false;
}
}
/**
* 生成一個“查詢對象”
*
* @param string $sql
* 查詢語句
* @return CPDOStatement
*/
public function prepare($sql)
{
try {
return new CPDOStatement ($this->pdo->prepare($sql), $this->config['debug'], $this->pdo);
} catch (PDOException $e) {
halt($e, $this->config['debug']);
return false;
}
}
/*
* 為某個SQL中的字符串添加引號 public function quote(){ }
*/
/*
* 執行回滾
*/
public function rollBack()
{
return $this->pdo->rollBack();
}
/*
* 為一個“數據庫連接對象”設定屬性
*/
public function setAttribute($attribute, $value)
{
try {
$this->pdo->setAttribute($attribute, $value);
return $this;
} catch (PDOException $e) {
return false;
}
}
/**
* 析構函數
*/
public function __destruct()
{
$this->pdo = null;
}
}
/**
* 數據模型基類
* @author Feng . Fox 2013.11.11
*/
class CDb
{
//數據庫配置
protected $db_config = '';
//數據庫連接
protected $db;
//調用數據庫的配置項
protected $db_setting = 'default';
//數據表名
protected $table_name = '';
//表前綴
protected $db_tablepre = '';
//數據庫
protected $database = '';
/** 配置
* @param $table_name 表名稱
* @param string $db_setting 調用數據庫配置項
* @param array $db_config 數據庫配置
*/
public function __construct($table_name, $db_setting = 'default', $db_config = array())
{
if (!empty($db_config) && !isset($db_config[$db_setting])) {
$db_setting = 'default';
}
$this->db_setting = $db_setting;
$dbf = CDbFactory::getInstance($db_setting, $db_config);
//$this->db=$dbf->getDatabase($db_setting);
$this->db_config = $dbf->getDbConfig($db_setting, true);
//參數賦值
$this->db_tablepre = $this->db_config[$db_setting]['tablepre'];
$this->database = $this->db_config[$db_setting]['dbname'];
$this->table_name = $this->db_tablepre . $table_name;
}
/**
* 重置數據庫連接參數
* @param string $table_name
* @param string $db_setting
* @param array $db_config
*/
public function ReSetDb($table_name = '', $db_setting = 'default', $db_config = array())
{
if (!empty($db_config) && !isset($db_config[$db_setting])) {
$db_setting = 'default';
}
$this->db_setting = $db_setting;
$dbf = CDbFactory::getInstance($db_setting, $db_config);
$this->db = $dbf->getDatabase($db_setting);
$this->db_config = $dbf->getDbConfig($db_setting, true);
//參數賦值
$this->db_tablepre = $this->db_config[$db_setting]['tablepre'];
$this->database = $this->db_config[$db_setting]['dbname'];
if (!empty($table_name)) {
$this->table_name = $this->db_tablepre . $table_name;
}
}
/**
* 設置 表名稱，不包括前綴
* @param $table_name
* @return string
*/
public function setTableName($table_name)
{
if (!empty($table_name)) {
$this->table_name = $this->db_tablepre . $table_name;
}
return $this->table_name;
}
/**
* 表名稱，包括前綴
* @return string
*/
public function getTableName()
{
return $this->table_name;
}
/**
* 表前綴
* @return string
*/
public function getTablePre()
{
return $this->db_tablepre;
}
/**
* 當前數據庫配置,默認當前數據庫
* @param string $db_setting
* @param bool $isAll true 全部
* @return string
*/
public function getDbConfig($db_setting = '', $isAll = false)
{
if (empty($db_setting)) {
$db_setting = $this->db_setting;
}
return $isAll ? $this->db_config : $this->db_config[$db_setting];
}
/** pdo 類
* @return CPDO
*/
final public function pdo()
{
if ($this->db != '' && is_object($this->db)) {
return $this->db;
}
$this->db = $pdo = CDbFactory::getInstance($this->db_setting)->getDatabase($this->db_setting);
return $pdo;
}
/**
* 執行sql查詢
* @param $where 查詢條件
* 字符串,請按照格式 ：
* 字符串 [例2：array('catid=:catid AND time>=:time ',array(':catid'=>10,':time'=>'2012-02-10')) ]
* 數組時 [例: array('name'=>'phpcms','password'=>'123456')]
* @param $fields 需要查詢的字段值[例`name`,`gender`,`birthday`]
* @param $limit 返回結果範圍[例：10或10,10 默認為空]
* @param $order 排序方式 [默認按數據庫默認方式排序]
* @param $group 分組方式 [默認為空]
* @return array 查詢結果集數組
*/
final public function select($where = '', $fields = '*', $limit = '', $order = '', $group = '')
{
$parameter = array();
$search = '';
if (!empty($where) && is_array($where)) {
if (isset($where[1]) && is_array($where[1])) {
list($a, $b) = $where;
$search = $a;
$i = 1;
foreach ($b as $k => $r) {
$parameter[$i]['key'] = is_numeric($k) ? $i : $k;
$parameter[$i]['value'] = $r;
$i++;
}
} else {
$i = 1;
foreach ($where as $k => $r) {
if ($i > 1) {
$search .= " AND `$k`=:$k ";
} else {
$search .= " `$k`=:$k ";
}
$parameter[$i]['key'] = ':' . $k;
$parameter[$i]['value'] = $r;
$i++;
}
}
} else {
$search = $where;
}
$where = $search == '' ? '' : ' WHERE ' . $search;
$order = $order == '' ? '' : ' ORDER BY ' . $order;
$group = $group == '' ? '' : ' GROUP BY ' . $group;
$limit = $limit == '' ? '' : ' LIMIT ' . $limit;
$field = explode(',', $fields);
array_walk($field, array($this, 'add_special_char'));
$fields = implode(',', $field);
$sql = 'SELECT ' . $fields . ' FROM ' . $this->databaseTable($this->table_name) . $where . $group . $order . $limit;
$pdo = $this->pdo();
$smt = $pdo->prepare($sql);
if (is_array($parameter) && count($parameter) > 0) foreach ($parameter as $k => $r) {
$smt->bindValue($r['key'], $r['value']);
}
$smt->execute();
return $smt->fetchAll();
}
/**
* 查詢多條數據並分頁
* @param $where 查詢條件
* 字符串,請按照格式 ：
* 字符串 [例2：array('catid=:catid AND time>=:time ',array(':catid'=>10,':time'=>'2012-02-10')) ]
* 數組時 [例: array('name'=>'phpcms','password'=>'123456')]
* @param $fields 字段 *,id
* @param $order 排序 id desc ,orderlist asc
* @param $page 頁碼 1
* @param $pagesize 每頁條數
* @param $setpages
* @param $urlrule
* @param $array
* @return array
*/
final public function listinfo($where = '', $fields = '*', $order = '', $page = 1, $pagesize = 20, $setpages = 10, $urlrule = '', $array = array())
{
$parameter = array();
$search = '';
if (!empty($where) && is_array($where)) {
if (isset($where[1]) && is_array($where[1])) {
list($a, $b) = $where;
$search = $a;
$i = 1;
foreach ($b as $k => $r) {
$parameter[$i]['key'] = is_numeric($k) ? $i : $k;
$parameter[$i]['value'] = $r;
$i++;
}
} else {
$i = 1;
foreach ($where as $k => $r) {
if ($i > 1) {
$search .= " AND `$k`=:$k ";
} else {
$search .= " `$k`=:$k ";
}
$parameter[$i]['key'] = ':' . $k;
$parameter[$i]['value'] = $r;
$i++;
}
}
} else {
$search = $where;
}
$where = $search == '' ? '' : ' WHERE ' . $search;
$order = $order == '' ? '' : ' ORDER BY ' . $order;
$group = '';
$group = $group == '' ? '' : ' GROUP BY ' . $group;
$page = max(intval($page), 1);
$offset = $pagesize * ($page - 1);
$limit = " LIMIT $offset,$pagesize";
$fields = empty($fields) ? '*' : $fields;
$field = explode(',', $fields);
array_walk($field, array($this, 'add_special_char'));
$fields = implode(',', $field);
$sql = 'SELECT sql_calc_found_rows ' . $fields . ' FROM ' . $this->databaseTable($this->table_name) . $where . $group . $order . $limit;
$pdo = $this->pdo();
$smt = $pdo->prepare($sql);
if (is_array($parameter) && count($parameter) > 0) foreach ($parameter as $k => $r) {
$smt->bindValue($r['key'], $r['value']);
}
$smt->execute();
$smt2 = $pdo->prepare("SELECT FOUND_ROWS()");
$smt2->execute();
$this->number = $smt2->fetchColumn();
if ($this->number > 0) {
$this->pages = pages($this->number, $page, $pagesize, $urlrule, $array, $setpages);
return $smt->fetchAll();
}
return array();
}
/**
* 獲取單條記錄查詢
* @param array $where 查詢條件語句
* 字符串,請按照格式 ：
* 字符串 [例2：array('catid=:catid AND time>=:time ',array(':catid'=>10,':time'=>'2012-02-10')) ]
* 數組時 [例: array('name'=>'phpcms','password'=>'123456')]
* @param string $fields 需要查詢的字段值[例`name`,`gender`,`birthday`]
* @param string $order 排序方式 [默認按數據庫默認方式排序]
* @param string $group 分組方式 [默認為空]
* @return array/null 數據查詢結果集,如果不存在，則返回空
*/
final public function get_one($where, $fields = '*', $order = '', $group = '')
{
$parameter = array();
$search = '';
if (is_array($where)) {
//$where第2個參數為數組
if (isset($where[1]) && is_array($where[1])) {
$search = $where[0];
$i = 1;
foreach ($where[1] as $k => $r) {
$parameter[$i]['key'] = is_numeric($k) ? $i : $k;
$parameter[$i]['value'] = $r;
$i++;
}
} else {
$i = 1;
foreach ($where as $k => $r) {
if ($i > 1) {
$search .= " AND `$k`=:$k ";
} else {
$search .= " `$k`=:$k ";
}
$parameter[$i]['key'] = ':' . $k;
$parameter[$i]['value'] = $r;
$i++;
}
}
} else {
$search = $where;
}
$where = $search == '' ? '' : ' WHERE ' . $search;
$order = $order == '' ? '' : ' ORDER BY ' . $order;
$group = $group == '' ? '' : ' GROUP BY ' . $group;
$limit = ' LIMIT 1';
$field = explode(',', $fields);
array_walk($field, array($this, 'add_special_char'));
$fields = implode(',', $field);
$sql = 'SELECT ' . $fields . ' FROM ' . $this->databaseTable($this->table_name) . $where . $group . $order . $limit;
$pdo = $this->pdo();
$smt = $pdo->prepare($sql);
foreach ($parameter as $k => $r) {
$smt->bindValue($r['key'], $r['value']);
}
$smt->execute();
return $smt->fetch();
}
/**
* 直接執行sql查詢
* @param $sql 查詢sql語句
* @return boolean/query resource 如果為查詢語句，返回資源句柄，否則返回true/false
*/
final public function query($sql)
{
return $this->pdo()->query($sql);
}
/**
* 直接執行sql查詢
* @param $sql 查詢sql語句
* @return int|false
*/
final public function exec($sql)
{
return $this->pdo()->exec($sql);
}
/**
* 執行添加記錄操作
* @param array $data 要增加的數據，參數為數組。數組key為字段值，數組值為數據取值
* @param bool $return_insert_id 是否返回新建ID號
* @param bool $replace 是否采用 replace into的方式添加數據
* @return boolean
*/
final public function insert($data, $return_insert_id = false, $replace = false)
{
if (!is_array($data) || count($data) == 0) {
return false;
}
$fields = array_keys($data);
$valuedata = array_values($data);
array_walk($fields, array($this, 'add_special_char'));
$field = implode(',', $fields);
$value = str_repeat('?,', count($fields) - 1) . '?';
$cmd = $replace ? 'REPLACE INTO' : 'INSERT INTO';
$sql = $cmd . $this->databaseTable($this->table_name) . ' (' . $field . ') VALUES (' . $value . ')';
$pdo = $this->pdo();
$smt = $pdo->prepare($sql);
$i = 1;
foreach ($valuedata as $r) {
$smt->bindValue($i, empty($r) ? '' : $r);
$i++;
}
$row = $smt->execute();
return $return_insert_id ? $pdo->lastInsertId() : $row;
}
/**
* 獲取最後一次添加記錄的主鍵號
* @return int
*/
final public function insert_id()
{
return $this->pdo()->lastInsertId();
}
/**
* 執行更新記錄操作
* @param $data 要更新的數據內容，參數為數組
* 為數組時數組key為字段值，數組值為數據取值
* 為數組時[例: array('name'=>'lanmps','password'=>'123456')]
* 數組的另一種使用array('name'=>'+=1', 'base'=>'-=1');程序會自動解析為`name` = `name` + 1, `base` = `base` - 1
* 字符串,請按照格式 ：
* 字符串 [例2：array('catid=:catid AND time>=:time ',array(':catid'=>10,':time'=>'2012-02-10')) ]
* @param $where 更新數據時的條件,
* 字符串,請按照格式 ：
* 字符串 [例2：array('catid=:catid AND time>=:time ',array(':catid'=>10,':time'=>'2012-02-10')) ]
* 數組時 [例: array('name'=>'lanmps','password'=>'123456')]
* @return boolean
*/
final public function update($data, $where)
{
$parameter = array();
$i = $j = 1;
$field = '';
if (is_array($data)) {
$fields = array();
foreach ($data as $k => $v) {
switch (substr($v, 0, 2)) {
case '+=':
$v = substr($v, 2);
if (is_numeric($v)) {
$fields[] = self::add_special_char($k) . '=' . self::add_special_char($k) . '+' . self::escape_string($v, '', false);
} else {
continue;
}
break;
case '-=':
$v = substr($v, 2);
if (is_numeric($v)) {
$fields[] = self::add_special_char($k) . '=' . self::add_special_char($k) . '-' . self::escape_string($v, '', false);
} else {
continue;
}
break;
default:
$fields[] = self::add_special_char($k) . '=:' . $k;
$parameter[$i]['key'] = ':' . $k;
$parameter[$i]['value'] = $v;
$i++;
}
}
$field = implode(',', $fields);
} else {
$field = $data;
}
$search = '';
if (is_array($where)) {
//$where第2個參數為數組
if (isset($where[1]) && is_array($where[1])) {
$search = $where[0];
//$i++;
foreach ($where[1] as $k => $r) {
$parameter[$i]['key'] = is_numeric($k) ? $i : $k;
$parameter[$i]['value'] = $r;
$i++;
}
} else {
//$i++;
$x = 1;
foreach ($where as $k => $r) {
if ($x > 1) {
$search .= " AND `$k`=:$k ";
} else {
$search .= " `$k`=:$k ";
}
$parameter[$i]['key'] = ':' . $k;
$parameter[$i]['value'] = $r;
$i++;
$x++;
}
}
} else {
$search = $where;
}
$where = ' WHERE ' . $search;
$sql = 'UPDATE ' . $this->databaseTable($this->table_name) . ' SET ' . $field . $where;
$pdo = $this->pdo();
$smt = $pdo->prepare($sql);
foreach ($parameter as $r) {
$smt->bindValue($r['key'], $r['value']);
}
return $smt->execute();
}
/**
* 執行刪除記錄操作
* @param $where 刪除數據條件,不充許為空。
* @return boolean
*/
final public function delete($where)
{
if (!is_array($where) || count($where) == 0) {
return false;
}
$parameter = array();
$search = '';
if (is_array($where)) {
//$where第2個參數為數組
if (isset($where[1]) && is_array($where[1])) {
$search = $where[0];
$i = 1;
foreach ($where[1] as $k => $r) {
$parameter[$i]['key'] = is_numeric($k) ? $i : $k;
$parameter[$i]['value'] = $r;
$i++;
}
} else {
$i = 1;
foreach ($where as $k => $r) {
if ($i > 1) {
$search .= " AND `$k`=:$k ";
} else {
$search .= " `$k`=:$k ";
}
$parameter[$i]['key'] = ':' . $k;
$parameter[$i]['value'] = $r;
$i++;
}
}
} else {
$search = $where;
}
$where = $search == '' ? '' : ' WHERE ' . $search;
$sql = 'DELETE FROM ' . $this->databaseTable($this->table_name) . $where;
$pdo = $this->pdo();
$smt = $pdo->prepare($sql);
foreach ($parameter as $k => $r) {
$smt->bindValue($r['key'], $r['value']);
}
return $smt->execute();
}
/**
* 計算記錄數
* @param string /array $where 查詢條件
*/
final public function count($where = '')
{
$r = self::get_one($where, "COUNT(*) AS num");
return $r['num'];
}
/**
* 將數組轉換為SQL語句
* @param array $where 要生成的數組
* @param string $font 連接串。
* @return string
*/
final public function sqls($where, $font = ' AND ')
{
if (is_array($where)) {
$sql = '';
foreach ($where as $key => $val) {
$sql .= $sql ? " $font `$key` = '$val' " : " `$key` = '$val'";
}
return $sql;
} else {
return $where;
}
}
/**
* 獲取數據表主鍵
* @return array
*/
final public function get_primary()
{
return self::$db->get_primary($this->table_name);
}
/**
* 獲取表字段
* @param string $table_name 表名
* @param bool true默認加前綴,false不加前綴
* @return array
*/
final public function get_fields($table_name = '', $isTablepre = true)
{
if (empty($table_name)) {
$table_name = $this->table_name;
} else {
$table_name = ($isTablepre ? $this->db_tablepre : '') . $table_name;
}
$fields = array();
$pdo = $this->pdo();
$smt = $pdo->prepare("SHOW COLUMNS FROM $table_name");
$smt->execute();
while ($r = $smt->fetch()) {
$fields[$r['Field']] = $r['Type'];
}
return $fields;
}
/**
* 檢查表是否存在
* @param $table 表名
* @return boolean
*/
final public function table_exists($table)
{
$tables = $this->list_tables();
return in_array($this->db_tablepre . $table, $tables) ? 1 : 0;
}
/**
* 檢查字段是否存在
* @param $field 字段名
* @return boolean
*/
public function field_exists($field)
{
$fields = $this->db->get_fields($this->table_name);
return array_key_exists($field, $fields);
}
/**獲取數據庫中的所有表
* @return array
*/
final public function list_tables()
{
$tables = array();
$pdo = $this->pdo();
$smt = $pdo->prepare("SHOW TABLES");
$smt->execute();
while ($r = $smt->fetch()) {
$tables[] = $r['Tables_in_' . $this->database];
}
return $tables;
}
/**
* 返回數據結果集
* @return array
*/
final private function fetch_array()
{
}
/**
* 返回數據庫版本號
*/
final public function version()
{
return $this->db->version();
}
final private function databaseTable($table)
{
if (strpos($table, $this->db_tablepre) !== 0) {
$table = $this->db_tablepre . $table;
}
return " `{$this->database}`.`{$table}` ";
}
/**
* 對字段兩邊加反引號，以保證數據庫安全
* @param $value 數組值
* @return string
*/
final public static function add_special_char($value)
{
if ('*' == $value || false !== strpos($value, '(') || false !== strpos($value, '.') || false !== strpos($value, '`')) {
//不處理包含* 或者 使用了sql方法。
} else {
$value = '`' . trim($value) . '`';
}
if (preg_match('/\b(select|insert|update|delete)\b/i', $value)) {
$value = preg_replace('/\b(select|insert|update|delete)\b/i', '', $value);
}
return $value;
}
/**
* 對字段值兩邊加引號，以保證數據庫安全
* @param $value 數組值
* @param $key 數組key
* @param $quotation
* @return string
*/
final public static function escape_string(&$value, $key = '', $quotation = 1)
{
if ($quotation) {
$q = '\'';
} else {
$q = '';
}
$value = $q . $value . $q;
return $value;
}
/**
* 獲取單條記錄查詢
* @param array $sql 查詢條件語句
* @return array/null 數據查詢結果集,如果不存在，則返回空
*/
final public function fetch($sql)
{
$pdo = $this->pdo();
$smt = $pdo->prepare($sql);
$smt->execute();
return $smt->fetch();
}
/**
* 執行sql查詢
* @param $sql 查詢條件
* @return array 查詢結果集數組
*/
final public function fetchAll($sql = '')
{
$pdo = $this->pdo();
$smt = $pdo->prepare($sql);
$smt->execute();
return $smt->fetchAll();
}
/**第一個參數值
* @param $sql
* @return mixed
*/
final public function resultFirst($sql)
{
$pdo = $this->pdo();
$smt = $pdo->prepare($sql);
$smt->execute();
return $smt->fetchColumn();
}
/**
* 析構函數
*/
public function __destruct()
{
$dbf = CDbFactory::getInstance();
$dbf->close();
$this->db = null;
}
}
怎麼吃，吃法，使用方法案例：
數據庫配置：
在 class CDbFactory 中搜索
F::loadConfig('database')
改成你自己的數據庫配置.參數如下
$config = [
'ms' => 'mysql',
'host' => 'localhost',
'prot' => '3306'
'dbname' => 'test',
'username' => 'root',
'password' => 'root',
'debug' => true,
'options'=>[
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'",
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]
];
這array我用的是PHP5.5中的簡寫，你自己改下
方法一：我用的就是這種
class adminModel extends CDb {
public function __construct() {
$this->table_name = 'a_admin';
parent::__construct($this->table_name);
}
}
$test=new adminModel();
$test->insert(array('id'=>1))
方法二：再次封裝成靜態類
另一個項目用的，那個項目沒用PDO，且基本的INSERT,UPDATE,DELETE都沒封裝，所已我直接一部到位。
https://blog.csdn.net/fenglailea/article/details/15335925
最後更新：2017-04-03 14:54:15