<?php
class Mysql
{
public $link;
public function __construct()
{
$this->conn();
}
/**
* 连接数据库，从配置文件读取配置信息
*/
public function conn()
{
$cfg = require 'config.php';
try {
$this->link = new PDO("mysql:dbname={$cfg['databaseName']};host={$cfg['host']};charset={$cfg['charset']};port={$cfg['port']}", $cfg['name'], $cfg['password']);
$this->link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // 设置禁止本地模拟prepare
//$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// 设置捕获异常
} catch (PDOException $e) {
die("Error: " . $e->getMessage());
}
}
/**
* 查询多行数据
* @param string $table 表名字
* @param string $where where条件
* @param string $field 字段
* @param string $additional 附加sql语句
* @return array
*/
public function getAll($table, $where = [], $field = "*", $additional = '')
{
if (strpos($field, ",") !== false) {
$arr = explode(",", $field);
$str = '';
foreach ($arr as $v) {
$str .= "`{$v}`,";
}
$field = substr($str, 0, -1);
} else if ($field != "*") {
$field = "`{$field}`";
}
$sql = "SELECT {$field} FROM `{$table}`";
$sql2 = '';
$value = [];
if ($where) {
if (!is_array($where[0])) {
if (strtolower($where[1]) == 'in') {
$where[1] = 'IN';
$sql2 = " `{$where[0]}` {$where[1]} (";
if (is_array($where[2])) {
foreach ($where[2] as $v) {
$value[] = $v;
$sql2 .= '?,';
}
} else {
$value[] = $where[2];
$sql2 .= '?';
}
$sql2 = rtrim($sql2, ',') . ')';
} else {
$value[] = $where[2];
$sql2 = " `{$where[0]}` {$where[1]} ?";
}
} else {
foreach ($where as $v) {
if (strtolower($v[1]) == 'in') {
$v[1] = 'IN';
$sql2 .= " `{$v[0]}` {$v[1]} (";
if (is_array($v[2])) {
foreach ($v[2] as $v2) {
$value[] = $v2;
$sql2 .= "?,";
}
} else {
$value[] = $v[2];
$sql2 .= "?";
}
$sql2 = rtrim($sql2, ',') . ') AND';
} else {
$value[] = $v[2];
$sql2 .= " `{$v[0]}` {$v[1]} ? AND";
}
}
$sql2 = substr($sql2, 0, -4);
}
if ($sql2) {
$sql .= " WHERE " . $sql2;
}
}
if ($additional) {
$sql .= ' ' . $additional;
}
$res = $this->link->prepare($sql);
$res->execute($value);
$data = $res->fetchAll(PDO::FETCH_ASSOC);
return $data;
}
/**
* 查询单行数据
* @param string $table 表名字
* @param string $where where条件
* @param string $field 字段
* @param string $additional 附加sql语句
* @return array
*/
public function getRow($table, $where = [], $field = "*", $additional = '')
{
if (strpos($field, ",") !== false) {
$arr = explode(",", $field);
$str = '';
foreach ($arr as $v) {
$str .= "`{$v}`,";
}
$field = substr($str, 0, -1);
} else if ($field != "*") {
$field = "`{$field}`";
}
$sql = "SELECT {$field} FROM `{$table}`";
$sql2 = '';
$value = [];
if ($where) {
if (!is_array($where[0])) {
if (strtolower($where[1]) == 'in') {
$where[1] = 'IN';
$sql2 = " `{$where[0]}` {$where[1]} (";
if (is_array($where[2])) {
foreach ($where[2] as $v) {
$value[] = $v;
$sql2 .= '?,';
}
} else {
$value[] = $where[2];
$sql2 .= '?';
}
$sql2 = rtrim($sql2, ',') . ')';
} else {
$value[] = $where[2];
$sql2 = " `{$where[0]}` {$where[1]} ?";
}
} else {
foreach ($where as $v) {
if (strtolower($v[1]) == 'in') {
$v[1] = 'IN';
$sql2 .= " `{$v[0]}` {$v[1]} (";
if (is_array($v[2])) {
foreach ($v[2] as $v2) {
$value[] = $v2;
$sql2 .= "?,";
}
} else {
$value[] = $v[2];
$sql2 .= "?";
}
$sql2 = rtrim($sql2, ',') . ') AND';
} else {
$value[] = $v[2];
$sql2 .= " `{$v[0]}` {$v[1]} ? AND";
}
}
$sql2 = substr($sql2, 0, -4);
}
if ($sql2) {
$sql .= " WHERE " . $sql2;
}
}
if ($additional) {
$sql .= ' ' . $additional;
}
$res = $this->link->prepare($sql);
$res->execute($value);
$data = $res->fetch(PDO::FETCH_ASSOC);
return $data;
}
/**
* 自动创建sql语句并执行
* @param string $table 表名字
* @param array $data 关联数组 键/值与表的列/值对应
* @param string $act 1为insert，2为update
* @param array $where 条件，用于update
* @return int 成功为insert产生的主键值,update是影响的行数，失败为0
*/
public function exec($table, $data, $act = 1, $where = [])
{
$value = [];
if ($act == 1) {
$sql = "INSERT INTO `{$table}` (`";
$sql .= implode('`,`', array_keys($data)) . '`)';
$str = '';
foreach ($data as $v) {
$str .= "?,";
}
$str = substr($str, 0, -1);
$sql .= " VALUES ({$str})";
$value = array_values($data);
} else {
$sql = "UPDATE `{$table}` SET ";
foreach ($data as $k => $v) {
$sql .= "`" . $k . '`= ' . " ?,";
$value[] = $v;
}
$sql = rtrim($sql, ',');
$sql2 = '';
if ($where) {
if (!is_array($where[0])) {
if (strtolower($where[1]) == 'in') {
$where[1] = 'IN';
$sql2 = " `{$where[0]}` {$where[1]} (";
if (is_array($where[2])) {
foreach ($where[2] as $v) {
$value[] = $v;
$sql2 .= '?,';
}
} else {
$value[] = $where[2];
$sql2 .= '?';
}
$sql2 = rtrim($sql2, ',') . ')';
} else {
$value[] = $where[2];
$sql2 = " `{$where[0]}` {$where[1]} ?";
}
} else {
foreach ($where as $v) {
if (strtolower($v[1]) == 'in') {
$v[1] = 'IN';
$sql2 .= " `{$v[0]}` {$v[1]} (";
if (is_array($v[2])) {
foreach ($v[2] as $v2) {
$value[] = $v2;
$sql2 .= "?,";
}
} else {
$value[] = $v[2];
$sql2 .= "?";
}
$sql2 = rtrim($sql2, ',') . ') AND';
} else {
$value[] = $v[2];
$sql2 .= " `{$v[0]}` {$v[1]} ? AND";
}
}
$sql2 = substr($sql2, 0, -4);
}
if ($sql2) {
$sql .= " WHERE " . $sql2;
}
}
}
$res = $this->link->prepare($sql);
$run = $res->execute($value);
if ($run) {
if ($act == 1) {
return $this->link->lastInsertId();
} else {
return $res->rowCount();
}
} else {
return 0;
}
}
/**
* 删除数据
* @param string $table 表名字
* @param array $where where条件
* @return bool
*/
public function delete($table, $where = [])
{
$sql = "DELETE FROM `{$table}`";
$sql2 = '';
$value = [];
if ($where) {
if (!is_array($where[0])) {
if (strtolower($where[1]) == 'in') {
$where[1] = 'IN';
$sql2 = " `{$where[0]}` {$where[1]} (";
if (is_array($where[2])) {
foreach ($where[2] as $v) {
$value[] = $v;
$sql2 .= '?,';
}
} else {
$value[] = $where[2];
$sql2 .= '?';
}
$sql2 = rtrim($sql2, ',') . ')';
} else {
$value[] = $where[2];
$sql2 = " `{$where[0]}` {$where[1]} ?";
}
} else {
foreach ($where as $v) {
if (strtolower($v[1]) == 'in') {
$v[1] = 'IN';
$sql2 .= " `{$v[0]}` {$v[1]} (";
if (is_array($v[2])) {
foreach ($v[2] as $v2) {
$value[] = $v2;
$sql2 .= "?,";
}
} else {
$value[] = $v[2];
$sql2 .= "?";
}
$sql2 = rtrim($sql2, ',') . ') AND';
} else {
$value[] = $v[2];
$sql2 .= " `{$v[0]}` {$v[1]} ? AND";
}
}
$sql2 = substr($sql2, 0, -4);
}
if ($sql2) {
$sql .= " WHERE " . $sql2;
}
}
$res = $this->link->prepare($sql);
return $res->execute($value);
}
/**
* count数据
* @param string $table 表名字
* @param array $where where条件
* @param string $field 字段
* @return int
*/
public function count($table, $where = [],$field = '*')
{
$sql = "SELECT COUNT({$field}) FROM `{$table}`";
$sql2 = '';
$value = [];
if ($where) {
if (!is_array($where[0])) {
if (strtolower($where[1]) == 'in') {
$where[1] = 'IN';
$sql2 = " `{$where[0]}` {$where[1]} (";
if (is_array($where[2])) {
foreach ($where[2] as $v) {
$value[] = $v;
$sql2 .= '?,';
}
} else {
$value[] = $where[2];
$sql2 .= '?';
}
$sql2 = rtrim($sql2, ',') . ')';
} else {
$value[] = $where[2];
$sql2 = " `{$where[0]}` {$where[1]} ?";
}
} else {
foreach ($where as $v) {
if (strtolower($v[1]) == 'in') {
$v[1] = 'IN';
$sql2 .= " `{$v[0]}` {$v[1]} (";
if (is_array($v[2])) {
foreach ($v[2] as $v2) {
$value[] = $v2;
$sql2 .= "?,";
}
} else {
$value[] = $v[2];
$sql2 .= "?";
}
$sql2 = rtrim($sql2, ',') . ') AND';
} else {
$value[] = $v[2];
$sql2 .= " `{$v[0]}` {$v[1]} ? AND";
}
}
$sql2 = substr($sql2, 0, -4);
}
if ($sql2) {
$sql .= " WHERE " . $sql2;
}
}
$res = $this->link->prepare($sql);
$res->execute($value);
$data = $res->fetch(PDO::FETCH_NUM);
return $data[0];
}
}
config.php
<?php
return array(
'host' => 'localhost',
'name' => 'root',
'password' => 'root',
'databaseName' => 'cs_cn',
'port' => '3306',
'charset' => 'utf8'
);
使用方法
<?php
require 'Mysql.class.php';
$mysql = new Mysql();
// 添加
$data = [
'code' => mt_rand(1000000000,9999999999),
'url' => 'https://www.zzwws.cn',
'ip' => mt_rand(1000000000,9999999999),
'add_time' => time()
];
$res = $mysql->exec('zz_url',$data);
if($res){
echo '添加成功';
}else{
echo '添加失败';
}
// 修改
$data = [
'code' => mt_rand(1000000000,9999999999)
];
$where = ['id','=',51];
// 或者
// $where = [
// ['id','=',51],
// ['ip','=','3755406202']
// ];
$res = $mysql->exec('zz_url',$data,2,$where);
if($res){
echo '修改成功';
}else{
echo '修改失败';
}
// 查询一行数据
$row = $mysql->getRow('zz_url',['id','=',51],'id,code');
if(!$row){
echo '获取失败';
}
print_r($row);
// 查询多行数据
$rows = $mysql->getAll('zz_url');
print_r($rows);
// 删除
$where = [
['id','=',52]
];
// 或者
// $where = [
// ['id','in',[1,2,3]]
// ];
$res = $mysql->delete('zz_url',$where);
var_dump($res);
// count
$count = $mysql->count('zz_url',['url','=','https://www.zzwws.cn']);
var_dump($count);
// query方法（没有预处理）
$res = $mysql->link->query("SELECT * FROM zz_url WHERE id = '1'");
$row = $res->fetch(PDO::FETCH_ASSOC);
print_r($row);