db_pdo.class.php sql;}/** * 打开数据库连接,有可能不真实连接数据库 * @param $config数据库连接参数 * * @return void */public function open($config) {$this-config = $config;if($config['autoconnect'] == 1) {$this-connec
sql; } /** * 打开数据库连接,有可能不真实连接数据库 * @param $config 数据库连接参数 * * @return void */ public function open($config) { $this->config = $config; if($config['autoconnect'] == 1) { $this->connect(); } } /** * 真正开启数据库连接 * * @return void */ public function connect() { $config = $this->config; if(empty($config)) { exit("The master database is not found, Please checking 'data/config.php'"); } $dsn = "mysql:dbname={$config['database']};host={$config['hostname']};port={$config['port']}"; $options = $config['pconnect'] ? array(PDO::ATTR_PERSISTENT=>true) : array(); try { $this->link = new PDO($dsn, $config['username'], $config['password'], $options); $this->link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //设置如果sql语句执行错误则抛出异常,事务会自动回滚 $this->link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); //禁用prepared statements的仿真效果(防SQL注入) } catch (PDOException $e) { die('Connection failed: ' . $e->getMessage()); } $sql = "SET NAMES '{$config['charset']}';"; $this->link->exec($sql); $this->link->exec("SET sql_mode='';"); return $this->link; } public function prepare($sql) { if(!is_object($this->link)) { $this->connect(); } $this->sql = $sql; $this->statement = $this->link->prepare($sql); return $this->statement; } /** * 执行sql查询 * @param $data 需要查询的字段值[例`name`,`gender`,`birthday`] * @param $table 数据表 * @param $where 查询条件[例`name`='$name'] * @param $limit 返回结果范围[例:10或10,10 默认为空] * @param $order 排序方式 [默认按数据库默认方式排序] * @param $group 分组方式 [默认为空] * @param $key 返回数组按键名排序 * @return array 查询结果集数组 */ public function select($data, $table, $where = '', $limit = '', $order = '', $group = '', $key = '') { $where = $where == '' ? '' : ' WHERE '.$where; $order = $order == '' ? '' : ' ORDER BY '.$order; $group = $group == '' ? '' : ' GROUP BY '.$group; $limit = $limit == '' ? '' : ' LIMIT '.$limit; $field = explode(',', $data); array_walk($field, array($this, 'add_special_char')); $data = implode(',', $field); $sql = 'SELECT '.$data.' FROM `'.$this->config['database'].'`.`'.$table.'`'.$where.$group.$order.$limit; $statement = $this->prepare($sql); $result = $statement->execute(); if (!$result) { return false; } else { if (empty($key)) { return $statement->fetchAll(pdo::FETCH_ASSOC); } else { $temp = $statement->fetchAll(pdo::FETCH_ASSOC); $rs = array(); if (!empty($temp)) { foreach ($temp as $key => &$row) { if (isset($row[$key])) { $rs[$row[$key]] = $row; } else { $rs[] = $row; } } } return $rs; } } } /** * 获取单条记录查询 * @param $data 需要查询的字段值[例`name`,`gender`,`birthday`] * @param $table 数据表 * @param $where 查询条件 * @param $order 排序方式 [默认按数据库默认方式排序] * @param $group 分组方式 [默认为空] * @return array/null 数据查询结果集,如果不存在,则返回空 */ public function get_one($data, $table, $where = '', $order = '', $group = '') { $where = $where == '' ? '' : ' WHERE '.$where; $order = $order == '' ? '' : ' ORDER BY '.$order; $group = $group == '' ? '' : ' GROUP BY '.$group; $limit = ' LIMIT 1'; $field = explode( ',', $data); array_walk($field, array($this, 'add_special_char')); $data = implode(',', $field); $sql = 'SELECT '.$data.' FROM `'.$this->config['database'].'`.`'.$table.'`'.$where.$group.$order.$limit; $statement = $this->prepare($sql); $result = $statement->execute(); if (!$result) { return false; } else { return $statement->fetch(pdo::FETCH_ASSOC); } } /** * 直接执行sql查询 * @param $sql 查询sql语句 * @return boolean/query resource 如果为查询语句,返回资源句柄,否则返回true/false */ public function query($sql) { if(!is_object($this->link)) { $this->connect(); } $this->sql = $sql; return $this->link->exec($sql); /* $statement = $this->prepare($sql); $result = $statement->execute(); if (!$result) { return false; } else { return $statement->rowCount(); } */ } /** * 执行添加记录操作 * @param $data 要增加的数据,参数为数组。数组key为字段值,数组值为数据取值 * @param $table 数据表 * @return boolean */ public function insert($data, $table, $return_insert_id = false, $replace = false) { if(!is_array( $data ) || $table == '' || count($data) == 0) { return false; } $fielddata = array_keys($data); $valuedata = array_values($data); array_walk($fielddata, array($this, 'add_special_char')); array_walk($valuedata, array($this, 'escape_string')); $field = implode (',', $fielddata); $value = implode (',', $valuedata); $cmd = $replace ? 'REPLACE INTO' : 'INSERT INTO'; $sql = $cmd.' `'.$this->config['database'].'`.`'.$table.'`('.$field.') VALUES ('.$value.')'; $return = $this->query($sql); return $return_insert_id ? $this->insert_id() : $return; } /** * 获取最后一次添加记录的主键号 * @return int */ public function insert_id() { if(!is_object($this->link)) { $this->connect(); } return $this->link->lastInsertId(); } /** * 执行更新记录操作 * @param $data 要更新的数据内容,参数可以为数组也可以为字符串,建议数组。 * 为数组时数组key为字段值,数组值为数据取值 * 为字符串时[例:`name`='phpcms',`hits`=`hits`+1]。 * 为数组时[例: array('name'=>'phpcms','password'=>'123456')] * 数组可使用array('name'=>'+=1', 'base'=>'-=1');程序会自动解析为`name` = `name` + 1, `base` = `base` - 1 * @param $table 数据表 * @param $where 更新数据时的条件 * @return boolean */ public function update($data, $table, $where = '') { if($table == '' or $where == '') { return false; } $where = ' WHERE '.$where; $field = ''; if(is_string($data) && $data != '') { $field = $data; } elseif (is_array($data) && count($data) > 0) { $fields = array(); foreach($data as $k=>$v) { switch (substr($v, 0, 2)) { case '+=': $v = substr($v,2); if (is_numeric($v)) { $fields[] = $this->add_special_char($k).'='.$this->add_special_char($k).'+'.$this->escape_string($v, '', false); } else { continue; } break; case '-=': $v = substr($v,2); if (is_numeric($v)) { $fields[] = $this->add_special_char($k).'='.$this->add_special_char($k).'-'.$this->escape_string($v, '', false); } else { continue; } break; default: $fields[] = $this->add_special_char($k).'='.$this->escape_string($v); } } $field = implode(',', $fields); } else { return false; } $sql = 'UPDATE `'.$this->config['database'].'`.`'.$table.'` SET '.$field.$where; return $this->query($sql); } /** * 执行删除记录操作 * @param $table 数据表 * @param $where 删除数据条件,不充许为空。 * 如果要清空表,使用empty方法 * @return boolean */ public function delete($table, $where) { if ($table == '' || $where == '') { return false; } $where = ' WHERE '.$where; $sql = 'DELETE FROM `'.$this->config['database'].'`.`'.$table.'`'.$where; return $this->query($sql); } public function fetchcolumn($sql, $column = 0) { $statement = $this->prepare($sql); $result = $statement->execute(); if (!$result) { return false; } else { return $statement->fetchColumn($column); } } public function fetch($sql) { $statement = $this->prepare($sql); $result = $statement->execute(); if (!$result) { return false; } else { return $statement->fetch(pdo::FETCH_ASSOC); } } public function fetchall($sql, $keyfield = '') { $statement = $this->prepare($sql); $result = $statement->execute(); if (!$result) { return false; } else { if (empty($keyfield)) { return $statement->fetchAll(pdo::FETCH_ASSOC); } else { $temp = $statement->fetchAll(pdo::FETCH_ASSOC); $rs = array(); if (!empty($temp)) { foreach ($temp as $key => &$row) { if (isset($row[$keyfield])) { $rs[$row[$keyfield]] = $row; } else { $rs[] = $row; } } } return $rs; } } } /** * 获取最后数据库操作影响到的条数 * @return int * @desc 暂未实现,稍后处理 */ public function affected_rows() { if(!is_object($this->link)) { $this->connect(); } return $this->statement->rowCount(); } /** * 获取数据表字段数 兼容CMS数据库管理工具 * @desc 暂未实现,稍后处理 */ public function num_rows($sql) { } /** * 获取数据表行数 兼容CMS数据库管理工具 * @desc 暂未实现,稍后处理 */ public function num_fields($sql) { } /** * 获取数据表主键 * @param $table 数据表 * @return array */ public function get_primary($table) { $rs = $this->fetchall("SHOW COLUMNS FROM $table"); foreach($rs as $r){ if($r['Key'] == 'PRI') break; } return $r['Field']; } /** * 获取表字段 * @param $table 数据表 * @return array */ public function get_fields($table) { $fields = array(); $rs = $this->fetchall("SHOW COLUMNS FROM $table"); foreach($rs as $r){ $fields[$r['Field']] = $r['Type']; } return $fields; } /** * 检查不存在的字段 * @param $table 表名 * @return array */ public function check_fields($table, $array) { $fields = $this->get_fields($table); $nofields = array(); foreach($array as $v) { if(!array_key_exists($v, $fields)) { $nofields[] = $v; } } return $nofields; } /** * 检查表是否存在 * @param $table 表名 * @return boolean */ public function table_exists($table) { $tables = $this->list_tables(); return in_array($table, $tables) ? 1 : 0; } /** * 获取表 * @return array */ public function list_tables() { $tables = array(); $rs = $this->fetchall("SHOW TABLES"); foreach($rs as $r){ $tables[] = $r['Tables_in_'.$this->config['database']]; } return $tables; } /** * 检查字段是否存在 * @param $table 表名 * @return boolean */ public function field_exists($table, $field) { $fields = $this->get_fields($table); return array_key_exists($field, $fields); } public function begin() { $this->link->beginTransaction(); } public function commit() { $this->link->commit(); } public function rollback() { $this->link->rollBack(); } public function error() { if(!is_object($this->link)) { $this->connect(); } return $this->link->errorInfo(); } public function errno() { if(!is_object($this->link)) { $this->connect(); } return intval($this->link->errorCode()); } /** * 获取mysql 版本号 */ public function version() { $sql = 'SELECT VERSION();'; return $this->fetchcolumn($sql); } /** * 错误展示信息,暂未添加debug * 此处可能有误,暂未实现 */ public function halt($message = '', $sql = '') { if($this->config['debug']) { $this->errormsg = "MySQL Query : $sql
MySQL Error : ".$this->error()."
MySQL Errno : ".$this->errno()."
Message : $message "; $msg = $this->errormsg; echo ' '.$msg.' '; exit; } else { return false; } } /** * 关闭连接 * PHP 在脚本结束时会自动关闭连接。 */ public function close() { if (!is_null($this->link)) $this->link->close(); } /** * 对字段两边加反引号,以保证数据库安全 * @param $value 数组值 */ public 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 */ public function escape_string(&$value, $key='', $quotation = 1) { if ($quotation) { $q = '\''; } else { $q = ''; } $value = $q.$value.$q; return $value; } }