简单的php mysql类库,简单的PHP Mysql数据库操作类

本文介绍了一个基于PHP和PDO的简单MySQL数据库操作类,该类支持常见的数据库操作如增删改查,并提供了分页、联表查询等功能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一个简单的PHP Mysql数据库操作类

依赖pdo安装

composer 安装 composer require ninvfeng/mysql

引入/vendor目录下的autoload.php require 'vendor/autoload.php';

初始化

//配置

$config=['host'=>'127.0.0.1',

'port'=>3306,

'name'=>'test'

];

//推荐使用函数进行实例化,后续操作更加方便

function db($table='null') use $config{static $_db;

if(!$_db){

$_db=new \ninvfeng\mysql($config);

}

return $_db->table($table);

}

db('user')->insert(['user'=>'ninvfeng','pass'=>'password']);

db('user')->insert(['user'=>'lvlv','pass'=>'password']);

db('user')->where(['user'=>'ninvfeng'])->delete();

db('user')->where(['user'=>'lvlv'])->update(['pass'=>'password2']);

查找一条

db('user')->where(['user'=>'lvlv'])->find();

查找全部

db('user')->select();

条件查找

db('user')->where(['user'=>'ninvfeng'])->select();

分页查找

db('user')->page(1)->select();

字段查找

db('user')->field('user')->select();

排序

db('user')->order('id desc')->select();

join

db('user')->join('user_info on user_info.user_id=user.id')->select();

debug 仅打印sql不执行

db('user')->debug()->select();

执行原生sql

db('user')->query("select * from user");

事务

db('user')->trans();

返回原生对象

db()->pdo();<?php

namespace ninvfeng;

//基础数据库操作

class mysql

{

protected $_field='*';

protected $_where='';

protected $_order='';

protected $_limit='';

protected $_join='';

protected $_debug=false;

protected $_param=[];

protected $_sql=[];

function __construct($config){

//链接数据库

$this->_pdo=new \PDO('mysql:host='.$config['host'].';dbname='.$config['name'],$config['user'],$config['pass'],array(\PDO::ATTR_PERSISTENT => true));

//设置客户端字符集

$this->_pdo->exec("set names 'utf8'");

//禁用prepared statements的仿真效果 确保SQL语句和相应的值在传递到mysql服务器之前是不会被PHP解析

$this->_pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);

//数据表

$this->_table=$table;

}

//返回pdo对象

public function pdo(){

return $this->_pdo;

}

//操作表

public function table($table){

$this->_table=$table;

return $this;

}

//字段

public function field($field){

$this->_field=$field;

return $this;

}

//排序

public function order($order){

$this->_order='order by '.$order;

return $this;

}

//限制

public function limit($limit){

$this->_limit='limit '.$limit;

return $this;

}

//条件

public function where($where,$param=[]){

if($param){

$this->_param=array_merge($this->_param,$param);

}

if(is_array($where)){

$res='';

foreach($where as $k => $v){

$column_key='';

foreach (explode('.',$k) as $kk => $vv) {

$column_key.=' `'.$vv.'`.';

$column_plac='where_'.$vv;

}

if(is_array($v)){

$op = ' '.$v[0].' ';

$this->_param[$column_plac]=$v[1];

}else{

$op = ' = ';

$this->_param[$column_plac]=$v;

}

$column_key=trim($column_key,'.');

$res.=$column_key.$op.':'.$column_plac.' and';

}

$where=trim($res,'and');

}

$this->_where.=' '.$where.' and';

return $this;

}

//分页

public function page($page=1,$num=10){

$page=intval($page);

$num=intval($num);

$start=($page-1)*$num;

$this->_limit="limit $start,$num";

return $this;

}

//join

public function join($join){

//语句中不包含join时自动添加left join

if(stripos($join,'join')===false){

$join='left join '.$join;

}

$this->_join=$join;

return $this;

}

//调试

public function debug(){

$this->_debug=true;

return $this;

}

//结果集

public function select(){

$res=$this->_query();

if($res&&count($res[0])==1){

$column=explode('.',$this->_field);

$column=array_pop($column);

$result=array_column($res,$column);

return $result;

}else{

return $res;

}

}

//获取单条数据

public function find(){

$res=$this->_query()[0];

if($res&&count($res)==1){

$column=explode('.',$this->_field);

$column=array_pop($column);

return $res[$column];

}else{

return $res;

}

}

//更新

