数据库查询类（1）
<?php
/**
* @author [Demon] <508037051@qq.com>
*
* 数据库操作类采用Mysqli进行处理
* 数据库操作分为：
* 查询 支持 fields='id,name' 指定获取的字段
* 查询列表 listInfo() 支持 page size 分页 返回一个列表数组
* 查询详情 detailInfo() 返回一个数组详情
* 修改 支持 fields='id,name' 指定获取的字段
* 添加数据 updateInfo(); 参数没有id视为添加数据
* 修改数据 updateInfo(); 参数有id视为修改数据
*
*/
// 操作实例
/*
// 查询
D(ZDB::model('test')->detailInfo([ 'id' => 3 ,'fields' => 'id,name' ]));
D(ZDB::model('test')->listInfo(['page'=>'1','size'=> '2', 'fields' => '*' ]));
// 修改
D(ZDB::model('test')->updateInfo(['name'=>'12312', 'fields' => '*' ]));
D(ZDB::model('test')->updateInfo(['name'=> time() ,'id'=>'1', 'fields' => 'id,name' ]));
die(' 数据库操作类实例结束 ');
*/
/**
* 数据库操作封装类
*/
class ZDB extends DB
{
/** @var [type] [单例] */
private static $instance;
/** @var [type] [description] */
public $tableName;
/** @var [type] [description] */
private $fields = [];
/** @var [type] [description] */
// private $where;
/**
* [model 加载模型对象]
* @param string $tableName [description]
* @return [type] [description]
*/
public static function model($tableName = '')
{
return static::getModel($tableName);
}
/**
* [getModel 获取单例对象]
* @param string $tableName [description]
* @return [type] [description]
*/
public static function getModel( $tableName = '')
{
if(is_null(self::$instance))
{
}
$argv = array
(
'host' => 'xxxxx',
'user' => 'xxx',
'password' => 'xxx',
'port' => 3306,
'database' => 'xxx',
'charset' => 'utf8'
);
self::$instance = new static($argv);
self::$instance->tableName = $tableName;
self::$instance->getMeta();
// D( self::$instance->tableName );
return self::$instance;
}
/**
* [updateData description]
* @param array $params [description]
* @return [type] [description]
*/
public function updateData( $params = [] )
{
$setStr = '';
foreach ($params as $key => $value)
{
foreach ($this->fields as $field)
{
if ( $field['field'] == $key)
{
$setStr .= "$key='$value', ";
}
}
}
$setStr = rtrim($setStr,', ');
$sql="update {$this->tableName} set {$setStr} WHERE id = {$params['id']}";
$this->query($sql);
$detailInfo = [];
if ( $this->affected_rows > 0 )
{
$detailParams = [ 'id' => $params['id'] ];
empty($params['fields']) OR $detailParams['fields'] = $params['fields'];
$detailInfo = $this->detailInfo($detailParams);
}
return $detailInfo;
}
/**
* [addData description]
* @param array $params [description]
*/
public function addData( $params = [] )
{
//拼接SQL语句
$field_str='';
$value_str='';
foreach ($params as $key => $value)
{
foreach ($this->fields as $field)
{
if ( $field['field'] == $key && $key != 'id' )
{
$field_str .= $this->escape($key).',';
$value_str .= "'" . $this->escape($value) . "',";
}
}
}
//
//去掉最后一个逗号
$field_str= rtrim($field_str,',');
$value_str= rtrim($value_str,',');
//准备执行的SQL语句
$sql="INSERT INTO {$this->tableName} ({$field_str}) VALUES ({$value_str})";
$this->query($sql);
$detailInfo = [];
if ( $this->insert_id > 0 )
{
$detailParams = ['id' => $this->insert_id];
empty($params['fields']) OR $detailParams['fields'] = $params['fields'];
$detailInfo = $this->detailInfo($detailParams);
}
return $detailInfo;
}
/**
* [getFields description]
* @param [type] $fields [description]
* @return [type] [description]
*/
public function getFields( $fields )
{
// $arrField = '*';
$arrField = [];
$arrFields = is_string($fields) ? explode(',',$fields): $fields;
foreach ($arrFields as $fieldName)
{
foreach ($this->fields as $field)
{
if ( $field['field'] == $fieldName )
{
$arrField[] = $fieldName;
}
}
}
return empty($arrField) ? '*' : rtrim(implode(',', $arrField),',') ;
}
/**
* [updateInfo description]
* @param array $params [description]
* @return [type] [description]
*/
public function updateInfo( $params = [] )
{
$detailInfo = [];
if (empty($params['id']))
{
$detailInfo = $this->addData($params);
}
else
{
$params['modifyTime'] = date('Y-m-d H:i:s',time());
$detailInfo = $this->updateData($params);
}
return $detailInfo;
}
/**
* [detailInfo description]
* @param array $params [description]
* @return [type] [description]
*/
public function detailInfo( $params = [] )
{
$detailInfo = [];
if (!empty($params))
{
$params['size'] = 1;
$listInfo = $this->listInfo($params);
empty($listInfo[0]) OR $detailInfo = $listInfo[0];
}
return $detailInfo;
}
/**
* [attributes description]
* @return [type] [description]
*/
public function attributes()
{
$fields = [];
foreach ($this->fields as $key => $value)
{
$fields[$value['field']] = $value['content'];
}
return $fields;
}
/**
* [arrCondition 特殊条件]
* @param array $params [description]
* @return [type] [description]
*/
public function arrCondition($params = [])
{
$attributes = $this->attributes();
$arrCondition = [];
foreach ($params as $key => $value)
{
$arrKey = [];
// startPosition search=古交
$space = ' ';
if ( strpos($key,$space) !== FALSE ) //如果存在空格
{
$arrKey = explode($space, $key);
}
// startPosition_search=古交
else if ( stristr($key,'_search') !== FALSE ) //如果存在空格
{
$arrKey = $arrKey = explode('_', $key);
}
if (count($arrKey) == 2)
{
$arrKey[0] = trim( $arrKey[0] );
if ( array_key_exists( $arrKey[0], $attributes ) && isset($value) )
{
// 'targetType <=' => 2
if( in_array($arrKey[1], ['<','>','>=','<=','=']) )
{
$arrCondition[] = " {$arrKey[0]} {$arrKey[1]} '{$value}' ";
}
// else if( $arrKey[1] === 'in' ) //$params[CRITERIA_KEY::WHERE]['id in'] = $targetID;
// {
// $arrCondition[] = " {$arrKey[0]} {$arrKey[1]} {$value} ";
// }
// else if( $arrKey[1] === 'notin' )
// {
// $arrCondition[] = " {$arrKey[0]} {$arrKey[1]} {$value} ";
// }
else if( $arrKey[1] === 'search' )
{
// sprintf('%s', trim($arrKey[1]);
$arrCondition[] = " {$arrKey[0]} like '%{$value}%' ";
}
}
}
}
return $arrCondition;
}
/**
* [validParams 合法的参数]
* @param array $params [description]
* @return [type] [description]
*/
public function validParams($params = [])
{
$checkParams = [];
if(!empty($params) && is_array($params) )foreach ($params as $key => $value)
{
foreach ($this->fields as $field)
{
if ( $field['field'] == $key )
{
$checkParams[$key] = $value;
}
}
}
return $checkParams;
}
/**
* [arrCriteria 标准的=条件]
* @param array $params [description]
* @return [type] [description]
*/
public function arrCriteria($params = [])
{
!isset($params['status']) && $params['status'] = '1'; // 默认
$arrCriteria = [];
foreach ($params as $key => $value)
{
foreach ($this->fields as $field)
{
if ( $field['field'] == $key )
{
$arrCriteria[] = "{$key} = '" . $this->escape($value) . "'";
}
}
}
return $arrCriteria;
}
/**
* [getWhere description]
* @param array $params [description]
* @return [type] [description]
*/
public function where($params = [])
{
$arrCriteria = $this->arrCriteria($params);
$arrCondition = $this->arrCondition($params);
$arrWhere = array_merge($arrCriteria,$arrCondition);
$condition='';
if(!empty($arrWhere))
{
$condition = ' ' . implode(' and ',$arrWhere);
}
return $condition;
}
/**
* [listInfo description]
* @param array $params [description]
* @return [type] [description]
*/
public function listInfo( $params = [] )
{
$where = $this->where($params);
$where = empty($where) ? '': ' WHERE ' . $where;
$fields = empty($params['fields']) ? '*' : $this->getFields( $params['fields'] );
$page = empty($params['page']) ? 1 : $params['page'] ;
$size = empty($params['size']) ? 10 : $params['size'] ;
$order = empty($params['order']) ? ' id DESC ' : $params['order'] ;
// $size = empty($params['size']) ? 3 : $params['size'] ;
$offset = intval( $page-1 ) * $size ;
$limit = " ORDER BY {$order} LIMIT {$offset}, {$size} ";
if(!empty($params['_count']))
{
$fields = $params['_count'];
$limit = '';
}
$sql = "SELECT {$fields} FROM {$this->defaultDB}.{$this->tableName} {$where} {$limit} ";
$listInfo = $this->find( $sql );
return $listInfo;
}
public function count( $params = [] )
{
$count = 0;
$params['_count'] = 'count(id) as count ';
$params['size'] = '1';
$listInfo = $this->listInfo($params);
empty($listInfo[0]['count']) OR $count = $listInfo[0]['count'];
return $count;
}
//获取字段信息
private function getMeta()
{
$sql = "SELECT column_name as field, column_comment as content FROM Information_schema.columns WHERE table_schema = '{$this->host_info->database}'AND table_Name = '{$this->tableName}'; ";
$findList = $this->find($sql);
// D($sql);
// D($findList);
// D($this);
$this->fields = $findList;
// D( $this->fields );
}
}
class DB {
protected $defaultDB = null;
protected $link = null;
protected $sql = null;
protected $host_info = null;
protected $bindValue = null;
public $num_rows = 0;
public $affected_rows = 0;
public $insert_id = 0;
public $queries = 0;
protected function __construct() {
if(func_num_args()) {
$argv = func_get_arg(0);
if(!empty($argv) && is_array($argv)) {
$this->connect($argv);
$argv['charset'] = isset($argv['charset']) ? $argv['charset'] : 'utf8';
$this->setCharset($argv['charset']);
}
}
}
public static $mysqli_connect = null;
public function connect($argv, $charset = null) {
if($this->link) return false;
$argv = func_get_arg(0);
$argv['port'] = isset($argv['port']) ? $argv['port'] : 3306;
if(is_null(self::$mysqli_connect))
{
// D(12);
}
self::$mysqli_connect = mysqli_connect( $argv['host'], $argv['user'], $argv['password'], $argv['database'], $argv['port']);
$this->link = self::$mysqli_connect;
if(mysqli_connect_errno()) {
echo mysqli_connect_error();
exit(0);
}
$this->defaultDB = $argv['database'];
$this->selectDB($this->defaultDB);
$this->host_info = (object) $argv;
if($charset) $this->setCharset($charset);
}
public function selectDB($database){
$int = mysqli_select_db($this->link, $database);
if($int) $this->defaultDB = $database;
return $int;
}
public function query($sql) {
// DD($this->link);
// D($sql);
$result = mysqli_query($this->link, $sql);
if(mysqli_errno($this->link)) {
echo mysqli_error($this->link);
exit(0);
}
$this->queries++;
if(preg_match('/^use\s+(\w+)/', $sql, $matches))
list($range, $this->defaultDB) = $matches;
if(!preg_match('/^select(.+)$/i', $sql)) {
$this->affected_rows = mysqli_affected_rows($this->link);
}else{
$this->num_rows = mysqli_num_rows($result);
}
if(preg_match('/^insert(.+)$/i', $sql))
$this->insert_id = mysqli_insert_id($this->link);
return $result;
}
private $sqlLog;
public function find($sql) {
$this->sqlLog[] = $sql;
// D( $this->sqlLog );
$collection = array();
$result = $this->query($sql);
while($rows = mysqli_fetch_assoc($result))
array_push($collection, $rows);
mysqli_free_result($result);
return $collection;
}
public function getSqlLog() {
return $this->sqlLog;
}
public function setCharset($charset) {
return mysqli_set_charset($this->link, $charset);
}
/*
public function prepare($sql) {
$this->sql = $sql;
}
public function bindValue($search, $value) {
$this->bindValue = array();
$this->bindValue[$search] = $value;
}
public function execute() {
if(func_num_args()) {
$argv = func_get_arg(0);
if(!empty($argv) && is_array($argv)) {
if(!is_array($this->bindValue)) $this->bindValue = array();
$this->bindValue = array_merge($this->bindValue, $argv);
}
}
if($this->bindValue) {
foreach($this->bindValue as $search => $value) {
$this->sql = str_replace($search, $this->escape($value), $this->sql);
}
$this->bindValue = null;
}
$int = $this->query($this->sql);
//$this->sql = null;
return (boolean) $int;
}*/
/** [escape description] */
public function escape($string)
{
return mysqli_real_escape_string($this->link, $string);
}
/** [close description] */
public function close() {
return mysqli_close($this->link);
}
/** [ping description] */
public function ping() {
return mysqli_ping($this->link);
}
/** [beginTransaction description] */
public function beginTransaction($boolean) {
return mysqli_autocommit($this->link, $boolean);
}
public function commit() {
return mysqli_commit($this->link);
}
public function rollback() {
return mysqli_rollback($this->link);
}
public function __destruct() {
if($this->link) $this->close();
unset($this->link, $this->defaultDB, $this->bindValue, $this->sql, $this->result, $this->num_rows, $this->affected_rows, $this->insert_id, $this->host_info);
}
}
数据库查询类（2）
<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', 'demo');
define('DB_PORT', '3306');
define('CHARSET', 'utf-8');
// 打印变量 调试
function D() {echo '<pre>'; print_r( func_get_args() ); echo '</pre>'; echo "<hr />"; }
// 单例设计模式，只连接一次数据库
class DBConnect
{
private static $_instance = null;
//私有构造函数，防止外界实例化对象
// private function __construct() {}
//私有克隆函数，防止外办克隆对象
// private function __clone() {}
//静态方法，单例统一访问入口
public static function getInstance()
{
if(is_null(self::$_instance))
{
// D('tableName');
self::$_instance = mysqli_connect( DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE, DB_PORT );
}
return self::$_instance;
}
}
class DBModel
{
// 成员属性
private $sqlCache;
private $tableName;
private $connect;
private $tableColumn;
// 初始化对象
public function __construct( $tableName = '')
{
// 连接数据库
$this->tableName = $tableName;
$this->connect = DBConnect::getInstance();
mysqli_set_charset($this->connect, CHARSET);
$this->tableColumn = $this->getTableColumn($this->tableName);
$this->sqlCache = [];
}
// 获取列表
public function getList($params = [])
{
$listInfo = [];
// 默认查询列表
$sql = " SELECT * FROM {$this->tableName} ";
$sql .= $this->getWhere($params);
$order = empty($params['order']) ? ' id desc ' : $params['order'];
$sql .= " ORDER BY {$order} ";
// 分页参数
$page = empty($params['page']) ? 1 : $params['page'];
$size = empty($params['size']) ? 10 : $params['size'];
// 当前页码 - 1 乘以 显示的条数 例：limit 0,5
$page = ($page - 1) * $size;
$sql .= " limit {$page},$size ";
if(isset($this->sqlCache[$sql]))
{
$listInfo = $this->sqlCache[$sql];
}
else
{
$result = $this->mysqliQuery($sql);
// 查看贴子列表
$listInfo = array();
while($rows = mysqli_fetch_assoc($result))
{
$listInfo[] = $rows;
}
$this->sqlCache[$sql] = $listInfo;
}
// D($this->sqlCache);
return $listInfo;
}
// 获取详情
public function getDetail($params = [])
{
$getDetail = [];
$list = $this->getList($params);
if(!empty($list[0]))
{
$getDetail = $list[0];
}
return $getDetail;
}
// 修改数据 params 必须包含ID
public function update($params = [])
{
$detail = [];
$column = $this->getColumn($params);
if(!empty($column['id']))
{
// UPDATE `demo`.`user` SET `name`='33', `age`='22' WHERE `id`='6';
$sql = " UPDATE {$this->tableName} SET ";
$field = '';
foreach ($column as $key => $value)
{
$field .= " `{$key}` = '{$value}',";
}
$field = trim($field, ',');
$sql .= $field;
$sql .= " WHERE id = {$column['id']} ";
$result = $this->mysqliQuery($sql);
$rows = mysqli_affected_rows($this->connect);
// var_dump($rows);
if(!empty($rows))
{
$detail = $this->getDetail(['id' => $column['id'] ]);
}
}
return $detail;
}
// 添加数据
public function addInfo($params = [])
{
$detail = [];
$column = $this->getColumn($params);
$sql = " INSERT INTO {$this->tableName} ";
// INSERT INTO `demo`.`user` (`name`, `age`) VALUES ('1', '2');
$keys = '';
$values = '';
foreach ($column as $key => $value)
{
$keys .= "`{$key}`,";
$values .= "'{$value}',";
}
$keys = trim($keys, ',');
$values = trim($values, ',');
$sql.= " ($keys) ";
$sql.= ' VALUES ';
$sql.= " ($values) ";
// 执行添加
$result = $this->mysqliQuery($sql);
// D($column);
// D($keys);
// D($values);
// D($sql);
// var_dump($result);
if( $result=== true )
{
$insertID = mysqli_insert_id($this->connect);
$detail = $this->getDetail(['id' => $insertID]);
}
return $detail;
}
// 删除数据 params 必须有ID
public function delete($params = [])
{
$rows = 0;
if(!empty($params['id']))
{
$sql = " DELETE FROM {$this->tableName} WHERE `id`='{$params['id']}' ";
$result = $this->mysqliQuery($sql);
$rows = mysqli_affected_rows($this->connect);
}
return $rows;
}
// 查询数据
public function mysqliQuery( $sql = '' )
{
D($sql);
$result = mysqli_query($this->connect, $sql);
return $result;
}
// 组装有效的where
public function getWhere( $params = [] )
{
$strWhere = [];
$column = $this->getColumn($params);
foreach ($column as $key => $value)
{
$strWhere[] = " {$key} = '{$value}' ";
}
$strWhere = implode(' AND ', $strWhere);
foreach ($params as $key => $value)
{
if(is_int($key))
{
$strWhere .= $value;
}
}
if(!empty($strWhere))
{
$strWhere = " WHERE {$strWhere} ";
}
// D($params);
// D($column);
return $strWhere;
}
// 获取表的列字段
public function getTableColumn($tableName = '')
{
$listTableColumn = [];
$sql = " desc {$tableName}";
$result = $this->mysqliQuery($sql);
// 查看贴子列表
$tableColumn = array();
while($rows = mysqli_fetch_assoc($result))
{
$tableColumn[$rows['Field']] = $rows;
}
return $tableColumn;
}
// 获取参数的有效字段
public function getColumn( $params = [] )
{
$column = [];
foreach ($params as $key => $value)
{
if(isset($this->tableColumn[$key]))
{
$column[$key] = $value;
}
}
return $column;
}
}
// 实例化对象
// $userModel = new DBModel('user');
// 参数
// $params = [];
// 等值查询
// $params['id'] = '5';
// 特殊查询
// $params[] = ' name like "%c%" ';
// 无效字段
// $params['xxxx'] = '213';
// 获取列表
// $userList = $userModel->getList($params);
// D($userList);
// 获取详情
// $detail = $userModel->getDetail($params);
// $detail = $userModel->getDetail($params);
// $detail = $userModel->getDetail($params);
// D($detail);
// D($userModel);
// 添加数据
// $addInfo = []; // 添加的参数
// $addInfo['name'] = 'opp';
// $addInfo['age'] = '2';
// $addInfo['username'] = 'opp';
// $addInfo['avatar'] = 'opp';
// $addInfo = $userModel->addInfo($addInfo);
// D($addInfo);
// $update = [];
// $update['id'] = 6;
// $update['name'] = '12312';
// $update = $userModel->update($update);
// D($update);
/*$delete = [];
$delete['id'] = 10;
$delete = $userModel->delete($delete);
var_dump($delete);*/
// 单例测试
$userModel = new DBModel('user');
$params = [];
$params['page'] = 2;
// $params['order'] = ' age desc ';
$params['size'] = 5;
$list = $userModel->getList($params);
D($list);
__以上两例代码是业余时间写的，，有些许不完美的情况有待各位读者尽力完善
适合初学者和想熟悉其它框架数据库的同学。
以上代码修改下数据库配置信息，可以很方便的使用去一些小功能哦
感觉不错的，记得给个喜欢哦 ：）🙃 __
网友评论