基于工厂的DB类
自己简单根据工厂模式封装了一个数据库类,
仅仅实例化了一个mysqli
链接数据库的类,首先定义一个接口类
<?php
interface InterDB{
//增加 arr为传入的数组
public function insert($arr);
//查询字段
public function select($where);
//条件查询
public function where($where,$con);
//添加and查询条件
public function andwhere($where,$con);
//添加or查询条件
public function orwhere($where,$con);
//分组查询
public function groupBy($params);
//having查询
public function having($params);
//增加筛选条件
public function addhaving($params);
//排序
public function orderBy($params);
//limit
public function limit($offset,$start=0);
//修改
public function update($arr,$where='',$con='AND');
//删除
public function delete($del,$con='AND');
//执行sql语句
public function sqlquery($sql);
//获取一条数据
public function One();
//获取所有数据
public function All();
//获取新增id
public function getlastInsertId();
//获取影响条数
public function getRow();
//获取结果集
public function get();
}
提供一个根据参数实例化DB
类的工厂类
class DB{
public $db;
public $tablename;
public function __construct(){
$this->db=self::create($GLOBALS['config']);
$this->db->table=$this->tablename;
}
public static function create($dsn){
$name=$dsn['type']."DB";
return $name::getInstance($dsn);
}
}
最后是具体的mysqli
类
<?php
include 'DB.interface.php';
/**
*
*/
class MysqlDB implements InterDB{
//表名称
public $table;
//当前数据库对象
private $db;
//sql语句查询条件
private $condition;
//查询出的对象结果集
private $obj;
//私有的静态属性
private static $_instance = null;
//私有的构造方法
private function __construct($dsn){
$this->connect($dsn);
}
//公有的静态方法
public static function getInstance($dsn){
if (self::$_instance === null) {
self::$_instance = new self($dsn);
}
return self::$_instance;
}
//私有的clone方法
private function __clone(){
trigger_error('Clone is not allow!',E_USER_ERROR);
}
//按照配置连接数据库
public function connect($dsn){
$localhost=$dsn['localhost'];
$username=$dsn['username'];
$password=$dsn['password'];
$dbname=$dsn['dbname'];
$mysqli = new mysqli("$localhost","$username","$password","$dbname");
if ($mysqli->connect_error) {
die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}
$charset=$dsn['charset'];
$mysqli->query("set names $charset");
$this->db=$mysqli;
return $this;
}
//执行查询sql语句
public function sqlquery($sql){
$res=$this->db->query($sql);
$arr=array();
while ($row = mysqli_fetch_assoc($res)){
$arr[]=$row;
}
return $arr;
}
//执行除查询外的语句
public function dataquery($sql){
$res=$this->db->query($sql);
return $res;
}
//执行查询所有数组
public function All(){
$result=$this->obj;
//var_dump($result);die;
$arr=array();
while ($row = mysqli_fetch_assoc($result)){
$arr[]=$row;
}
return $arr;
}
//获取一条数据
public function One(){
$result=$this->obj;
$row = mysqli_fetch_assoc($result);
return $row;
}
//获取新增id
public function getlastInsertId(){
$id=$this->db->insert_id;
return $id;
}
//获取影响行数
public function getRow(){
$num=$this->db->affected_rows;
return $num;
}
//执行条件语句,存储结果集
public function get(){
$sql=$this->condition;
//echo $sql;die;
$res=$this->db->query($sql);
$this->obj=$res;
return $this;
}
//添加数据
public function insert($arr){
$key=implode(',',array_keys($arr));
$v=array_values($arr);
$value='';
foreach ($v as $k)
{
$value=$value."'".$k."',";
}
$value=substr($value,0,(strlen($value)-1));
$sql="insert into `$this->table` ($key) values ($value)";
//echo $sql;
$mysqli=$this->db;
$mysqli->query($sql);
$this->db=$mysqli;
$id=$this->getlastInsertId();
if ($id) {
return $id;
}else{
return false;
}
}
/*
*删除数据
*/
public function delete($del,$con='AND'){
if (is_array($del)) {
$str='';
foreach ($del as $k => $val) {
$str.=" $con".' '.$k."="."'$val'";
}
$str=substr($str,4);
}else{
$str=$del;
}
$sql="delete from $this->table where $str";
$mysqli=$this->db;
$mysqli->query($sql);
$this->db=$mysqli;
$num=$this->getRow();
if ($num) {
return $num;
}else{
return false;
}
}
//修改数据
public function update($arr,$where='',$con='AND'){
if (is_array($arr)) {
$update='';
foreach ($arr as $k => $val) {
$update.=",".$k."="."'$val'";
}
$update=substr($update,1);
}else{
$update=$arr;
}
$str=$this->params($where,$con);
$sql="update $this->table set $update where $str";
echo $sql;
$mysqli=$this->db;
$mysqli->query($sql);
$this->db=$mysqli;
$num=$this->getRow();
if ($num !== 'false') {
return $num;
}else{
return false;
}
}
//查询字段
public function select($where='*'){
$sql="select $where from `$this->table`";
$this->condition=$sql;
return $this;
}
//查询条件,拼接查询语句
/*
*array('name'=>"zhanmg"); == name =zhang
*['in','id',1,10] == id in (1,10)
*['like','name','zhang'] == name like '%zhang%'
*['between','id',1,10] == id between 1 and 10
*['>','id',1] == id>1
*['<','id',10] == id<10
*/
public function params($where,$con){
if (!is_array($where)) {
$str=$where;
}else{
$arr=array();
$str='';
foreach ($where as $key => $value) {
if (is_int($key)) {
$arr[$key]=$value;
}else{
$str.=" $con".' '.$key."="."'$value'";
}
}
if (!empty($arr)) {
$type=$arr[0];
switch ($type) {
case 'in':
for ($i=2; $i <count($arr) ; $i++) {
$ids.=','.$arr[$i];
}
$ids=substr($ids,1);
$str= $arr[1].' '."in ($ids)";
break;
case 'like':
$str= $arr[1].' '."like '%".$arr[2]."%'";
break;
case '>':
$str= $arr[1].' '."> ".$arr[2];
break;
case '<':
$str= $arr[1].' '."< ".$arr[2];
break;
case 'between':
$str= $arr[1].' '."between ".$arr[2].' and '.$arr[3];
break;
}
}else{
if ($con=='AND') {
$str=substr($str,4);
}else{
$str=substr($str,3);
}
}
}
return $str;
}
//构建条件语句
public function where($where,$con='AND'){
$str=$this->params($where,$con);
$sql=$this->condition;
$sql.=" where $str";
$this->condition=$sql;
//echo $sql;die;
return $this;
}
//添加and查询条件
public function andwhere($where,$con='AND'){
$str=$this->params($where,$con);
$sql=$this->condition;
$sql.=" AND $str";
$this->condition=$sql;
//echo $sql;die;
return $this;
}
//添加or查询条件
public function orwhere($where,$con='AND'){
$str=$this->params($where,$con);
$sql=$this->condition;
$sql.=" OR $str";
$this->condition=$sql;
return $this;
}
//查询条数限制
public function limit($offset,$start=0){
$sql=$this->condition;
$sql.=" limit $start,$offset";
$this->condition=$sql;
return $this;
}
//分组查询
public function groupBy($params){
$sql=$this->condition;
$sql.=" GROUP BY $params";
$this->condition=$sql;
return $this;
}
//筛选
public function having($params,$con='AND'){
$str=$this->params($params,$con);
$sql=$this->condition;
$sql.=" having ($str)";
$this->condition=$sql;
return $this;
}
//增加筛选条件
public function addhaving($params,$con='AND'){
$str=$this->params($params,$con);
$sql=$this->condition;
$sql.=" AND ($str)";
$this->condition=$sql;
return $this;
}
//排序
public function orderBy($params){
$sql=$this->condition;
$sql.=" ORDER BY $params";
$this->condition=$sql;
return $this;
}
}