public function update($data){

if($this->_where){

$update='';

foreach($data as $k => $v){

$column_key='';

foreach (explode('.',$k) as $kk => $vv) {

$column_key.='`'.$vv.'`.';

$column_plac=$vv;

}

$this->_param[$column_plac]=$v;

$column_key=trim($column_key,'.');

$update.=$column_key."=:".$column_plac.",";

}

$update=trim($update,',');

$this->preWhere();

$this->_sql="update {$this->_table} set $update {$this->_where};";

return $this->exec($this->_sql,$this->_param);

}else{

echo '保存数据需指定条件';

die();

}

}

//添加

public function insert($data){

$update='';

foreach($data as $k => $v){

$column_key='';

foreach (explode('.',$k) as $kk => $vv) {

$column_key.='`'.$vv.'`.';

$column_plac=$vv;

}

$this->_param[$column_plac]=$v;

$column_key=trim($column_key,'.');

$update.=$column_key."=:".$column_plac.",";

}

$update=trim($update,',');

$this->_sql="insert into {$this->_table} set $update;";

$this->exec($this->_sql,$this->_param);

return $this->_pdo->lastInsertId();

}

//删除

public function delete(){

if($this->_where){

$this->preWhere();

$this->_sql="delete from {$this->_table} {$this->_where};";

return $this->exec($this->_sql,$this->_param);

}else{

echo '删除数据需指定条件';

die();

}

}

//执行原生query

public function query($sql,$param=[]){

$this->clearParam();

if($this->_debug){

echo "

";

echo $this->debugSql();

die();

}else{

$pre=$this->_pdo->prepare($sql);

if(!$pre){

$this->_error();

}

$pre->execute($param);

if($this->_error()){

return $pre->fetchAll(\PDO::FETCH_ASSOC);

}

}

}

//执行原生exec

public function exec($sql,$param=[]){

$this->clearParam();

if($this->_debug){

echo "

";

echo $this->debugSql();

die();

}else{

$pre=$this->_pdo->prepare($sql);

$res=$pre->execute($param);

if($this->_error()){

return $res;

}

}

}

//事务

public function trans($callback,$arr=[])

{

$this->_pdo->beginTransaction();

try {

$result = null;

if (is_callable($callback)) {

$result = call_user_func_array($callback, [$arr]);

}

$this->_pdo->commit();

return $result;

} catch (\Exception $e) {

$this->_pdo->rollback();

throw $e;

}

}

//清空参数

public function clearParam(){

$this->_field='*';

$this->_where='';

$this->_order='';

$this->_limit='';

$this->_join='';

$this->_debug=false;

$this->_param=[];

$this->_sql='';

}

//自增

public function setInc($field,$step=1){

if($this->_where){

$update=$field.'='.$field.'+'.$step;

$this->preWhere();

$this->_sql="update {$this->_table} set $update {$this->_where};";

return $this->exec($this->_sql,$this->_param);

}else{

echo '保存数据需指定条件';

die();

}

}

//自减

public function setDec($field,$step=1){

if($this->_where){

$update=$field.'='.$field.'-'.$step;

$this->preWhere();

$this->_sql="update {$this->_table} set $update {$this->_where};";

return $this->exec($this->_sql,$this->_param);

}else{

echo '保存数据需指定条件';

die();

}

}

//预处理where条件

protected function preWhere(){

if($this->_where){

$this->_where='where'.trim($this->_where,'and');

}

return $this;

}

//查询

protected function _query(){

$this->preWhere();

$this->_sql="select {$this->_field} from {$this->_table} {$this->_join} {$this->_where} {$this->_order} {$this->_limit}";

return $this->query($this->_sql,$this->_param);

}

//错误处理

protected function _error(){

if($this->_pdo->errorCode()==00000){

return true;

}else{

echo '

';

$error_msg=$this->_pdo->errorInfo()[2];

$e=new \Exception($error_msg);

echo '

'.$error_msg.'

';

echo '

'.$e->getTrace()[2]['file'].' In line '.$e->getTrace()[2]['line'].'

';

echo '

SQL 语句:'.$this->debugSql().'

';

die();

}

}

//生成调试sql

protected function debugSql(){

$res=$this->_sql;

foreach ($this->_param as $k => $v) {

$res=str_replace(':'.$k,'"'.$v.'"',$res);

}

return $res;

}

}

如果文章或资源对您有帮助,欢迎打赏作者。一路走来,感谢有您!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值