- /**
- * Created by PhpStorm.
- * User: MCtion
- * 接收数据库名,连接数据库,提供CURD接口
- * 连接模式:PDO
- * 方法:Insert,Update,Delete,Select,PrePare,ClearKey,Limit,OrderBy,Where
- */
- if(!defined("__webROOT__")) define("__WEBROOT__",$_SERVER['DOCUMENT_ROOT']);
- class NewSql {
- public $Db; //数据库实例
- protected $_Sql = 'mysql';
- protected $_Host = 'localhost';
- protected $_User = 'TestUser';
- protected $_Pass = 'TestUserSqlPass';
- protected $_DbName = null; //数据库名
- protected $_Where = '';
- protected $_OrderBy = '';
- protected $_Limit = '';
- protected $_Field = '*';
- protected $_Clear = 0; //状态,0表示查询条件干净,1表示查询条件污染
- /**
- * 初始化类
- * @param string $DbName 设置数据库名
- */
- public function __construct($DbName = 'test'){
- $this -> _DbName = $DbName;
- $Db = new PDO($this -> _Sql.':host='.$this -> _Host.';dbname='.$this -> _DbName,$this -> _User,$this -> _Pass,array(PDO::ATTR_PERSISTENT => True));
- $Db -> exec("set names utf8");
- $this -> Db = $Db;
- }
- /**
- * 插入方法
- * @param string $TabName 操作的数据表名
- * @param array $Val 字段-值数组
- * @return int 受影响的行数
- */
- public function Insert($TabName,array $Val){
- foreach($Val as $K=>$V){
- $Key_Arr[] = $K;
- $Val_Arr[] = is_string($V)?"'".$V."'":$V;
- }
- $Query = "insert into ".$TabName."(".implode(',',$Key_Arr).") values(".implode(',',$Val_Arr).")";
- return $this -> Db -> exec($Query);
- }
- /**
- * 删除方法
- * @param string $TabName 操作的数据表名
- * @return int 受影响的行数
- */
- public function Delete($TabName){
- $Query = "delete from ".$TabName." ".$this -> _Where;
- $this -> _Clear = 1;
- $this -> Clear();
- return $this -> Db -> exec(trim($Query));
- }
- /**
- * 更新函数
- * @param string $TabName 操作的数据表名
- * @param array $Val 参数数组
- * @return int 受影响的行数
- */
- public function Update($TabName,array $Val){
- $ValStr = '';
- foreach($Val as $K=>$V){
- $ValStr .= $K.'=';
- $ValStr .= is_string($V) ? "'".$V."'," : $V.',';
- }
- $ValStr = substr($ValStr,0,strlen($ValStr) - 1);
- $Query = "update ".trim($TabName)." set ".trim($ValStr)." ".trim($this -> _Where);
- return $this -> Db -> exec($Query);
- }
- /**
- * 查询函数
- * @param string $TabName 操作的数据表名
- * @return PDOStatement
- */
- public function Select($TabName){
- $Query = "select ".trim($this -> _Field)." from ".$TabName." ".trim($this -> _Where)." ".trim($this -> _OrderBy)." ".trim($this -> _Limit);
- $this -> _Clear = 1;
- $this -> Clear();
- return $this -> Db -> query(trim($Query));
- }
- /**
- * @param string $TabName 要操作的数据表名
- * @return PDOStatement 返回构造后的语句对象
- */
- public function PrePare($TabName){
- $Query = "select ".trim($this -> _Field)." from ".$TabName." ".trim($this -> _Where)." ".trim($this -> _OrderBy)." ".trim($this -> _Limit);
- $this -> _Clear = 1;
- $this -> Clear();
- return $this -> Db -> prepare(trim($Query));
- }
- /**
- * @param array $Option 组合条件的二维数组,例:$Options['Field1'] = array(1,'=>','or')
- * @return $this
- */
- public function Where(array $Option){
- if($this -> _Clear > 0) $this -> Clear();
- $this -> _Where = ' where';
- $ContConditions = 'and';
- foreach($Option as $K=>$V){
- $Conditions1 = isset($V[1]) ? $V[1] : '=';
- $Conditions2 = isset($V[2]) ? $V[2] : 'and';
- $ContConditions = $Conditions2;
- $this -> _Where .= ' '.$K.' '.$Conditions1.' '.$V[0].' '.$Conditions2;
- }
- $this -> _Where = substr($this -> _Where,0,strlen($this -> _Where) - strlen($ContConditions));
- return $this;
- }
- /**
- * 设置排序
- * @param array $Val 排序条件数组 例:array('sort'=>'desc')
- * @return $this
- */
- public function OrderBy(array $Val){
- if($this -> _Clear > 0) $this -> Clear();
- $this -> _OrderBy = "order by ";
- foreach($Val as $K=>$V){
- $this -> _OrderBy .= $K." ".$V.",";
- }
- $this -> _OrderBy = substr($this -> _OrderBy,0,strlen($this -> _OrderBy) - 1);
- return $this;
- }
- /**
- * 设置查询行数及页数
- * @param $Page $PageSize不为空时为页数,否则为行数
- * @param null $PageSize 为空则函数设定取出行数,不为空则设定取出行数及页数
- * @return $this
- */
- public function Limit($Page,$PageSize = null){
- if($this -> _Clear > 0) $this -> Clear();
- if($PageSize == null){
- $this -> _Limit = "limit ".$Page;
- }else{
- $SelStart = ($Page - 1) * $PageSize;
- $this -> _Limit = "limit ".$SelStart.",".$PageSize;
- }
- return $this;
- }
- /**
- * 设置查询字段
- * @param array $Field 字段数组
- * @return $this
- */
- public function Field(array $Field){
- if($this -> _Clear > 0) $this -> Clear();
- $this -> _Field = '';
- foreach($Field as $K=>$V){
- $this -> _Field .= $V.",";
- }
- $this -> _Field = substr($this -> _Field,0,strlen($this -> _Field) - 1);
- return $this;
- }
- /**
- * 清理标记函数
- */
- protected function Clear(){
- $this -> _Where = '';
- $this -> _OrderBy = '';
- $this -> _Limit = '';
- $this -> _Clear = 0;
- $this -> _Field = '*';
- }
- /**
- * 手动清理标记
- * @return $this
- */
- public function ClearKey(){
- $this -> _Where = '';
- $this -> _OrderBy = '';
- $this -> _Limit = '';
- $this -> _Clear = 0;
- $this -> _Field = '*';
- return $this;
- }
- /**
- * @param string $Address 备份类型,默认为SERVER,用以返回后判断动作
- * @return mixed|string 返回备份信息的JSON格式数据
- */
- public function BackUp($Address = 'SERVER'){
- //连接数据库
- $DB = new MySqli($this -> _Host,$this -> _User,$this -> _Pass,$this -> _DbName);
- $DB -> query("set names utf8");
- //检查并创建备份目录、名称
- $FilePath = '/BackUp/';
- if(!file_exists(__WEBROOT__.$FilePath)){
- mkdir(__WEBROOT__.$FilePath,0777,True);
- }
- $FileName = 'Back'.date('YmdHis').'.sql';
- $File = fopen(__WEBROOT__.$FilePath.$FileName,'a'); //追加模式打开文件句柄
- //创建头部信息
- $Sql = Null;
- $Sql .= "-- Server Type :MySql \\r\\n";
- $Sql .= "-- Create User : \\r\\n";
- $Sql .= "-- Create Time :".date('Y-m-d H:i:s')." \\r\\n\\r\\n\\r\\n";
- fwrite($File,$Sql); //写入头部信息
- $Databases = $DB -> query("show tables"); //查询所有数据表
- while($vo = $Databases -> fetch_array(MYSQL_NUM)){ //遍历数据表
- $Sql = Null;
- $Table = $DB -> query("show create table ".$vo[0]); //查询当前表的创建语句
- if($vo2 = $Table -> fetch_array(MYSQL_NUM)){
- $Sql = Null;
- //创建表语句
- $Sql .= "-- Create Table ".$vo2[0]." \\r\\n";
- $Sql .= $vo2[1].";\\r\\n\\r\\n";
- //创建数据语句
- $Sql .= "-- Insert Table ".$vo2[0]." \\r\\n";
- $Insert = $DB -> query("select * from ".$vo2[0]);
- while($vo3 = $Insert -> fetch_array(MYSQL_ASSOC)){
- $Sql .= "Insert Into ".$vo2[0]." Values(";
- foreach($vo3 as $Key=>$Val){
- $Sql .= "'".$DB -> real_escape_string($Val)."',";
- }
- $Sql = substr($Sql,0,strlen($Sql) - 1);
- $Sql .= ");\\r\\n";
- }
- }else{
- $BackUpArr['address'] = strtoupper($Address);
- $BackUpArr['state'] = 1;
- $BackUpArr['msg'] = '失败:无法读取表语句,请重试或联系管理员';
- $BackUpArr['filepath'] = '';
- @unlink(__WEBROOT__.$FilePath.$FileName);
- return json_encode($BackUpArr);
- }
- $Sql .= "\\r\\n\\r\\n";
- fwrite($File,$Sql); //每个数据库表插入一次
- }
- fclose($File);
- $BackUpArr['address'] = strtoupper($Address);
- $BackUpArr['state'] = 200;
- $BackUpArr['msg'] = '备份成功';
- $BackUpArr['filepath'] = $FilePath.$FileName;
- return json_encode($BackUpArr);
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/1304201512214.html
来源: http://www.codesnippet.cn/detail/1304201512214.html