php mysql PDO封装
来源：互联网 发布：linux查看当前用户名 编辑：程序博客网 时间：2024/05/03 16:55
PDO封装，大神们给点意见，主要是用来方便自己使用，欢迎评论；具体使用可看文章末尾
使用过程中，发现一部分问题，已经作进一步修改，主要修改可以支持多表联合查询
支持事务处理，原始sql语句输出
<?php header("content-type:text/html;charset=utf-8");/** * autor：sujianbin * 2016-09-15 * 采用pdo对象方式连接 * MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！(alter table tablename ENGINE = InnoDB) * 支持预处理技术,主要在连接和编译过程精简，还可以SQL防止注入,快速执行 */class pdoModel{ private $linkId = '';//数据库连接标识 private $PDOStatement = null;//PDOStatement对象 private static $charset = 'utf8';//设置字符集 private static $host = 'localhost';//主机名或ip地址 private static $DBPort = '3306';//端口号 private static $DBUser = 'root';//用户名 private static $DBPass = 'root';//密码 private static $DBName = 'wsttc';//数据库名称 private static $debug = 1;//是否开启调试模式(项目上线后请关闭) private static $db_log = 0;//是否开启日志 private $querySql ='';//预处理sql语句 private $params = array();//预处理数组 private $autoCommit = TRUE;//是否开启自动提交,不适用于查询操作,默认自动提交，如若关闭操作时请先调用方法关闭 protected $transactionCount = 0;//事务标识数 private $db_press = 'su_';//数据库表前缀 /** * 构造函数 */ public function __construct() { if (!class_exists('PDO')){ throw new Exception('not found PDO'); return false; } try{ $this->linkId = new PDO('mysql:dbname='.self::$DBName.';host='.self::$host.';port='.self::$DBPort,self::$DBUser,self::$DBPass,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES '".self::$charset."';")); session_start(); date_default_timezone_set("PRC"); if(self::$debug){ error_reporting(E_ALL ^ E_NOTICE); }else{ error_reporting(NULL); ini_set('display_errors','Off'); } }catch (PDOException $e){ exit($e->getMessage()); } } /** * __get()方法用来获取私有属性 */ public function __get($property_name){ if(isset($this->$property_name)){ return($this->$property_name); }else{ return null; } } /** * __set()方法用来设置私有属性 */ public function __set($property_name,$value){ $this->$property_name = $value; } /** * 释放查询结果 */ private function free() { $this->PDOStatement = null; $this->params = array(); } /** * 销毁变量 * @return 无 */ protected function destroy(){ $this->db_log($this->_sql()); $array = array('field'=>'','join'=>'','where'=>'','group'=>'','having'=>'','order'=>'','limit'=>'','pk'=>''); foreach($array as $key=>$value){ unset($this->$key); } } /** * 获取毫秒技术 * @param string $format [description] * @param [type] $utimestamp [description] * @return [type] [description] */ private function udate($format = 'u', $utimestamp = null) { if (is_null($utimestamp)) $utimestamp = microtime(true); $timestamp = floor($utimestamp); $milliseconds = round(($utimestamp - $timestamp) * 1000000); return date(preg_replace('`(?<!\\\\)u`', $milliseconds, $format), $timestamp); } /** * 写入日志 * @param string $sql 执行的sql语句 * @return 无 */ private function db_log($sql){ if($this->db_log){ $file = 'cache/log.txt'; if(!file_exists($file)){ mkdir ("cache"); $myfile = fopen($file, "w"); } $filesize = filesize($file)/1024/1024;//1M if($filesize > 1){ file_put_contents($file,"");//防止日志太大 } if(preg_match('/INSERT|UPDATE|insert|update/',$sql)){ file_put_contents($file,$this->udate('Y-m-d H:i:s.u')." ".$sql."\r\n\r\n",FILE_APPEND | LOCK_EX); } } } /** * 过滤字符 * @param string $item 需要过滤的字符 * @return string 返回过滤后的字符 */ private function saddslashes($item){ $item=trim($item); if(!get_magic_quotes_gpc()) $item = addslashes($item); return $item; } /** *开启事务 */ public function beginTransaction(){ if (!$this->transactionCounter++) { return $this->linkId->beginTransaction(); } $this->exec('SAVEPOINT trans'.$this->transactionCounter); return $this->transactionCounter >= 0; } /** *提交事务 */ public function commit(){ if (!--$this->transactionCounter) { return $this->linkId->commit(); } return $this->transactionCounter >= 0; } /** *事务回滚 */ public function rollback(){ if (--$this->transactionCounter) { $this->exec('ROLLBACK TO trans'.$this->transactionCounter + 1); return true; } return $this->linkId->rollback(); } /** * 析构函数 * 释放结果集和销毁变量 */ public function __destruct(){ $this->destroy(); $this->free(); } /** * 函数执行错误处理 * @param string $functionName 函数名称 * @param array $args 包含信息的数组 * @return 返回执行状态 */ public function __call($functionName,$args){ if(strstr($functionName,'getFieldBy')){ $search = str_replace('getFieldBy','',$functionName); return $this->getFieldBy($args,$search); }else if(!function_exists($functionName)){ $msg = "你所调用的函数： ".$functionName."不存在"; echo $msg;exit; } } /** *输出最后执行的sql语句 */ public function getsql(){ if(is_array($this->params) && strpos($this->querySql,'?')){ $sql = ''; $array = explode('?',$this->querySql); foreach ($array as $key => $value) { if(!empty($this->params[$key]) || $this->params[$key] == '0'){ if(strpos($this->params[$key],"{$this->db_press}")==0 && strpos($this->params[$key],'.')){ $sql .= $array[$key].$this->params[$key]; }else{ $sql .= $array[$key]."'".$this->params[$key]."'"; } }else{ $sql .=$array[$key]; } } return $sql; } return $this->querySql; } /** * getsql函数的别名 * @return string 返回sql语句 */ public function _sql(){ return $this->getsql(); } /** * 给表加上前缀 * @param string $table 表名 * @return string 返回完整等我表名 */ protected function db_press($table){ return "`".$this->db_press.$table."`"; } /** *公共函数获取表信息 *@param string $table 表名 *@param array $array 对提交的所有数据进行过滤 */ private function common($table,$array=''){ $this->params = ''; $this->table = $this->db_press($table); //释放前次的查询结果 if ( !empty($this->PDOStatement) ) $this->free(); $this->PDOStatement = $this->linkId->query("DESC {$this->table}"); if(self::$debug && !$this->PDOStatement){ echo "\nPDO::errorInfo():\n"; print_r($this->linkId->errorInfo());die; } $results = $this->PDOStatement->fetchAll(PDO::FETCH_ASSOC); foreach($results as $k=>$v){ $result[] = $v['Field']; if($v['Key'] == 'PRI'){ $zhujian = $v['Field']; } } $arr = $left = $right = ''; //获取前台处理后的数组 if(empty($array)){ $data = count($_POST) != 0 ? $_POST:''; }else{ $data = count($_POST) != 0 ? $array + $_POST : $array; } $sqlR = ''; if(is_array($data)){ foreach ($data as $key => $value) { //过滤字段处理 if(in_array($key,$result)){ //为字段加上`符号和过滤字段 $value = $this->saddslashes($value); $left =$left. '`'.$key.'`'.','; $right[] = $value; $arr=$arr."`".$key."`=?".','; $sqlR .= '?,'; } } } $sqlR = substr($sqlR,0,-1); $left = substr($left,0,-1); $sql = substr($arr,0,-1); $temp['left'] = $left; $temp['right'] = $right; $temp['sql'] = $sql; $temp['zhujian'] = $zhujian; $temp['sqlR'] = $sqlR; return $temp; } /** * 需要查询的字段 * @param string $field 查询的字段 * @return $this 返回当前对象 */ public function field($field){ if(!empty($field)){ if(strpos($field,',') && !strpos($field,'.')){ $field = explode(',',$field); foreach ($field as $key => $value) { $field[$key] = '`'.$value.'`'; } $this->field = implode(',',$field); }else if(strpos($field,'.')){ $this->field = $field; }else{ $this->field = '`'.$field.'`'; } }else{ $this->field = '*'; } return $this; } /** * 连接方式，联合查询 * @return 返回当前对象 */ public function join($join){ if(!empty($join)){ $this->join = $join; } return $this; } /** * 排序条件 * @param string $having 字段 * @return $this 返回当前对象 */ public function having($having){ $this->having = $having?'HAVING '.$having :''; return $this; } /** * 排序条件 * @param string $group groupby字段 * @return $this 返回当前对象 */ public function group($group){ $this->group = $group?'GROUP BY '.$group :''; return $this; } /** * 排序条件 * @param string $order 排序条件 * @return $this 返回当前对象 */ public function order($order){ $this->order = $order?'ORDER BY '.$order :''; return $this; } /** * 查询数量 * @param int $offset 开始位置 * @param int $length 查询长度 * @return $this 返回当前对象 */ public function limit($offset,$length){ $this->limit = $length?('LIMIT '.$offset.','.$length) :''; return $this; } /** * 查询条件 * @param array|string $condition 查询条件 * @return $this 返回当前对象 */ public function where($condition=''){ $this->params = ''; if(!empty($condition)){ if(is_array($condition)){ $conditions = $this->condition($condition); $this->where = $conditions['right']; $this->where = $this->where?'and '.$this->where:''; $this->params = $conditions['params']; }else{ $this->where = 'and '.$condition; } } return $this; } /** * 判断条件是否加` * @param string $value 条件字段 * @return string 返回条件字段 */ public function parseK($value){ if(strpos($value,'.')){ return $value; }else{ return "`".$value."`"; } } /** * 解析条件数组 * @param array $condition 条件数组 * @return array 返回数组，包含右侧prepare语句和param数组 */ public function condition($condition){ $right = ''; $params = ''; if(is_array($condition)){ foreach($condition as $k=>$v){ if(is_array($v)){ if(empty($v['description'])){ $right.="and ".$this->parseK($k)." ".$v['terms']." ? "; $params[] = $v['value']; }else{ if(is_array($v['value'])){ $right.='and ('; foreach($v['value'] as $k1=>$v1){ $params[] = $v1; if(is_array($v['terms'])){ if(count($v['value']) != $k1+1){ $right.= " ".$this->parseK($k)." ".$v['terms'][$k1].' ? '.$v['description']." "; }else{ $right.= " ".$this->parseK($k)." ".$v['terms'][$k1].' ?'; } }else{ if(count($v['value']) != $k1+1){ $right.= " ".$this->parseK($k)." ".$v['terms'].' ? '.$v['description']." "; }else{ $right.= " ".$this->parseK($k)." ".$v['terms'].' ?'; } } } $right.=') '; }else{ $right.= $v['description']." ".$this->parseK($k)." ".$v['terms']." ? "; $params[] = $v['value']; } } }else{ $right.="and ".$this->parseK($k).' =? '; $params[] = $v; } } $right = preg_replace('/and /', '', $right, 1); //只替换一次(去掉第一次出现的字符and ) $condition['right'] = $right; $condition['params'] = $params; }else{ $condition = array('right'=>$condition,$params=>''); } return $condition; } /** * 值类型 * @param string 预处理值 * @param int 返回值类型 */ protected function pdoParamType($value){ if(is_int($value)) $type = PDO::PARAM_INT; elseif(is_bool($value)) $type = PDO::PARAM_BOOL; elseif(is_null($value)) $type = PDO::PARAM_NULL; elseif(is_string($value)) $type = PDO::PARAM_STR; else $type = FALSE; return $type; } /** * 把一个值绑定到一个参数 * @param array $params 预处理值数组 */ protected function bindPdoParam($params){ if(is_array($params)){ foreach($params as $key=>$val){ $val = array($key+1,$val,$this->pdoParamType($val)); call_user_func_array(array($this->PDOStatement,'bindValue'),$val); } } } /** * 添加单条数据 * @param string $table 表名 * @param array $data 需要改变的数组参数值 * @return 返回执行结果 */ public function add($table,$data=''){ $temp = $this->common($table,$data); $left = $temp['left']; $sqlR = $temp['sqlR']; $this->querySql = "INSERT INTO {$this->table} ($left) VALUES ($sqlR)"; //释放前次的查询结果 if ( !empty($this->PDOStatement) ) $this->free(); $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->params = $temp['right']; $this->bindPdoParam($this->params); $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } if($this->autoCommit != FALSE){ if($this->PDOStatement->rowCount()){ return $this->PDOStatement->rowCount(); }else{ return null; } } } /** * 修改单条数据 * @param string $table 表名 * @param int $id 主键id * @param array $data 需要改变的数组参数值 * @return 返回执行结果 */ public function save($table,$id,$data=''){ $temp = $this->common($table,$data); $sql = $temp['sql']; $zhujian = $temp['zhujian']; $this->querySql = "UPDATE {$this->table} SET $sql WHERE `{$zhujian}` = ?"; //释放前次的查询结果 if ( !empty($this->PDOStatement) ) $this->free(); $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->params = $temp['right']; $this->params[] = (int)$id; $this->bindPdoParam($this->params); $result = $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } if($this->autoCommit != FALSE){ if($result){ return $result; }else{ return null; } } } /** * 基于add、save方法后，添加和修改可统一使用update * @param string $table 表名 * @param array $data 传值处理数组 * @return 返回执行结果 */ public function update($table,$data=''){ $temp = $this->common($table,$data); $zhujian = $temp['zhujian']; if(empty($_POST[$zhujian])){//add return $this->add($table,$data); }else{ $id = $_POST[$zhujian]; return $this->save($table,$id,$data); } } /** * 根据主键删除单表的数据 * @param string $table 表名 * @param int $id 主键id * @return string 返回结果 */ public function del($table,$id){ $temp = $this->common($table); $zhujian = $temp['zhujian']; $this->querySql = "DELETE FROM {$this->table} WHERE `{$zhujian}` = ?"; //释放前次的查询结果 if ( !empty($this->PDOStatement) ) $this->free(); $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->params[] = (int)$id; $this->bindPdoParam($this->params); $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } if($this->autoCommit != FALSE){ if($this->PDOStatement->rowCount()){ return $this->PDOStatement->rowCount(); }else{ return null; } } } /** * 根据所传主键批量删除数据 * @param string $table 表名 * @param string|array $ids 主键组成的字符串(必须以逗号隔开)或者数组 * @return int 返回删除总数 */ public function dels($table,$ids){ $temp = $this->common($table); $zhujian = $temp['zhujian']; //释放前次的查询结果 if ( !empty($this->PDOStatement) ) $this->free(); $right = ''; if(is_array($ids)){ $this->params = $ids; foreach($ids as $k=>$v){ if($k == 0){ $right.='?'; }else{ $right.=',?'; } } }else{ $this->params = explode(',',$ids); foreach($this->params as $k=>$v){ if($k == 0){ $right.='?'; }else{ $right.=',?'; } } } $this->querySql = "DELETE FROM {$this->table} WHERE `{$zhujian}` IN ($right)"; $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->bindPdoParam($this->params); $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } if($this->autoCommit != FALSE){ if($this->PDOStatement->rowCount()){ return $this->PDOStatement->rowCount(); }else{ return null; } } } /** * 根据条件删除数据 * @param string $table 表名 * @param array $condition 条件 * @return int 返回删除总数 */ public function delc($table,$condition){ $this->table = $this->db_press($table); //释放前次的查询结果 if ( !empty($this->PDOStatement) ) $this->free(); $conditions = $this->condition($condition); $right = $conditions['right']; $this->params = $conditions['params']; $this->querySql = "DELETE FROM {$this->table} WHERE $right"; $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->bindPdoParam($this->params); $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } if($this->autoCommit != FALSE){ if($this->PDOStatement->rowCount()){ return $this->PDOStatement->rowCount(); }else{ return null; } } } /** * 获取表的表名或者单表时的主键和字段 * @param string $table 表名集合 * @return 无返回，已将值设置为类变量 */ public function getTable($table){ if(!strpos($table,',')){//单表查找主键 $tables = $this->db_press($table); $this->PDOStatement = $this->linkId->query("DESC {$tables}"); if(self::$debug && !$this->PDOStatement){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } $result = $this->PDOStatement->fetch(PDO::FETCH_ASSOC); $this->pk = $result['Field']; }else{ $tables = explode(',',$table); foreach ($tables as $key => $value) { $tables[$key] = $this->db_press($value); } $tables = implode(',',$tables); } return $tables; } public function querySql($sql){ $this->querySql = $sql; $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } return $this->PDOStatement; } /** * 根据主键查询单条记录 * @param string $table 表名 * @param int $id 主键 * @return array/string 成功返回当条记录失败返回null */ public function find($table,$id){ $this->field = ($this->field)?($this->field) :'*'; if(!$this->where) $this->params = ''; $this->querySql = "SELECT {$this->field} FROM {$this->getTable($table)} WHERE `{$this->pk}` = ? {$this->where} {$this->group} {$this->having} {$this->order} {$this->limit}"; //销毁变量 $this->destroy(); $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->params[] = (int)$id; $this->bindPdoParam($this->params); $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } $results = $this->PDOStatement->fetch(PDO::FETCH_ASSOC); if($this->autoCommit != FALSE){ if($results){ return $results; }else{ return null; } } } /** * 查询符合条件的所有数据 * @param string $table 表名 * @return array/string 成功返回满足条件的所有记录失败返回null */ public function select($table){ $this->field = ($this->field)?($this->field) :'*'; if(!$this->where) $this->params = ''; $this->querySql = "SELECT {$this->field} FROM {$this->getTable($table)} {$this->join} WHERE 1 {$this->where} {$this->group} {$this->having} {$this->order} {$this->limit}"; //销毁变量 $this->destroy(); $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->bindPdoParam($this->params); $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo()); die; } } $results = $this->PDOStatement->fetchAll(PDO::FETCH_ASSOC); if($results){ return $results; }else{ return null; } } /** * 根据主键设置某个字段的值 * @param string $table 表名 * @param int $id 主键 * @param string $field_name 字段名 * @param string $field_value 字段值 * @return 返回设置的值 */ public function setField($table,$id,$field_name,$field_value){ $this->querySql = "UPDATE {$this->getTable($table)} SET `{$field_name}` = ? WHERE `{$this->pk}` = ?"; //释放前次的查询结果 if ( !empty($this->PDOStatement) ) $this->free(); $this->pk = ''; $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->params = array($field_value,(int)$id); $this->bindPdoParam($this->params); $results = $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } if($this->autoCommit != FALSE){ if($results){ return $field_value; }else{ return null; } } } /** * 根据查询字段返回需要的字段 * @param 查看_call()方法调用 $args * @param 查看_call()方法调用 $search * @param string $field_c 条件字段 * @param string $field_f 需要查找字段 * @return 返回需要查询的字段值 */ public function getFieldBy($args,$search){ $field_c = $search; $table = $args[0]; $table = $this->db_press($table); $where = "`{$field_c}` = ?";// $field_f = $args[2]; $this->querySql = "SELECT `{$field_f}` FROM {$table} WHERE {$where}"; //释放前次的查询结果 if ( !empty($this->PDOStatement) ) $this->free(); $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->params[] = $args[1]; $this->bindPdoParam($this->params); $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } $results = $this->PDOStatement->fetch(PDO::FETCH_ASSOC); if($results){ return $results[$field_f]; }else{ return null; } } /** * 将数据自动加上对应数据 * @param string $table 表名 * @param int $id 主键 * @param string $field 字段名 * @param int $num 增加数量 */ public function setInc($table,$id,$field,$num){ $this->getTable($table); $method = getFieldBy.$this->pk; $this->pk = ''; $fields = $this->$method($table,$id,$field); $fields+=$num; $results = $this->setField($table,$id,$field,$fields); if($this->autoCommit != FALSE){ if($results){ return $results; }else{ return null; } } } /** * 将数据自动减去对应数据最低值为0 * @param string $table 表名 * @param int $id 主键 * @param string $field 字段名 * @param int $num 减去数量 */ public function setDec($table,$id,$field,$num){ $this->getTable($table); $method = getFieldBy.$this->pk; $this->pk = ''; $fields = $this->$method($table,$id,$field); $fields -= $num; if($fields < 0){ $fields = 0; } $results = $this->setField($table,$id,$field,$fields); if($this->autoCommit != FALSE){ if($results){ return $results; }else{ return null; } } } /** * 查询满足条件的总数 * @param string table表名 * @param string field字段名 * @return 返回满足条件的数量 */ public function count($table,$field=''){ if(!strpos($table,',')){ $table = $this->db_press($table); }else{ $table = explode(',',$table); foreach ($table as $key => $value) { $value = $this->db_press($value); $table[$key] = $value; } $table = implode(',',$table); } $this->field = $field?"count($field) as counts":"count(*) as counts"; if(!$this->where) $this->params = ''; $this->querySql = "SELECT {$this->field} FROM {$table} {$this->join} WHERE 1 {$this->where} {$this->group} {$this->having} {$this->order} {$this->limit}"; //销毁变量 $this->destroy(); $this->PDOStatement = $this->linkId->prepare($this->querySql); $this->bindPdoParam($this->params); $this->PDOStatement->execute(); if(self::$debug){ $errorInfo = $this->PDOStatement->errorInfo(); if($errorInfo[0] != 0){ echo '<br/>bindSql:'.$this->querySql.'<br/>'; echo "\nPDOStatement::errorInfo():\n"; print_r($this->PDOStatement->errorInfo());die; } } $results = $this->PDOStatement->fetch(PDO::FETCH_ASSOC); $counts = $results["counts"]; return $counts?$counts:0; } /** * 查询分页方法 * @param string $table 表名 * @param string $page 当前页码 * @param string $num 默认显示页数10 * @param string $num_page 默认分页数8 * @param string $fields 需要查询的字段 * @param string $order 排序条件 * @param string $first 第一页（根据需要传入） * @param string $pre 上一页（根据需要传入） * @param string $next 下一页（根据需要传入） * @param string $end 尾页（根据需要传入） * @return array 成功返回数组$list['list']为查询数据$list['page_show']为分页样式 */ public function search($table,$page = '',$num = '',$num_page = '',$first = '第一页',$pre= '上一页',$next = '下一页',$end = '尾页'){ //每页显示数量 $_pageNum = $num ? $num : 10; $start = ceil(($page-1)*$_pageNum); if(!$this->where) $this->params = ''; //将值赋给变量否则会被销毁 $where1 = $this->where; $order1 = $this->order; $field1 = $this->field; $join1 = $this->join; $params = $this->params; //获取查询的总数 $counts = $this->count($table); //echo $this->_sql(); //$counts = count($results); //将变量再赋值给类变量，完成查询 $this->where = $where1; $this->order = $order1; $this->field = $field1; $this->join = $join1; $this->limit = " LIMIT $start,$_pageNum"; $this->params = $params; $list = $this->select($table); $this->destroy(); if($counts != 0){ //赋值数据结果集 $list['list'] = $list; //默认分页显示数8 $num_page = $num_page ? $num_page : 8; //赋值分页结果集 $list['page_show'] = $this->page($page,$counts,$num,$num_page,$first,$pre,$next,$end); //赋值查询的总数 $list['counts'] = $counts; return $list; }else{ return null; } } /** * 根据结果集数组进行分页 * @param array $array 结果集数据 * @param int $page 当前页码 * @param string $page 当前页码 * @param string $num 默认显示页数10 * @param string $num_page 默认分页数8 * @param string $first 第一页（根据需要传入） * @param string $pre 上一页（根据需要传入） * @param string $next 下一页（根据需要传入） * @param string $end 尾页（根据需要传入） * @return array 成功返回数组$list['list']为查询数据$list['page_show']为分页样式 $list['counts']为总数 */ public function getPage($array,$page,$num='',$num_page='',$first='第一页',$pre='上一页',$next='下一页',$ends='尾页'){ if(!empty($array)){ $counts = count($array); $_pageNum = $num ? $num : 10; $start = ceil(($page-1)*$_pageNum); $end = $start + $_pageNum; if($counts>0){ foreach ($array as $key=>$v){ if($key>=$start && $key<$end){ $list[] = $v; } } } $lists['list'] = $list; $num_page = $num_page ? $num_page : 8; $lists['page_show'] = $this->page($page,$counts,$num,$num_page,$first,$pre,$next,$ends); $lists['counts'] = $counts; return $lists; }else{ return null; } } /** * 分页方法 * @param int $page 当前页码 * @param int $counts 总数 * @param integer $num 每页显示数（默认10条） * @param integer $num_page 默认分页数（默认8条） * @param string $first 第一页（根据需要传入） * @param string $pre 上一页（根据需要传入） * @param string $next 下一页（根据需要传入） * @param string $end 尾页（根据需要传入） */ public function page($page,$counts,$num='',$num_page='',$first = '',$pre='',$next='',$end=''){ //获取当前url使得查询去除分页影响 $url = 'http://'.$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF'].'?'.$_SERVER['QUERY_STRING']; $url1 = strpos($url,"&page"); if(!empty($url1)){ $url = substr($url,0,$url1); }else{ $url = $url; } //分页显示样式处理 //第一页 $first = $first ? $first : ''; //上一页 $pre = $pre ? $pre : '«'; //下一页 $next = $next ? $next : '»'; //最后一页 $end = $end ? $end : ''; //分页结果处理 //默认显示数量为10 $_pageNum = $num ? $num : 10; //默认分页显示数8 $num_page = $num_page ? $num_page : 8; //总页数 $pages = ceil($counts/$_pageNum); //当前页面小于1 则为1 $page = $page<1?1:$page; //当前页大于总页数 则为总页数 $page = $page > $pages ? $pages : $page; //页数小当前页 则为当前页 $pages = $pages < $page ? $page : $pages; //计算开始页 //计算基数 $_start = 1 + floor(($page-1)/$num_page)*$num_page; $_start = $_start<1 ? 1 : $_start; //计算结束页 $_end = $_start + $num_page; $_end = $_end>$pages? $pages : $_end; //当前显示的页码个数不够最大页码数，在进行左右调整 $_curPageNum = $_end-$_start+1; //左调整 if($_curPageNum<$_pageNum && $_start>1){ $_start = $_start - ($_pageNum-$_curPageNum); $_start = $_start<1 ? 1 : $_start; $_curPageNum = $_end-$_start+1; } //右边调整 if($_curPageNum<$_pageNum && $_end<$pages){ $_end = $_end + ($_pageNum-$_curPageNum); $_end = $_end>$pages? $pages : $_end; } //初始化变量 $_pageHtml = ''; if(!empty($first)){ if($_start == 1){ $_pageHtml .= '<li><a title="第一页" href="'.$url.'&page=1">'.$first.'</a></li>'; }else{ $_pageHtml .= '<li><a title="第一页" href="'.$url.'&page=1">'.$first.'</a></li>'; } } if($page>1){ $_pageHtml .= '<li><a title="上一页" href="'.$url.'&page='.($page-1).'">'.$pre.'</a></li>'; } for ($i = $_start; $i <= $_end; $i++) { if($i == $page){ //当前页 $_pageHtml .= '<li><a class="current">'.$i.'</a></li>'; }else{ //跳转指定页 $_pageHtml .= '<li><a href="'.$url.'&page='.$i.'">'.$i.'</a></li>'; } } if(!empty($end)){ if($_end == $pages){ $_pageHtml .= '<li><a title="最后一页" href="'.$url.'&page='.$pages.'">'.$end.'</a></li>'; }else{ $_pageHtml .= '<li><a title="最后一页" href="'.$url.'&page='.$pages.'">'.$end.'</a></li>'; } } if($page<$_end){ $_pageHtml .= '<li><a title="下一页" href="'.$url.'&page='.($page+1).'">'.$next.'</a></li>'; } //返回分页数据 if($pages>1){ return $_pageHtml; }else{ return null; } }}?>//使用示例：
echo "测试PDO";
require_once 'libs/pdoModel.class.php';
$mysqlObj = new pdoModel();
$data['cat_id'] = '';
$data['cat_name'] = 'pdo';
$data['spec'] = 1;
$data['cname'] = 'pdo';
$result = $mysqlObj->add("categorys",$data);
if($result){
echo "添加成功".$result.'条数据';
}
echo $mysqlObj->getsql();
$data['cat_name'] = 'pdo1?get=2';
$data['spec'] = 1;
$result = $mysqlObj->save("category",104,$data);
if($result){
echo "修改成功".$result.'条数据';
}
echo $mysqlObj->getsql();
echo '<br>根据主键删除<br/>';
$result = $mysqlObj->del('category',85);
if($result){
echo "删除成功".$result.'条数据';
}
echo $mysqlObj->_sql();
echo '<br>根据主键批量删除<br/>';
$result = $mysqlObj->dels('category','81,82,83,84');
if($result){
echo "删除成功".$result.'条数据';
}
echo $mysqlObj->_sql();
echo '<br>根据条件删除<br/>';
$conditiondelc['parent_id'] = 77; //建议数组传递，安全性高一点
$conditiondelc = 'parent_id = 77';//字符串形式，不熟悉该封装的人用的会比较熟练一点
$result = $mysqlObj->delc('category',$conditiondelc);
if($result){
echo "删除成功".$result.'条数据';
}
echo $mysqlObj->_sql();
echo '<br>根据主键查找单条记录<br/>';
$result = $mysqlObj->field('cat_id')->find('category','64');
if($result){
var_dump($result);
echo "成功查询单条记录";
}
echo $mysqlObj->_sql();
echo '<br>根据特定条件查询所有记录<br/>';
$conditionselect['parent_id'] = 0;
$conditionselect['cat_name'] = array('terms'=>array('like','='),'value'=>array('%政策%','%1%'),'description'=>'or');
$conditionselect['class_id'] = array('terms'=>'=','value'=>'0','description'=>'and');
//建议数组传递，安全性高一点(数组里面千变万化，这里不多说)
//$conditionselect = "parent_id = 0 and (cat_name like '%政策%' or cat_name = '%1%') and class_id = 0"; //字符串形式，不熟悉该封装的人用的会比较熟练一点
$result = $mysqlObj->field('cat_id,cat_name')->where($conditionselect)->group('cat_id')->order('order_id asc,cat_id desc')->limit(0,1)->select('category');
if($result){
var_dump($result);
echo "成功查询所有记录";
}
echo $mysqlObj->_sql();
echo '<br>测试where等语句条件是否会影响，证明无影响<br/>';
$result = $mysqlObj->field('cat_id,cat_name')->select('category');
if($result){
var_dump($result);
echo "成功查询所有记录";
}
echo $mysqlObj->_sql();
echo '<br>根据主键设置某个字段的值<br/>';
$result = $mysqlObj->setField('category',68,'detail','办事指南1');
if($result){
echo "成功根据主键设置某个字段的值";
}
echo $mysqlObj->_sql();
echo '<br>根据查询字段返回需要的字段<br/>';
$result = $mysqlObj->getFieldBycat_id('category',68,'detail');
if($result){
echo "成功返回需要的字段detail的值为:".$result;
}
echo $mysqlObj->_sql();
echo '<br>将数据自动加上对应数据这里测试在原有基础上+2<br/>';
$result = $mysqlObj->setInc('category',64,'order_id',2);
if($result){
echo "返回该字段现有的数值:".$result;
}
echo $mysqlObj->_sql();
echo '<br>将数据自动减去对应数据这里测试在原有基础上-1<br/>';
$result = $mysqlObj->setDec('category',64,'order_id',1);
if($result){
echo "返回该字段现有的数值:".$result;
}
echo $mysqlObj->_sql();
echo '<br>查询获取的数量<br/>';//可使用where，order等除了field方法之外的连贯操作方法
$result = $mysqlObj->count('category','cat_id');
if($result){
echo "返回查询获取的数量值:".$result;
}
echo $mysqlObj->_sql();
echo '<br>开启事务<br/>';
try{
$result = $mysqlObj->beginTransaction();
//$mysqlObj->add('category',$data);
//$mysqlObj->save('category',64);
$mysqlObj->commit();
}catch(Exception $e){
echo $e->getMessage();
$mysqlObj->rollback();
}
if($result){
echo "返回查询获取的数量值:".$result;
}
//echo $mysqlObj->_sql();
1 0
- php mysql PDO封装
- PHP PDO(mysql) 封装类
- php 封装PDO类
- php PDO封装
- php 封装PDO类
- php mysql PDO使用
- php PDO连接MySql
- php mysql PDO使用
- php PDO连接mysql
- pdo+mysql+php
- php mysql PDO使用
- php mysql PDO使用
- php PDO mysql
- php PDO mysql问题解决
- PHP PDO操作MYSQL
- PHP PDO操作MYSQL
- PHP MySQL & PDO
- PDO使用归纳【PHP】 【任务】将PDO封装成类
- 安卓小日记练习（5）点击下栏选项显示菜单
- P，NP，NPC，NP-hard问题简述
- 通过过滤器控制静态资源的缓存时间
- css-伪类和伪元素的区别
- mysql乐观锁总结和实践
- php mysql PDO封装
- springMVC框架开发笔记 lesson2 注解开发
- IO技术（三）字符流和字符缓冲区
- 对象的序列化
- 构建自己的 LINUX 系统（一）
- noip2007 树网的核
- iOS代码规范整理（欢迎补充修改）
- 设计模式学习笔记之命令模式
- 文件加密移动与解密（winform版
