使用预处理,动态处理需要操作的字段和值。先使用 PDO 来动态绑定参数。
PDOModel:
<?php
/**
* Description of PDO
*
* @author Administrator
*/
class PDOModel {
private $dbtype;//数据库类型
private $dbhost;//数据库地址
private $dbuser;//数据库用户名
private $dbname;//数据库名字
private $dbpwd;//密码
private $dsn;
private $pre='pre_';//数据库前缀
private $long=false;//是否需要长连接
private $field;
static private $db;
//sql语句拼装
private $where;
private $sql=array(
'where'=>'',
'field'=>"`*` FROM ",
'limit'=>'',
'order'=>'',
);
//构造函数,设置基本信息
function __construct($config,$field) {
$this->dbtype=$config['dbtype'];
$this->dbhost=$config['dbhost'];
$this->dbuser=$config['dbuser'];
$this->dbname=$config['dbname'];
$this->dsn=$this->dbtype.":host=".$this->dbhost.";dbname=".$this->dbname;
$this->dbpwd=$config['dbpwd'];
if(isset($config['pre'])){
$this->pre=$config['pre'];
}
if(isset($config['long'])){
$this->long=$config['long'];
}
//设置需要操作的表
$this->field=$field;
//链接数据库
$this->contectSQL();
}
private function contectSQL(){
//开始连接数据库
try{
if($this->long){
$dbh=new PDO($this->dsn,$this->dbuser,$this->dbpwd,array(PDO::ATTR_PERSISTENT=>true));
}else{
$dbh=new PDO($this->dsn,$this->dbuser,$this->dbpwd);
}
} catch (PDOException $ex) {
die("ERROR!:".$ex->getMessage()."<br />");
}
self::$db=$dbh;
}
//where条件
function where($arr){
$sql='';
foreach ($arr as $key=>$val){
$sql.=" WHERE `".$key."`=:".$key;
}
$this->sql['where']=$sql;
$this->where=$arr;
return $this;
}
//order排序
function order($str){
$sql="ORDER BY ".$str;
$this->sql['order']=$sql;
return $this;
}
//limit条件
function limit($str){
$sql=" LIMIT ".$str;
$this->sql['limit']=$sql;
return $this;
}
function field($str='*'){
//按,分割为数组,遍历添加引号拼装
$arr= explode(',', $str);
$count=count($arr);
$ss='';
$i=1;
foreach($arr as $val){
if($i<$count){
$ss.="`".$val."`,";
}else{
$ss.="`".$val."`";
}
$i++;
}
$sql=$ss." FROM ";
$this->sql['field']=$sql;
return $this;
}
//查询数据,查询多条数据
function select(){
$sql="SELECT ".$this->sql['field']."`".$this->pre.$this->field."` " .$this->sql['where']." ".$this->sql['order']." ".$this->sql['limit'];
foreach ($this->where as $key=>$val){
$array[$key]=$val;
}
return $this->prepared($sql, $array,2);
}
//查询uju,查询单条数据
function find(){
$sql="SELECT ".$this->sql['field']."`".$this->pre.$this->field."` " .$this->sql['where']." ".$this->sql['order']." ".$this->sql['limit'];
foreach ($this->where as $key=>$val){
$array[$key]=$val;
}
return $this->prepared($sql, $array,1);
}
//更新数据
function save($array){
$sql=$this->saveSql($array);
foreach ($this->where as $key=>$val){
$array[$key]=$val;
}
return $this->prepared($sql, $array);
//print_r($array);
//echo $sql;
}
function saveSql($array){
$field='';
$count=count($array);
$i=1;
foreach($array as $key=>$val){
if($i<$count){
$field.="`".$key."`=:".$key.",";
}else{
$field.="`".$key."`=:".$key;
}
$i++;
}
$sql="UPDATE `".$this->pre.$this->field."` SET ".$field." ".$this->sql['where'];
return $sql;
}
function delete(){
$sql="DELETE FROM `".$this->pre.$this->field."` ".$this->sql['where'];
$array=array();
foreach ($this->where as $val=>$key){
$array[$val]=$key;
}
return $this->prepared($sql, $array).'<br>';
}
//数据插入方法,返回最终插入ID;
function add($array){
$sql=$this->addSql($array);
return $this->prepared($sql, $array).'<br>';
//echo $sql;
}
private function addSql($array){
$field='';
$value='';
$i=1;
$count=count($array);
foreach($array as $key=>$val){
if($i<$count){
$field.="`".$key."`,";
$value.=":".$key.",";
}else{
$field.="`".$key."`";
$value.=":".$key;
}
$i++;
}
$sql="INSERT INTO `".$this->pre.$this->field."` (".$field.") VALUES (".$value.");";
return $sql;
}
private function prepared($sql,$data,$echo=0){
$stmt=self::$db->prepare($sql,array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
foreach($data as $key=>$val){
$stmt->bindParam(':'.$key, $data[$key]);
}
if(!$stmt->execute()){
echo $stmt->debugDumpParams().'<br>';
$errorinfo=$stmt->errorInfo();
die('ERROR:'.$errorinfo[2].'<br>');
}else{
// print_r($stmt->fetchAll());
switch ($echo){
case 0:
return $stmt->rowCount();
break;
case 1:
return $stmt->fetch(PDO::FETCH_ASSOC);
break;
case 2:
return $stmt->fetchAll(PDO::FETCH_ASSOC);
break;
}
}
}
function __destruct() {
if(!$this->long){
self::$db=null;
}
}
}
模仿 TP 框架,用一个 M()方法:
function M($field){
$config=array(
'dbtype'=>'mysql',
'dbhost'=>'localhost',
'dbname'=>'testPdo',
'dbuser'=>'root',
'dbpwd'=>''
);
$m=new PDOModel($config,$field);
return $m;
}
所有传值都应该是数组
<?php
require_once"PDOModel.class.php";
$result= M('infos')->where(array('name'=>'adf'))->find();