php mysql操作封装类_php数据库操作封装类

1 <?php2 /**3 * Desc: php操作mysql的封装类4 * Author zhifeng5 * Date: 2015/04/156 * 连接模式：PDO7 */

8

9 classMMysql {10

11 protected static $_dbh = null; //静态属性,所有数据库实例共用,避免重复连接数据库

12 protected $_dbType = 'mysql';13 protected $_pconnect = true; //是否使用长连接

14 protected $_host = 'localhost';15 protected $_port = 3306;16 protected $_user = 'root';17 protected $_pass = 'root';18 protected $_dbName = null; //数据库名

19 protected $_sql = false; //最后一条sql语句

20 protected $_where = '';21 protected $_order = '';22 protected $_limit = '';23 protected $_field = '*';24 protected $_clear = 0; //状态，0表示查询条件干净，1表示查询条件污染

25 protected $_trans = 0; //事务指令数

26

27 /**28 * 初始化类29 * @param array $conf 数据库配置30 */

31 public function __construct(array $conf) {32 class_exists('PDO') or die("PDO: class not exists.");33 $this->_host = $conf['host'];34 $this->_port = $conf['port'];35 $this->_user = $conf['user'];36 $this->_pass = $conf['passwd'];37 $this->_dbName = $conf['dbname'];38 //连接数据库

39 if ( is_null(self::$_dbh) ) {40 $this->_connect();41 }42 }43

44 /**45 * 连接数据库的方法46 */

47 protected function_connect() {48 $dsn = $this->_dbType.':host='.$this->_host.';port='.$this->_port.';dbname='.$this->_dbName;49 $options = $this->_pconnect ? array(PDO::ATTR_PERSISTENT=>true) : array();50 try{51 $dbh = new PDO($dsn, $this->_user, $this->_pass, $options);52 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //设置如果sql语句执行错误则抛出异常，事务会自动回滚

53 $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); //禁用prepared statements的仿真效果(防SQL注入)

54 } catch (PDOException $e) {55 die('Connection failed: ' . $e->getMessage());56 }57 $dbh->exec('SET NAMES utf8');58 self::$_dbh = $dbh;59 }60

61 /**62 * 字段和表名添加 `符号63 * 保证指令中使用关键字不出错 针对mysql64 * @param string $value65 * @return string66 */

67 protected function _addChar($value) {68 if ('*'==$value || false!==strpos($value,'(') || false!==strpos($value,'.') || false!==strpos($value,'`')) {69 //如果包含* 或者 使用了sql方法 则不作处理

70 } elseif (false === strpos($value,'`') ) {71 $value = '`'.trim($value).'`';72 }73 return $value;74 }75

76 /**77 * 取得数据表的字段信息78 * @param string $tbName 表名79 * @return array80 */

