<?php
include_once(path_format('config/config.php'));
class Mysql {
private $conn = null;
private $tag = "Mysql";
public function __construct() {
}
public function connect($c) {
if(!isset($c['port'])){
$c['port'] = '3306';
}
//是否开启长链接 true:是 false:否
if (isset($c['pconnect']) && true == $c['pconnect']) {
$option[PDO::ATTR_PERSISTENT] = true;
} else {
$option[PDO::ATTR_PERSISTENT] = false;
}
try {
$dsn = "mysql:host={$c['host']};dbname={$c['dbname']};charset={$c['charset']};port={$c['port']};";
if ($c['charset']) {
$option[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES '.$c['charset'];
}
$this->conn = new PDO($dsn, $c['username'], $c['password'], $option);
return true;
} catch (Exception $e) {
Config::$logger->error($this->tag, "Could not connect : " . $e->getMessage());
return false;
}
}
public function close() {
$this->conn = null;
return true;
}
public function find($sql) {
$data = array();
Config::$logger->debug($this->tag, $sql);
$result = $this->conn->query($sql,PDO::FETCH_ASSOC);
if(!$result) {
return $data;
}
foreach ($result as $row) {
$data[] = $row;
}
return $data;
}
/**
* @param $table 表名
* @param $columns 列名
* @param $where 条件
* @param string $other
* @return array
*/
public function select($table, $columns, $where, $other = '') {
if(is_array($columns)) {
$columns = implode(',',$columns);
}
if(empty($columns)) {
$columns = '*';
}
$condition = '';
if(is_array($where) && !empty($where)) {
foreach ($where as $k => $v) {
$condition .= "`$k` = '$v' AND ";
}
$condition = substr($condition, 0, strlen($condition) - 5);
}
$condition = empty($condition) ? $other : "WHERE $condition $other";
$sql = "SELECT $columns FROM `{$table}` $condition";
$data = [];
Config::$logger->debug($this->tag, $sql);
$result = $this->conn->query($sql,PDO::FETCH_ASSOC);
if(!$result) {
return $data;
}
foreach ($result as $row) {
$data[] = $row;
}
return $data;
}
public function insert($table, $row) {
$attrColumns = implode(',',array_keys($row));
$perch = substr(str_repeat('?,',count($row)),0,-1);
$sql = "insert into {$table}({$attrColumns}) values({$perch})";
$stmt = $this->conn->prepare($sql);
$i = 1;
$bindParams = [];
foreach ($row as $key=>$val) {
$bindParams[] = $val;
$stmt->bindValue($i,$val);
$i++;
}
// print_r($this->getRealSql($sql,$bindParams));die;
// file_put_contents('/data/web/xy/logs/zjh.log', 'insertError'.json_encode($this->getRealSql($sql,$bindParams))."\t\n", FILE_APPEND);
Config::$logger->debug($this->tag, $this->getRealSql($sql,$bindParams));
$stmt->execute();
$insertId = $this->conn->lastInsertId();
// print_r($this->getRealSql($sql,$bindParams));die;
$stmt = null;
return !$insertId ? 0 : $insertId;
}
public function update($table, $row, $where) {
$attrColumns = [];
$bindParams = [];
foreach ($row as $key=>$val) {
$bindParams[] = $val;
$attrColumns[] = $key.'=?';
}
$attrColumns = implode(',',$attrColumns);
$condition = [];
if(is_array($where)) {
foreach ($where as $key=>$val) {
$bindParams[] = $val;
$condition[] = $key.'=?';
}
$condition = implode(' AND ',$condition);
} else {
$condition = '';
}
$sql = "update {$table} set {$attrColumns} where {$condition}";
$stmt = $this->conn->prepare($sql);
$i = 1;
foreach ($bindParams as $val) {
$stmt->bindValue($i,$val);
$i++;
}
$stmt->execute();
$affectRow = $stmt->rowCount();
Config::$logger->debug($this->tag, $this->getRealSql($sql,$bindParams));
//Config::$logger->info($this->tag, $this->getRealSql($sql,$bindParams));
$stmt = null;
return $affectRow;
}
public function insert_or_update($table, $row) {
$stat = '';
foreach ($row as $k => $v) {
$stat .= "`$k` = '$v',";
}
$stat = substr($stat, 0, strlen($stat) - 1);
$sql = "INSERT INTO `{$table}` SET $stat ON DUPLICATE KEY UPDATE $stat";
Config::$logger->debug($this->tag, $sql);
return $this->conn->exec($sql);
}
public function query($sql) {
Config::$logger->debug($this->tag, $sql);
$data = array();
$result = $this->conn->query($sql,PDO::FETCH_ASSOC);
if(!$result) {
return $data;
}
foreach ($result as $row) {
$data[] = $row;
}
return $data;
}
public function exec_sql($sql) {
Config::$logger->debug($this->tag, $sql);
return $this->conn->exec($sql);
}
/**
* 根据参数绑定组装最终的SQL语句 便于调试
* @access public
* @param string $sql 带参数绑定的sql语句
* @param array $bind 参数绑定列表
* @return string
*/
public function getRealSql($sql, array $bind = [])
{
if ($bind) {
foreach ($bind as $key => $val) {
$value = is_array($val) ? $val[0] : $val;
$type = is_array($val) ? $val[1] : PDO::PARAM_STR;
if (PDO::PARAM_STR == $type) {
$value = "'".$value."'";
} elseif (PDO::PARAM_INT == $type) {
$value = (float) $value;
}
// 判断占位符
$sql = is_numeric($key) ?
substr_replace($sql, $value, strpos($sql, '?'), 1) :
str_replace(
[':' . $key . ')', ':' . $key . ',', ':' . $key . ' '],
[$value . ')', $value . ',', $value . ' '],
$sql . ' ');
}
}
return rtrim($sql);
}
}
?>