MySQL 封装DB类
闲来无事,封装了一个轻量的,不用手动写SQL的类,不算太强大,满足简单的SQL语句
其他的不说,上代码:
//MySQL配置文件:
<?php
// 第一个为默认链接数据库配置
function choose_config($db_name = 'default'){
switch ($db_name){
case 'default':
return ([
'host'=>'localhost',
'user'=>'root',
'pass'=>'root',
'database'=>'test',
'ut'=>'utf8'
]);
break;
case 'admin':
return ([
'host'=>'地址',
'user'=>'名称',
'pass'=>'密码',
'database'=>'库名',
'ut'=>'utf8'
]);
break;
}
}
//DB类
<?php
include_once "mysql.php"; // 引入配置文件
date_default_timezone_set('PRC');
class db
{
private $host = '' ;
private $user = '' ;
private $pass = '' ;
private $database = '' ;
private $ut = '' ;
private $conn = null;
private $sql = '';
private $result = ''; /* 执行SQL语句,PDO返回结果集 */
private $error = '';
/* 构造函数,选择配置里面的数据库 */
public function __construct($db_name)
{
$configu = choose_config($db_name); // 选择数据库配置
$this->host = $configu['host'];
$this->user = $configu['user'];
$this->pass = $configu['pass'];
$this->database = $configu['database'];
$this->ut = $configu['ut'];
}
/* 析构函数,关闭连接数据库 */
public function __destruct()
{
$this->conn = null;
}
/* 连接数据库 */
public function connect() {
try {
$this->conn = @new PDO("mysql:host=$this->host;dbname=$this->database", "$this->user", "$this->pass");
$this->conn -> query("SET NAMES $this->ut");
} catch (PDOException $e) {
$this -> error_log($e -> getMessage());
}
}
/* 用原生态SQL语句操作数据库 */
public function sql_query($sql) {
$this->sql = $sql;
/* 打开连接 */
if(!$this -> conn){
/* 未打开连接,打开链接 */
$this->connect();
}
$result = $this -> conn -> query($this->sql)or die($this->error_log2($this->conn->errorInfo(),$this->sql));
$this->result = $result;
}
/* 操作数据库 */
public function query() {
/* 打开连接 */
if(!$this -> conn){
/* 未打开连接,打开链接 */
$this->connect();
}
/* update安全判断,当SQL语句为跟新语句时,未带条件不执行语句 */
if(strpos($this->sql,'update') !== false){
if(strpos($this->sql,'where') !== false){
$result = $this -> conn -> query($this->sql)or die($this->error_log2($this->conn->errorInfo(),$this->sql));
$this->result = $result;
}else{
$this->error = '警告:更新语句,未加where条件!';
}
}else{
$result = $this -> conn -> query($this->sql)or die($this->error_log2($this->conn->errorInfo(),$this->sql));
$this->result = $result;
}
}
/**
* 查询一条数据语句 用的 | limit 1
* @param string $table_name 表的名称
* @param array $field_arr 字段数组 | ['id','name']
*/
public function sel_row($table_name,$field_arr){
$sql = 'select ';
$sql1 = '';
foreach ($field_arr as $value){
$sql1 .= $value.',';
}
$sql1 = rtrim($sql1, ','); // 去除最后一个逗号
$sql = $sql.$sql1.' from `'.$table_name.'` limit 1';
$this->sql = $sql;
}
/**
* 查询语句
* @param string $table_name 表的名称
* @param string/array $field_arr 字段数组 | ['id','name']
*/
public function sel($table_name,$field_arr){
$sql = 'select ';
if(is_array($field_arr)) {
$sql1 = '';
foreach ($field_arr as $value){
$sql1 .= $value.',';
}
$sql1 = rtrim($sql1, ','); // 去除最后一个逗号
$sql = $sql.$sql1.' from `'.$table_name.'`';
}else{
$sql .= $field_arr.' from `'.$table_name.'`';
}
$this->sql = $sql;
}
/**
* 添加where语句
* @param string/array $para | string:'a=b and c<d' | array: [['a','=','b'],['c','<','e']]
*/
public function add_where($para){
$sql = $this->sql;
if(is_array($para)) {
$sql1 = '';
foreach ($para as $value){
$sql1 .= $value[0] . $value[1] . $value[2].' and ';
}
$sql1 = rtrim($sql1, ' and '); // 去除最后一个'and'
$sql .=' where '.$sql1;
}else{
$sql .= ' where '.$para;
}
$this->sql = $sql;
}
/**
* 添加分页查询数据语句
* @param string $by_field 按照哪个字段分页
* @param int $page 页数
* @param int $num 一页多少条数据
* @param bool $DESC 是否开启降序排序,默认不开启,默认为升序
*/
public function add_page($by_field,$page,$num,$DESC = false){
$num1 = $num*($page-1);
$sql = $this->sql;
if($DESC){
$sql .= ' ORDER BY '.$by_field.' DESC limit '.$num1.','.$num;
}else{
$sql .= ' ORDER BY '.$by_field.' limit '.$num1.','.$num;
}
$this->sql = $sql;
}
/**
* 插入一条数据语句
* @param string $table_name 表名称
* @param array $field_arr 字段的名称数组 | ['id','name']
* @param array $value_arr 插入值的数组 | ['1','xcc']
*/
public function insert_row($table_name,$field_arr,$value_arr){
$sql = 'insert into `'.$table_name.'` (';
$key = '';
foreach ($field_arr as $value){
$key .= '`'.$value.'`,';
}
$key = rtrim($key, ','); // 去除最后一个逗号
$val = '';
foreach ($value_arr as $value){
$val .= '\''.$value.'\',';
}
$val = rtrim($val, ','); // 去除最后一个逗号
$sql .= $key.') values ('.$val.')';
$this->sql = $sql;
}
/**
* 更新一条语句
* @param string $table_name 表名称
* @param string/array $field_a 字段数组或字符串 | ['id','name'] | 'id'
* @param string/array $value_a 值数组或字符串 | ['1','xcc'] | '1'
*/
public function update_row($table_name,$field_a,$value_a){
$sql = 'update `'.$table_name.'` set ';
if(is_array($field_a)){
$sql1 = '';
for ($i=0;$i<count($field_a,0);$i++){
$sql1 .= $field_a[$i].'=\''.$value_a[$i].'\',';
}
$sql1 = rtrim($sql1, ','); // 去除最后一个逗号
$sql .= $sql1;
}else{
$sql .= $field_a.'=\''.$value_a.'\'';
}
$this->sql = $sql;
}
/* 可返回多条数据 */
public function ret_data() {
$data = array(); $i = 0;
while ($row = $this->result -> fetch()) {
$data[$i] = $row;
$i = $i + 1;
}
return $data;
}
/* 返回一条值 */
public function ret_row(){
return $this->result->rowcount();
}
/* 开始事务 */
public function beginTransaction(){
/* 打开连接 */
if(!$this -> conn){
/* 未打开连接,打开链接 */
$this->connect();
}
$this->conn->beginTransaction();
}
/* 提交事务 */
public function commit(){
$this->conn->commit();
}
/* 回滚事务 */
public function rollBack(){
$this->conn->rollBack();
}
/* 写入数据库连接错误日志 */
public function error_log($error) {
$this->error = $error;
file_put_contents('/var/www/html/log/'.date("Y-m-d").'.txt',date("Y-m-d H:i:s").' => '.'连接:'.$error."\r\n",FILE_APPEND);
}
/* 写入sql语法错误日志 */
public function error_log2($error,$sql) {
$error=$error[2];
$this->error = $error;
file_put_contents('/var/www/html/log/'.date("Y-m-d").'.txt',date("Y-m-d H:i:s").' => '.'语法:'.$error."\r\n".'sql语句:'.$sql."\r\n",FILE_APPEND);
}
/* 得到SQL语句 */
public function get_sql(){return $this->sql;}
/* 得到错误信息 */
public function get_err(){return $this->error;}
}
/* 使用例子 */
//$test = new db('xc'); // 实例化一个链接对象,选择配置名称为'xc'的配置
//$test->sel('order',['id','name']);
//$test->add_where([['a','=','b']]);
//$test->add_page('submit_time','1','20');
//$test->query();
//$res = $test->ret_data();
//var_dump($res);
//$test->insert_row('user_amount',['taobao_user_id','short_url'],['66666666','QQQQQQ']);
//$test->query();
//$res = $test->ret_row();
//var_dump($res);
//$test->update_row('user_amount','rmb','123');
//$test->add_where([['taobao_user_id','=','1701611475']]);
//$test->query();
//echo $test->get_sql();