81 protected function _tbFields($tbName) {82 $sql = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME="'.$tbName.'" AND TABLE_SCHEMA="'.$this->_dbName.'"';83 $stmt = self::$_dbh->prepare($sql);84 $stmt->execute();85 $result = $stmt->fetchAll(PDO::FETCH_ASSOC);86 $ret = array();87 foreach ($result as $key=>$value) {88 $ret[$value['COLUMN_NAME']] = 1;89 }90 return $ret;91 }92

93 /**94 * 过滤并格式化数据表字段95 * @param string $tbName 数据表名96 * @param array $data POST提交数据97 * @return array $newdata98 */

99 protected function _dataFormat($tbName,$data) {100 if (!is_array($data)) return array();101 $table_column = $this->_tbFields($tbName);102 $ret=array();103 foreach ($data as $key=>$val) {104 if (!is_scalar($val)) continue; //值不是标量则跳过

105 if (array_key_exists($key,$table_column)) {106 $key = $this->_addChar($key);107 if (is_int($val)) {108 $val = intval($val);109 } elseif (is_float($val)) {110 $val = floatval($val);111 } elseif (preg_match('/^$\w*(\+|\-|\*|\/)?\w*$$/i', $val)) {112 //支持在字段的值里面直接使用其它字段 ,例如 (score+1) (name) 必须包含括号

113 $val = $val;114 } elseif (is_string($val)) {115 $val = '"'.addslashes($val).'"';116 }117 $ret[$key] = $val;118 }119 }120 return $ret;121 }122

123 /**124 * 执行查询 主要针对 SELECT, SHOW 等指令125 * @param string $sql sql指令126 * @return mixed127 */

128 protected function _doQuery($sql='') {129 $this->_sql = $sql;130 $pdostmt = self::$_dbh->prepare($this->_sql); //prepare或者query 返回一个PDOStatement

131 $pdostmt->execute();132 $result = $pdostmt->fetchAll(PDO::FETCH_ASSOC);133 return $result;134 }135

136 /**137 * 执行语句 针对 INSERT, UPDATE 以及DELETE,exec结果返回受影响的行数138 * @param string $sql sql指令139 * @return integer140 */

141 protected function _doExec($sql='') {142 $this->_sql = $sql;143 return self::$_dbh->exec($this->_sql);144 }145

146 /**147 * 执行sql语句，自动判断进行查询或者执行操作148 * @param string $sql SQL指令149 * @return mixed150 */

151 public function doSql($sql='') {152 $queryIps = 'INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|REVOKE|LOCK|UNLOCK';153 if (preg_match('/^\s*"?(' . $queryIps . ')\s+/i', $sql)) {154 return $this->_doExec($sql);155 }156 else{157 //查询操作

158 return $this->_doQuery($sql);159 }160 }161

162 /**163 * 获取最近一次查询的sql语句164 * @return String 执行的SQL165 */

166 public functiongetLastSql() {167 return $this->_sql;168 }169

170 /**171 * 插入方法172 * @param string $tbName 操作的数据表名173 * @param array $data 字段-值的一维数组174 * @return int 受影响的行数175 */

176 public function insert($tbName,array $data){177 $data = $this->_dataFormat($tbName,$data);178 if (!$data) return;179 $sql = "insert into ".$tbName."(".implode(',',array_keys($data)).") values(".implode(',',array_values($data)).")";180 return $this->_doExec($sql);181 }182

183 /**184 * 删除方法185 * @param string $tbName 操作的数据表名186 * @return int 受影响的行数187 */

188 public function delete($tbName) {189 //安全考虑,阻止全表删除

190 if (!trim($this->_where)) return false;191 $sql = "delete from ".$tbName." ".$this->_where;192 $this->_clear = 1;193 $this->_clear();194 return $this->_doExec($sql);195 }196

197 /**198 * 更新函数199 * @param string $tbName 操作的数据表名200 * @param array $data 参数数组201 * @return int 受影响的行数202 */

203 public function update($tbName,array $data) {204 //安全考虑,阻止全表更新

205 if (!trim($this->_where)) return false;206 $data = $this->_dataFormat($tbName,$data);207 if (!$data) return;208 $valArr = '';209 foreach($data as $k=>$v){210 $valArr[] = $k.'='.$v;211 }212 $valStr = implode(',', $valArr);213 $sql = "update ".trim($tbName)." set ".trim($valStr)." ".trim($this->_where);214 return $this->_doExec($sql);215 }216

217 /**218 * 查询函数219 * @param string $tbName 操作的数据表名220 * @return array 结果集221 */

222 public function select($tbName='') {223 $sql = "select ".trim($this->_field)." from ".$tbName." ".trim($this->_where)." ".trim($this->_order)." ".trim($this->_limit);224 $this->_clear = 1;225 $this->_clear();226 return $this->_doQuery(trim($sql));227 }228

229 /**230 * @param mixed $option 组合条件的二维数组，例：$option['field1'] = array(1,'=>','or')231 * @return $this232 */

233 public function where($option) {234 if ($this->_clear>0) $this->_clear();235 $this->_where = ' where ';236 $logic = 'and';237 if (is_string($option)) {238 $this->_where .= $option;239 }240 elseif (is_array($option)) {241 foreach($option as $k=>$v) {242 if (is_array($v)) {243 $relative = isset($v[1]) ? $v[1] : '=';244 $logic = isset($v[2]) ? $v[2] : 'and';245 $condition = ' ('.$this->_addChar($k).' '.$relative.' '.$v[0].') ';246 }247 else{248 $logic = 'and';249 $condition = ' ('.$this->_addChar($k).'='.$v.') ';250 }251 $this->_where .= isset($mark) ? $logic.$condition : $condition;252 $mark = 1;253 }254 }255 return $this;256 }257

258 /**259 * 设置排序260 * @param mixed $option 排序条件数组 例:array('sort'=>'desc')261 * @return $this262 */

263 public function order($option) {264 if ($this->_clear>0) $this->_clear();265 $this->_order = ' order by ';266 if (is_string($option)) {267 $this->_order .= $option;268 }269 elseif (is_array($option)) {270 foreach($option as $k=>$v){271 $order = $this->_addChar($k).' '.$v;272 $this->_order .= isset($mark) ? ','.$order : $order;273 $mark = 1;274 }275 }276 return $this;277 }278

279 /**280 * 设置查询行数及页数281 * @param int $page pageSize不为空时为页数，否则为行数282 * @param int $pageSize 为空则函数设定取出行数，不为空则设定取出行数及页数283 * @return $this284 */

285 public function limit($page,$pageSize=null) {286 if ($this->_clear>0) $this->_clear();287 if ($pageSize===null) {288 $this->_limit = "limit ".$page;289 }290 else{291 $pageval = intval( ($page - 1) * $pageSize);292 $this->_limit = "limit ".$pageval.",".$pageSize;293 }294 return $this;295 }296

297 /**298 * 设置查询字段299 * @param mixed $field 字段数组300 * @return $this301 */

302 public function field($field){303 if ($this->_clear>0) $this->_clear();304 if (is_string($field)) {305 $field = explode(',', $field);306 }307 $nField = array_map(array($this,'_addChar'), $field);308 $this->_field = implode(',', $nField);309 return $this;310 }311

312 /**313 * 清理标记函数314 */

315 protected function_clear() {316 $this->_where = '';317 $this->_order = '';318 $this->_limit = '';319 $this->_field = '*';320 $this->_clear = 0;321 }322

323 /**324 * 手动清理标记325 * @return $this326 */

327 public functionclearKey() {328 $this->_clear();329 return $this;330 }331

332 /**333 * 启动事务334 * @return void335 */

336 public functionstartTrans() {337 //数据rollback 支持

338 if ($this->_trans==0) self::$_dbh->beginTransaction();339 $this->_trans++;340 return;341 }342

343 /**344 * 用于非自动提交状态下面的查询提交345 * @return boolen346 */

347 public functioncommit() {348 $result = true;349 if ($this->_trans>0) {350 $result = self::$_dbh->commit();351 $this->_trans = 0;352 }353 return $result;354 }355

356 /**357 * 事务回滚358 * @return boolen359 */

360 public functionrollback() {361 $result = true;362 if ($this->_trans>0) {363 $result = self::$_dbh->rollback();364 $this->_trans = 0;365 }366 return $result;367 }368

369 /**370 * 关闭连接371 * PHP 在脚本结束时会自动关闭连接。372 */

373 public functionclose() {374 if (!is_null(self::$_dbh)) self::$_dbh = null;375 }376

377 }