mysql sql model设置_操作mysql model

protected $tabName;//表名

protected $where=null;//条件

protected $limit=null;//限制

protected $orderBy=null;//排序

protected $fields=array();//字段信息

protected $pk;//主键的名称

protected $sql;//sql语句

function __construct($tabName){$this->link=mysql_connect(HOST,USER,PWD,true);mysql_select_db(DBNAME,$this->link);mysql_query(‘set names utf8‘);$this->tabName=$tabName;//获取表中的所有字段，放置在成员属性 fields中

$this->getFields();

}/**

* 获取字段信息，以数组的形式写入到成员属性fields中

* 获取主键的名称，写入到成员属性pk中

*

**/

functiongetFields(){$sql="desc $this->tabName";$result=mysql_query($sql);if($result && mysql_num_rows($result)>0){while($arr=mysql_fetch_assoc($result)){//print_r($arr);

//echo "

---

";

$this->fields[]=$arr[‘Field‘];if($arr[‘Key‘]==‘PRI‘){$this->pk=$arr[‘Field‘];

}

}

}//print_r($this->fields);

//exit;

}/** select查询获取多条语句

* @return array 查询的数据的数组

*

**/

/** find 单条数据

* max 获取最大值

* min 获取最小值

* avg 获取平均数

* sum 获取和

*

**/

functionselect(){$data=array();$this->sql="select * from {$this->tabName} {$this->where} {$this->orderBy} {$this->limit}";$result=mysql_query($this->sql);if($result && mysql_num_rows($result)>0){while($arr=mysql_fetch_assoc($result)){$data[]=$arr;

}

}$this->clearAll();return $data;

}/**

* 添加数据

* @param array 要添加的数据

* @return int 影响行数

*

**/

function insert($var=array()){if(empty($var)){$var=$_POST;

}$keys=‘‘;$values=‘‘;foreach($var as $k=>$v){if(!in_array($k,$this->fields)){continue;

}$keys.="`{$k}`,";$values.="‘{$v}‘,";

}$keys=rtrim($keys,‘,‘);$values=rtrim($values,‘,‘);$this->sql="insert into {$this->tabName}({$keys}) values({$values})";$result=mysql_query($this->sql);if($result){return mysql_affected_rows();

}else{return 0;

}

}/**

* 修改数据

* @param array 要添加的数据

* @return int 影响行数

*

* 如 array(‘name‘=>‘abc‘,‘sex‘=>0,‘password‘=>‘123‘,‘id‘=>10);

* `name`=‘abc‘,`sex`=‘0‘,`password`=‘123‘

**/

function update($var=array()){$tmp=‘‘;if(empty($var)){$var=$_POST;

}$keys=‘‘;$values=‘‘;foreach($var as $k=>$v){if(!in_array($k,$this->fields) || $k==$this->pk){ //过滤掉非法字段以及主键

continue;

}$tmp.="`{$k}`=‘{$v}‘,";

}$tmp=rtrim($tmp,‘,‘);//$this->sql="insert into {$this->tabName}({$keys}) values({$values})";

$this->sql="update {$this->tabName} set {$tmp} where {$this->pk}={$var[$this->pk]}";$result=mysql_query($this->sql);if($result){return mysql_affected_rows();

}else{return 0;

}

}/**

* 删除方法

* @param int id号

* @return int 影响行数

*

**/

function delete($var){$sql="delete from {$this->tabName} where {$this->pk}={$var}";$result=mysql_query($sql);if($result){return mysql_affected_rows();

}else{return 0;

}

}protected functionclearAll(){$this->where=null;$this->limit=null;$this->orderBy=null;

}/**

* 条件函数

* 在函数中向成员属性$where传递条件的值

* @param string $var 条件值

* @return Model 对象

**/

function where($var){$where=‘‘;//临时变量where

if(is_int($var)){$where="id={$var}";

}elseif(is_string($var)){$where="{$var}";

}elseif(is_array($var)){$logic=isset($var[‘_logic‘])?$var[‘_logic‘]:‘and‘;//array(‘name‘=>‘ztz‘,‘sex‘=>‘1‘,‘abc‘=>‘or‘)

//"name=‘ztz‘ and sex=1"

foreach($var as $k=>$v){//判断键是否是合法的字段名称，非法的被过滤

//$k = ‘abc‘;

//$this->fields array(‘id‘,‘name‘,‘sex‘,‘password‘);

if(!in_array($k,$this->fields)){continue;

}$where.="{$k} = ‘{$v}‘ {$logic} ";

}$where=rtrim($where," {$logic} ");

}else{die(‘您在where中输入的数据不再处理范围之内，请核对代码，或者联系项目经理解决‘);//return $this;

}$this->where="where $where";//where id >10

return $this;

}function limit($var){$this->limit="limit {$var}";return $this;

}function orderBy($var){$this->orderBy=" order by {$var}";return $this;

}/**

* 获取最后一条sql语句

* @return string $sql 返回成员属性sql语句

*

**/

functiongetLastSql(){return $this->sql;

}

}$user=new Model(‘user‘);//where 条件可以是

//string 可以直接用

//int id=10

// "name=‘ztz‘ or sex=1" array(‘name‘=>‘ztz‘,‘sex‘=>‘1‘,‘_logic‘=>‘and‘)

$user->update();$arr=$user->select();//$user->delete(10);

print_r($arr);//echo "

---

";

//$arr2=$user->select();

//print_r($arr2);

echo "

---

";echo $user->getLastSql();?>Model.class

姓名:性别:男 女