<?php
class Model{
protected $tableName='';
protected $pdo = '';
function __construct($tableName=''){
if(!empty($tableName)){
$this->tableName = $tableName;
}
//实例化PDO
$pdo = new PDO('mysql:host=localhost;dbname=company','root','root');
$pdo->exec('set names utf8');
$this->pdo = $pdo;
}
//表添加记录
public function add($arr){
//产生sql语句
$sqlFieldStr = '';
$sqlParamStr = '';
$i = 0;
foreach($arr as $k=>$v){
$sqlFieldStr .= ','.$k;
$sqlParamStr .= ',:p'.$i;
$i++;
}
$sqlFieldStr = substr($sqlFieldStr,1);
$sqlParamStr = substr($sqlParamStr,1);
$sql = "INSERT INTO {$this->tableName}({$sqlFieldStr}) VALUES({$sqlParamStr})";
//准备执行 $this->pdo->prepare('INSERT INTO news(title,content) values(:p0,:p1)
$pdoStatement = $this->pdo->prepare($sql);
//绑定数据
$i = 0;
foreach($arr as $v){
$varName = 'var'.$i;
$$varName = $v;
if(is_int($v)){
$pdoStatement->bindParam(':p'.$i,$$varName,PDO::PARAM_INT);
}else{
$pdoStatement->bindParam(':p'.$i,$$varName,PDO::PARAM_STR);
}
$i++;
}
//执行
$re = $pdoStatement->execute();
return $re;
}
//获取最后插入的记录的主键ID
public function getPrimaryKey(){
return $this->pdo->LastInsertId();
}
//删除
public function delete($where ,$params){
//delete from news where id=:id
$where = empty($where) ? '' : 'WHERE ' .$where;
$sql = "DELETE FROM {$this->tableName} {$where}";
$pdoStatement = $this->pdo->prepare($sql);
//绑定数据
$i = 0;
foreach($params as $k=>$v){
$varName = 'var'.$i;
$$varName = $v;
if(is_int($v)){
$pdoStatement->bindParam($k,$$varName,PDO::PARAM_INT);
}else{
$pdoStatement->bindParam($k,$$varName,PDO::PARAM_STR);
}
}
//执行
$re = $pdoStatement->execute();
return $re;
}
//更新数据
public function update($arr,$where="",$params=array()){
$where = empty($where) ? '' : 'WHERE '.$where;
//拼写sql语句:UPDATE news SET 字段名=:p0,字段名=:p1,... WHERE id=:id;
$i = 0;
$setStr = '';
foreach($arr as $k=>$v){
$setStr .= ','.$k.'=:p'.$i;
$i++;
}
$setStr = substr($setStr, 1);
$sql = "UPDATE {$this->tableName} SET {$setStr} {$where}";
//预处理一条sql语句
$pdoStatement = $this->pdo->prepare($sql);
//绑定数据
$i = 0;
foreach($arr as $v){
//使用一个动态变量,防止$v被覆盖;
$varName = 'var'.$i;
$$varName = $v;
if(is_int($v)){
$pdoStatement->bindParam(':p'.$i,$$varName,PDO::PARAM_INT);
}else{
$pdoStatement->bindParam(':p'.$i,$$varName,PDO::PARAM_STR);
}
$i++;
}
//绑定where条件中的数据 where id=:id array(':id'=>$id)
foreach($params as $k=>$v){
$varName = 'var'.$i;
$$varName = $v;
if(is_int($v)){
$pdoStatement->bindParam($k,$$varName,PDO::PARAM_INT);
}else{
$pdoStatement->bindParam($k,$$varName,PDO::PARAM_STR);
}
$i++;
}
//执行
return $pdoStatement->execute();
}
//查询操作
public function select($fields='*',$where='',$limit='',$order='',$params=array()){
$where = empty($where) ? '' : 'WHERE '.$where;
$limit = empty($limit) ? '' : 'LIMIT '.$limit;
$order = empty($order) ? '' : 'ORDER BY '.$order;
//预处理sql语句 SELECT * FROM tablename ....
$sql = "SELECT {$fields} FROM {$this->tableName} $where $order $limit";
$pdoStatement = $this->pdo->prepare($sql);
//绑定数据
$i = 0;
foreach($params as $k=>$v){
$varName = 'var'.$i;
$$varName = $v;
if(is_int($v)){
$pdoStatement->bindParam($k,$$varName,PDO::PARAM_INT);
}else{
$pdoStatement->bindParam($k,$$varName,PDO::PARAM_STR);
}
$i++;
}
//执行
$re = $pdoStatement->execute();
if($re){ //返回结果集
return $pdoStatement->fetchAll(PDO::FETCH_ASSOC);
}else{
return array();
}
}
}
转载于:https://my.oschina.net/u/2484959/blog/515956