MVCにおけるPDOプリプロセッシング付きModelベースクラス
4097 ワード
<?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();
}
}
}