<?php
/**
* 模型类
* @author ZouYiliang <[email protected]>
*/
class Model{
//数据源名称
public $dsn;
//用户名
public $username;
//密码
public $password;
//字符集
public $charset;
//表名
public $tableName;
//表中的字段名
public $fields=array();
public $where='';// "name=:name and age=:age"
public $whereParams=array();// array('name'=>'jack','age'=>28)
public $pdo;
//构造方法
public function __construct($tableName){
$this->dsn='mysql:host=localhost;dbname=s06;port=3306';
$this->username='root';
$this->password='iot';
$this->charset='utf8';
$this->tableName=$tableName;
//初始化
$this->init();
//获到表结构
if(!empty($tableName)){
$this->getFields();
}
}
//初始化
public function init(){
//实例化PDO对象
try{
$this->pdo=new PDO($this->dsn,'root','root');
//设置字符集
$this->pdo->exec('set names '. $this->charset);
}catch(PDOException $ex){
// '连接数据库失败';
}
}
//获到表字段信息，放入fields属性中
private function getFields(){
$sql="desc {$this->tableName}";
$stmt=$this->pdo -> query($sql);
$arr=array();
foreach($stmt as $row){
$arr[]=$row['Field'];
}
//var_dump($arr);exit;
$this->fields=$arr;
}
/**
* 新增操作
* @param $arr array 关联数组,要保存的值
* @return int 成功返回自增id，失败返回false
*/
public function insert($arr){
/*
$arr=array(
'name'=>'jack',
'age'=>'18',
)*/
//(name,age) VALUES ('jack','18')
$field='';
$value='';
foreach($arr as $key=>$item){
//去除不在字段中的信息
if(!in_array($key,$this->fields)){
continue;
}
$field .= '`' . $key . '`'. ',' ;
$value .= " :$key ,";
}
$field = rtrim( $field , ',');
$value = rtrim( $value , ',');
$sql="INSERT INTO {$this->tableName} ({$field}) VALUES ({$value})";
//echo $sql;exit;
$stmt=$this->pdo->prepare($sql);
if($stmt->execute($arr)){
return $this->pdo->lastInsertId();
}else{
return false;
}
}
// $model->delete('id=2');
// $model->delete(array('id'=>2));
//删除操作，返回受影响行数
public function delete($condition=array()){
if(!empty($condition)){
$this->where($condition);
}
if(!empty($this->where)){
$where = " where {$this->where} ";
}
$sql="delete from {$this->tableName} {$where}";
return $this->execute($sql,$this->whereParams);
}
//查询，成功返回二唯数组，没有查到，则返回空数组
/*
$list=array(
array('id'=>1,'name'=>'jack'),
array('id'=>2,'name'=>'mary'),
);
*/
public function select(){
$where='';
if(!empty($this->where)){
$where = " where {$this->where} ";
}
$sql="select * from {$this->tableName} {$where}";
return $this->query($sql,$this->whereParams);
}
/*
array(
'name'=>'jack',
'age'=>18
)
*/
//更新操作， 成功返回受影响行数，失败返回false
public function update($arr){
$where='';
if(!empty($this->where)){
$where = " where {$this->where} ";
}
$str='';
foreach($arr as $key=>$value){
$str .= " $key='$value' ,";
}
//name='jack' , age='18' ,
//echo $str;exit;
$str = rtrim($str , ', ' );
//$sql="update stu set name='jack' , age='18' {$where}";
$sql="update {$this->tableName} set {$str} {$where}";
//echo $sql;exit;
if(mysql_query($sql,$this->link)){
return mysql_affected_rows($this->link);
}
//echo mysql_error();
return false;
}
//where条件
public function where($options=''){
if(is_array($options)){
$arr=array();
foreach($options as $key=>$value){
$arr[] = "`$key` = :$key ";
}
if(count($arr)>0){
$this->where= join(' and ' , $arr);
}
$this->whereParams=$options;
}else{
$this->where=$options;
}
//echo $this->where;exit;
return $this;
}
public function count($str='*'){
$where='';
if(!empty($this->where)){
$where = " where {$this->where} ";
}
$sql="select count($str) from $this->tableName $where";
$result=mysql_query($sql);
$count=0;
if(mysql_num_rows($result)>0){
$count=mysql_result($result,0,0);
mysql_free_result($result);
}
return $count;
}
public function __call($method,$params){
$methods=array('max','min','avg','sum');
if( ! in_array(strtolower($method),$methods)){
trigger_error("Model没有{$method}方法",E_USER_ERROR);
return;
}
$str=$params[0];
$where='';
if(!empty($this->where)){
$where = " where {$this->where} ";
}
$sql="select $method($str) from $this->tableName $where";
$result=mysql_query($sql);
$count=0;
if(mysql_num_rows($result)>0){
$count=mysql_result($result,0,0);
mysql_free_result($result);
}
return $count;
}
//执行查询类型 的SQL，返回数组
//select
public function query($sql,$param=array()){
$stmt = $this->pdo->prepare($sql);
$stmt ->execute($param);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
//执行 增删改 类型 的SQL，返回受影响行数
//insert update delete
public function execute($sql,$params){
$stmt = $this->pdo->prepare($sql);
$stmt ->execute($params);
return $stmt->rowCount();
}
public function __destruct(){
mysql_close($this->link);
}
}
