<?php
class MysqliCommon {
private $mysqli; //内部建立mysqli对象
private $dbErrorFile = ‘‘; //错误日志文件
/**
*
* @param array $dbConfi
* 类似于array("host"=>"127.0.0.1","dbname"=>"dilicms2","user"=>"root","password"=>"123qwe")
* @param type $dbErrorFile
*/
public function __construct($dbConfi, $dbErrorFile = ‘‘, $charSet = ‘utf8‘) {
$this->mysqli = @new mysqli($dbConfi[‘host‘], $dbConfi[‘user‘], $dbConfi[‘password‘], $dbConfi[‘dbname‘]);
$this->dbErrorFile = $dbErrorFile;
if ($this->mysqli->connect_errno) {
$connectErrorMsg = date(‘Y-m-d H:i:s‘, time()) . ">>> Connection failed: " . mb_convert_encoding($this->mysqli->connect_error, ‘UTF-8‘, ‘GBK‘) . "\r\n";
$this->writeErrorLog($connectErrorMsg);
exit();
}
//设置访问数据库的字符集
$this->mysqli->query(‘set names ‘ . $charSet);
}
/**
* 向错误日志文件中记录错误日志
* @param string $errorMsg
*/
public function writeErrorLog($errorMsg = ‘‘) {
$this->dbErrorFile;
if ($this->dbErrorFile != ‘‘) {
$EMsg = "";
if ($errorMsg != ‘‘) {
$EMsg = $errorMsg;
} else {
$EMsg = date(‘Y-m-d H:i:s‘, time()) . ">>> Connection failed: " . mb_convert_encoding($this->mysqli->error, ‘UTF-8‘, ‘GBK‘) . "\r\n";
}
file_put_contents($this->dbErrorFile, $EMsg, FILE_APPEND);
}
}
/**
* 根据表名 字段 条件 选项 查询数据库数据
* @param string $table //表名
* @param array $fields //字段数组
* @param array $wheres //条件数组
* @param array $options //可选数据组
*/
public function seach($table, $fields, $wheres, $options = array()) {
$sql = ‘SELECT ‘ . implode($fields, ", ") . ‘ FROM ‘ . $table . ‘ WHERE 1=1 ‘;
$wheresSql = $this->dealWhere($wheres);
$sql = $sql . $wheresSql;
if (isset($options[‘limit‘])) {
$sql.=‘ LIMIT ‘ . $options[‘limit‘];
}
return $this->run($sql);
}
/**
* 执行SQl语句
* @param string $sql
* @return mixed
*/
private function run($sql) {
$result = $this->mysqli->query($sql);
if (!$result) {
$this->writeErrorLog();
return false;
}
if (preg_match("/^(" . implode("|", array("select", "describe", "pragma")) . ") /i", $sql)) {
$resultArray = array();
if ($result->num_rows > 0) {
while ($row = $result->fetch_array()) {
$resultArray[] = $row;
}
}
$result->free();
return $resultArray;
} elseif (preg_match("/^(" . implode("|", array("delete", "insert", "update")) . ") /i", $sql)) {
return $this->mysqli->affected_rows;
}
return false;
}
/**
* 处理查询条件
* @param array $wheres
* @return string
*/
public function dealWhere($wheres) {
$wheresSql = ‘‘;
foreach ($wheres as $key => $value) {
$cond = preg_replace("/\s+/", " ", $key);
$fieldWh = explode(‘ ‘, trim($cond));
if (count($fieldWh) == 1) {
$wheresSql .=‘ AND ‘ . $cond . ‘=‘ . (is_string($value) ? ‘ "‘ . $value . ‘" ‘ : $value . ‘ ‘);
} else if (count($fieldWh) == 2) {
$conStr = trim(strtoupper($fieldWh[0]));
if ($conStr == ‘OR‘) {
$wheresSql .=‘ OR ‘ . $cond . ‘=‘ . (is_string($value) ? ‘ "‘ . $value . ‘" ‘ : $value . ‘ ‘);
} else {
$wheresSql.=‘ AND ‘ . $fieldWh[0] . ‘ ‘ . $fieldWh[1] . (is_string($value) ? ‘ "‘ . $value . ‘" ‘ : $value . ‘ ‘);
}
} else if (count($fieldWh) == 3) {
$conStr = trim(strtoupper($fieldWh[0]));
if ($conStr == ‘OR‘) {
$wheresSql.=‘ OR ‘ . $fieldWh[0] . ‘ ‘ . $fieldWh[1] . (is_string($value) ? ‘ "‘ . $value . ‘" ‘ : $value . ‘ ‘);
}
}
}
return $wheresSql;
}
/**
* 开启事务
*/
public function beginTransaction() {
$this->mysqli->autocommit(false);
}
/**
* 结束事务
* @return boolean
*/
public function complectTransaction() {
if (!$this->mysqli->errno) {
$this->mysqli->commit();
return true;
} else {
$this->mysqli->rollback();
$this->writeErrorLog();
}
return false;
}
public function insert($table, $values) {
$sql = "INSERT INTO " . $table . " ";
$columns = "";
$columnsVal = "";
foreach ($values as $key => $value) {
$columns .= $key . ‘,‘;
$columnsVal.=(is_string($value) ? ‘ "‘ . $this->mysqli->real_escape_string($value) . ‘",‘ : $value . ‘,‘);
}
$sql = $sql . "(" . substr($columns, 0, strlen($columns) - 1) . ") VALUES (" . substr($columnsVal, 0, strlen($columnsVal) - 1) . ")";
return $this->run($sql);
}
public function doClose() {
$this->mysqli->close();
unset($this->mysqli);
}
public function delete($table, $wheres) {
$sql = "DELETE FROM " . $table . ‘ WHERE 1=1 ‘;
$wheresSql = $this->dealWhere($wheres);
$sql = $sql . $wheresSql;
return $this->run($sql);
}
public function update($table, $values, $wheres) {
$sql = "UPDATE " . $table . " SET ";
$count = 0;
foreach ($values as $key => $value) {
if ($count > 0) {
$sql .= ", ";
}
$count++;
$sql .= $key . " = " . (is_string($value) ? ‘ "‘ . $this->mysqli->real_escape_string($value) . ‘" ‘ : $value . ‘ ‘);
}
$sql2 = ‘ WHERE 1=1 ‘;
$wheresSql = $this->dealWhere($wheres);
$sql2 = $sql2 . $wheresSql;
$sql = $sql . $sql2;
return $this->run($sql);
}
}本文出自 “我的IT生涯” 博客，请务必保留此出处http://quietnight.blog.51cto.com/7163892/1675674
原文：http://quietnight.blog.51cto.com/7163892/1675674