php 仿thinkphp的sql类库
模仿thinkphp封装的类库
<?php /** * MySql操作类2015版 * 作者:咖啡兽兽 287962566@qq.com * 使用说明: * //包含文件 * inclode 'Mysql.class.php'; * //写个方法来实例化数据库对象 * function newMySQL($table){ * //DSN参数,建议从配置文件获取 * $dsn = array( * 'DB_HOST' => 'loaclhost', //主机地址 * 'DB_NAME' => 'mydb', //数据库名 * 'DB_PORT' => 3306, //端口 * 'DB_PREFIX' => 'kf', //表前缀 * 'DB_CHARSET' => 'utf8', //数据库编码 * 'DB_USER' => 'root', //用户名 * 'DB_PWD' => '123456', //密码 * ); * //表名 * $dsn['DB_TABLE'] = $table; * //返回数据库对象 * return Mysql::start($dsn); * } * //传入表名,获取数据库对象 * $Sql = newMySQL('user'); * //若要切换操作表,可直接给table成员属性赋值 * $Sql->table = 'product'; * //插入数据 * $data['name'] = '小白'; * $data['pwd'] = md5('123456'); * $data['age'] = 25; * $data['sex'] = '男'; * $Sql->add($data); * * //更新数据 * $data['name'] = '小黑'; * $where = "id=1"; * $Sql->where($where)->save($data); * //指定字段加1 * $Sql->where($where)->sum('age'); * //指定字段加N * $Sql->where($where)->sum('age',10); * * //查询数据 * $where = "sex='男' and age>20"; * //取回所有符合条件的数据 * $arr = $Sql->where($where)->select(); * //可用field指定要取回的字段 * $arr = $Sql->field('name,sex')->where($where)->select(); * //取回指定范围 limit 两个参数都是整数型 * $arr = $Sql->where($where)->limit(偏移量,最大行数)->select(); * //指定排序 desc or asc 可指定多个字段,以","隔开 * $arr = $Sql->where($where)->order("sex desc")->select(); * //取回第一行 * $find = $Sql->where($where)->find(); * //单独取回指定字段,若符合条件的数据 >1行,则返回数组 * $field = $Sql->where('id=1')->getField('name'); * //取符合条件的总行数 * $count = $Sql->where($where)->getCount(); * * //删除数据 * $Sql->where($where)->delete(); */ class Mysql { private $host; //主机地址 private $dbname; //数据库名 private $port; //数据库端口 private $username; //用户名 private $password; //密码 private $charset; //数据库编码 static $SQL; //数据库对象 static $obj = null; public $table; //操作表 private $pre = null; private $opt; //选项 /** * 初始化数据库 * @param Array $dsn 数据库连接参数 */ private function __construct($dsn){ $this->host = $dsn['DB_HOST']; $this->dbname = $dsn['DB_NAME']; $this->username = $dsn['DB_USER']; $this->password = $dsn['DB_PWD']; $this->port = $dsn['DB_PORT']; $this->charset = $dsn['DB_CHARSET']; //$this->table = $dsn['DB_PREFIX'].$dsn['DB_TABLE']; $this->opt['where'] = ''; $this->opt['order'] = ''; $this->opt['limit'] = ''; $this->opt['field'] = null; $this->connect(); } /* * Lee: * 还原默认纯洁的配置环境 * */ public function reset() { $this->opt['where'] = ''; $this->opt['order'] = ''; $this->opt['limit'] = ''; $this->opt['field'] = null; } /** * 连接数据库 * @return Bool */ private function connect(){ try{ $dsn = 'mysql:host='.$this->host.';dbname='.$this->dbname.';port='.$this->port; $options = array( PDO :: MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.$this->charset, ); self::$SQL = new PDO ($dsn,$this->username,$this->password,$options); //设置错误报告 self::$SQL->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); }catch(PDOException $e){ echo "数据库连接失败:".$e->getMessage(); exit; } return true; } /** * @param Array $dsn 数据库连接参数(DB_HOST[主机],DB_NAME[数据库名],DB_PORT[端口],DB_CHARSET[编码],DB_USER[帐户],DB_PWD[密码],DB_TABLE[表名]) * @return Object */ static function start($dsn){ //判断对象是否存在,若已存在则直接返回该对象 //if(is_null(self::$obj)){ self::$obj = null; self::$obj = new self($dsn); //} return self::$obj; } /** * 插入数据(成功返回新插入的 自动增长主键,失败返回0) * @param Array $data 欲插入的数据,对应表中的键与值 * @param Int 0 准备语句 ,1 执行语句,2(默认)准备并执行语句 * @return Int */ public function add($data,$type = 2){ try{ //构造SQL语句 $count = 0; $a = ''; foreach($data as $k=>$v){ if($count != 0){ $a = ','; } @$key .= $a.$k; @$bind .=$a.":$k"; $count++; } $s = "insert into {$this->table}({$key}) values({$bind})"; //发送语句 if($type != 1){ $this->pre = self::$SQL->prepare($s); if($type == 0 && is_object($this->pre) == true){ return 1; } } //执行语句 if($type != 0){ $this->pre->execute($data); } //重置各种条件 $this->reset(); //返回最新主键 return self::$SQL->lastinsertid(); }catch(PDOException $e){ exit("错误:".$e->getMessage()); } } /** * 更新数据(返回受影响行) * @param void $data 传入更新数组 * @return Int */ public function save($data){ try{ //构造SQL语句 $count = 0; $a = ''; foreach($data as $k=>$v) { if($count != 0){ $a = ','; } @$bind .=$a."$k='$v'"; $count++; } $s = "update {$this->table} set {$bind} {$this->opt['where']}"; //发送语句 $this->pre = self::$SQL->prepare($s); //执行语句 $this->pre->execute($data); //重置各种条件 $this->reset(); //返回受影响行 return $this->pre->rowCount(); }catch(PDOException $e){ exit("错误:".$e->getMessage()); } } /** * 指定字段加N,默认加1 * @param String $key 字段 * @param Int $num [可选]裕增加的数值,默认1 * @return Int */ public function sum($key,$num){ try{ $s = "update {$this->table} set {$key}={$key}+{$num} {$this->opt['where']}"; //发送语句 $this->pre = self::$SQL->prepare($s); //执行语句 $this->pre->execute(); //返回受影响行 return $this->pre->rowCount(); }catch(PDOException $e){ exit("错误:".$e->getMessage()); } } /** * 删除数据(返回受影响行) * @return Int */ public function delete(){ try{ $s = "delete from {$this->table} {$this->opt['where']}"; //发送语句 $this->pre = self::$SQL->prepare($s); //执行语句 $this->pre->execute(); //返回受影响行 return $this->pre->rowCount(); }catch(PDOException $e){ exit("错误:".$e->getMessage()); } } /* * Lee函数: * 只推荐不存在任何参数的sql语句使用该函数 * */ public function query($str) { try { $s = $str; //发送语句 $this->pre = self::$SQL->prepare($s); //执行语句 $this->pre->execute(); //设置返回关联数组 $this->pre->setFetchMode(PDO::FETCH_ASSOC); //重置各种条件 $this->reset(); return $this->pre->fetchAll(); } catch(PDOException $e) { exit("错误:".$e->getMessage()); } } /* * Lee函数: * 只推荐不存在任何参数的sql语句使用该函数 * */ public function send($str) { try { $s = $str; //发送语句 $this->pre = self::$SQL->prepare($s); //执行语句 $this->pre->execute(); //重置各种条件 $this->reset(); } catch(PDOException $e) { exit("错误:".$e->getMessage()); } } /** * 取数据集(返回符合条件的所有数据) * @return array */ public function select(){ try{ $s = "select ".($this->opt['field']? $this->opt['field']:'*')." from {$this->table} {$this->opt['where']} {$this->opt['order']} {$this->opt['limit']}"; //发送语句 $this->pre = self::$SQL->prepare($s); //执行语句 $this->pre->execute(); //设置返回关联数组 $this->pre->setFetchMode(PDO::FETCH_ASSOC); //重置各种条件 $this->reset(); return $this->pre->fetchAll(); } catch(PDOException $e) { exit("错误:".$e->getMessage()); } } /** * 取一行数据(只返回第一条数据) * @return array */ public function find(){ try{ $s = "select ".($this->opt['field']? $this->opt['field']:'*')." from {$this->table} {$this->opt['where']} {$this->opt['order']} limit 1"; $this->pre = self::$SQL->prepare($s); //执行语句 $this->pre->execute(); //设置返回关联数组 $this->pre->setFetchMode(PDO::FETCH_ASSOC); $arr = $this->pre->fetchAll(); //重置各种条件 $this->reset(); return @$arr[0]; }catch(PDOException $e){ exit("错误:".$e->getMessage()); } } /** * 取指定字段值(当只有一条数据时,直接返回键值,否则返回数组,失败返回false) * @param String $key 指定字段名 * @return mixed */ public function getField($key){ $opt['field'] = $key; $selArr = $this->select(); foreach($selArr as $number=>$value){ $retuls[$number] = $value[$key]; } if(@$retuls == null) $retuls=false; //判断是否单只有一条数据 if(count($retuls) == 1 and $retuls != false){ $retuls = $retuls[0]; } return $retuls; } /** * 设定指定字段值 * @param String $key 指定字段名 * @param mixed $value 字段值 * @return mixed */ public function setField($key,$value){ return $this->save(array($key=>$value)); } /** * 取符合条件的数据总行数 */ public function getCount(){ try{ $s = "select count(*) from {$this->table} {$this->opt['where']}"; $this->pre = self::$SQL->prepare($s); //执行语句 $this->pre->execute(); $count = $this->pre->fetch(); return Intval($count[0]); }catch(PDOException $e){ exit("错误:".$e->getMessage()); } } /** * 查询条件 * @param String $where 查询条件 * @return this */ public function where($where){ $this->opt['where'] = $where? "where ".$where:''; return $this; } /** * 指定排序 * @param String $order 排序规则 desc.倒序 asc.正序 * @return this */ public function order($order){ $this->opt['order'] = $order? "order by ".$order:''; return $this; } /** * 指定欲取的数据条数 * @param Int $min 只传一个参数时,传入欲取的数据条数;否则传入记录偏移量,从0开始 * @param Int $max 欲取的的数据条数 * @return this */ public function limit($min,$max=null){ $this->opt['limit'] = "limit ".intval($min).($max? ','.intval($max):''); return $this; } /** * 指定欲取的字段 * @param String $field 指定字段名称,多个请以(,)分号隔开 * @param this */ public function field($field){ $this->opt['field'] = $field; return $this; } } ?>