xml地图|网站地图|网站标签 [设为首页] [加入收藏]

智能家电

当前位置:美高梅游戏网站 > 智能家电 > php数据库类Mysqli篇

php数据库类Mysqli篇

来源:http://www.gd-chuangmei.com 作者:美高梅游戏网站 时间:2019-09-06 11:21
<?php/** * @author [Demon] <508037051@qq.com> * * 数据库操作类采用Mysqli进行处理 * 数据库操作分为: * 查询 支持 fields='id,name' 指定获取的字段 * 查询列表 listInfo() 支持 page size 分页 返回一个列表数组 * 查询详情 detailInfo() 返回一个数组详情 * 修改 支持 fields='id,name' 指定获取的字段 * 添加数据 updateInfo(); 参数没有id视为添加数据 * 修改数据 updateInfo(); 参数有id视为修改数据 * */// 操作实例/*// 查询D(ZDB::model->detailInfo([ 'id' => 3 ,'fields' => 'id,name' ]));D(ZDB::model->listInfo(['page'=>'1','size'=> '2', 'fields' => '*' ]));// 修改D(ZDB::model->updateInfo(['name'=>'12312', 'fields' => '*' ]));D(ZDB::model->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; 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; $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.','; $value_str .= "'" . $this->escape . "',"; } } } // //去掉最后一个逗号 $field_str= rtrim($field_str,','); $value_str= rtrim($value_str,','); //准备执行的SQL语句 $sql="INSERT INTO {$this->tableName} ({$field_str}) VALUES ({$value_str})"; $this->query; $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 ? 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; } else { $params['modifyTime'] = date('Y-m-d H:i:s',time; $detailInfo = $this->updateData; } return $detailInfo; } /** * [detailInfo description] * @param array $params [description] * @return [type] [description] */ public function detailInfo( $params = [] ) { $detailInfo = []; if (!empty { $params['size'] = 1; $listInfo = $this->listInfo; 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 == 2) { $arrKey[0] = trim( $arrKey[0] ); if ( array_key_exists( $arrKey[0], $attributes ) && isset { // '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 && is_array )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 . "'"; } } } return $arrCriteria; } /** * [getWhere description] * @param array $params [description] * @return [type] [description] */ public function where($params = []) { $arrCriteria = $this->arrCriteria; $arrCondition = $this->arrCondition; $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; $where = empty ? '': ' 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; return $listInfo; } public function count( $params = [] ) { $count = 0; $params['_count'] = 'count as count '; $params['size'] = '1'; $listInfo = $this->listInfo; 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; // D; // D($findList); // D; $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; if(!empty && is_array { $this->connect; $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; $argv['port'] = isset($argv['port']) ? $argv['port'] : 3306; if(is_null(self::$mysqli_connect)) { // D; } 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; } $this->defaultDB = $argv['database']; $this->selectDB($this->defaultDB); $this->host_info =  $argv; if $this->setCharset; } public function selectDB($database){ $int = mysqli_select_db($this->link, $database); if $this->defaultDB = $database; return $int; } public function query { // DD($this->link); // D; $result = mysqli_query($this->link, $sql); if(mysqli_errno($this->link)) { echo mysqli_error($this->link); exit; } $this->queries++; if(preg_match('/^uses+/', $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; } if(preg_match('/^insert$/i', $sql)) $this->insert_id = mysqli_insert_id($this->link); return $result; } private $sqlLog; public function find { $this->sqlLog[] = $sql; // D( $this->sqlLog ); $collection = array(); $result = $this->query; while($rows = mysqli_fetch_assoc array_push($collection, $rows); mysqli_free_result; return $collection; } public function getSqlLog() { return $this->sqlLog; } public function setCharset { return mysqli_set_charset($this->link, $charset); } /* public function prepare { $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; if(!empty && is_array { 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, $this->sql); } $this->bindValue = null; } $int = $this->query($this->sql); //$this->sql = null; return  $int; }*/ /** [escape description] */ public function escape { 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 { 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); }}

<?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; $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; // 查看贴子列表 $listInfo = array(); while($rows = mysqli_fetch_assoc { $listInfo[] = $rows; } $this->sqlCache[$sql] = $listInfo; } // D($this->sqlCache); return $listInfo; } // 获取详情 public function getDetail($params = []) { $getDetail = []; $list = $this->getList; if(!empty) { $getDetail = $list[0]; } return $getDetail; } // 修改数据 params 必须包含ID public function update($params = []) { $detail = []; $column = $this->getColumn; 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; $rows = mysqli_affected_rows($this->connect); // var_dump; if(!empty { $detail = $this->getDetail(['id' => $column['id'] ]); } } return $detail; } // 添加数据 public function addInfo($params = []) { $detail = []; $column = $this->getColumn; $sql = " INSERT INTO {$this->tableName} "; // INSERT INTO `demo`.`user` (`name`, `age`) VALUES ; $keys = ''; $values = ''; foreach ($column as $key => $value) { $keys .= "`{$key}`,"; $values .= "'{$value}',"; } $keys = trim($keys, ','); $values = trim($values, ','); $sql.= "  "; $sql.= ' VALUES '; $sql.= "  "; // 执行添加 $result = $this->mysqliQuery; // D; // D; // D; // D; // var_dump; 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; $rows = mysqli_affected_rows($this->connect); } return $rows; } // 查询数据 public function mysqliQuery( $sql = '' ) { D; $result = mysqli_query($this->connect, $sql); return $result; } // 组装有效的where public function getWhere( $params = [] ) { $strWhere = []; $column = $this->getColumn; foreach ($column as $key => $value) { $strWhere[] = " {$key} = '{$value}' "; } $strWhere = implode(' AND ', $strWhere); foreach ($params as $key => $value) { if(is_int { $strWhere .= $value; } } if(!empty($strWhere)) { $strWhere = " WHERE {$strWhere} "; } // D; // D; return $strWhere; } // 获取表的列字段 public function getTableColumn($tableName = '') { $listTableColumn = []; $sql = " desc {$tableName}"; $result = $this->mysqliQuery; // 查看贴子列表 $tableColumn = array(); while($rows = mysqli_fetch_assoc { $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;// 参数// $params = [];// 等值查询// $params['id'] = '5';// 特殊查询// $params[] = ' name like "%c%" ';// 无效字段// $params['xxxx'] = '213';// 获取列表// $userList = $userModel->getList;// D($userList);// 获取详情// $detail = $userModel->getDetail;// $detail = $userModel->getDetail;// $detail = $userModel->getDetail;// D;// D($userModel);// 添加数据// $addInfo = []; // 添加的参数// $addInfo['name'] = 'opp';// $addInfo['age'] = '2';// $addInfo['username'] = 'opp';// $addInfo['avatar'] = 'opp';// $addInfo = $userModel->addInfo;// D;// $update = [];// $update['id'] = 6;// $update['name'] = '12312';// $update = $userModel->update;// D;/*$delete = [];$delete['id'] = 10;$delete = $userModel->delete;var_dump;*/// 单例测试$userModel = new DBModel;$params = [];$params['page'] = 2;// $params['order'] = ' age desc ';$params['size'] = 5;$list = $userModel->getList;D;

php数据库分页,php分页

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<?php
//引入类(分页类10-24博客)
require_once "YIN.class.php";
require_once "../t/page.class.php";
$db = new YIN();
//取数据总条数
$zts = "select count(*) from wg";
$sql = $db->StrQuery($zts);
//造对象
$page = new Page($sql,15);
//在obj后边加上分页,拼接
$obj = "select * from wg ".$page->limit;
$data = $db->Query($obj);
?>
<table>
    <tr>
        <td>员工编号</td><td>工资</td><td>时间</td>
    </tr>
    <?php
    foreach ($data as $i){
        echo "<tr>
        <td>{$i[0]}</td><td>{$i[1]}</td><td>{$i[2]}</td>
    </tr>";
    }
    ?>
</table>
<div>
    <?php
//输出显示列表分页
    echo $page->fpage(3,4,5,6);
    ?>
</div>
</body>
</html>

 

!DOCTYPE htmlhtml lang="en"head meta charset="UTF-8" titleTitle/title/headbody? php //引入类(分页类10-24博客) require_once "YIN.class.php...

本文由美高梅游戏网站发布于智能家电,转载请注明出处:php数据库类Mysqli篇

关键词:

上一篇:PHP是世界上最好的语言?

下一篇:没有了