共享一个Model类,先贴上Model代码,在用实例讲解调用方法
<?php
/**
* 模型基类
* 数据库处理类
*/
class Model {
protected $table = '';
protected $error = null;
protected $config = null;
protected $conn = null;
protected $sql = null;
protected $where = '';
protected $order = null;
protected $group = null;
protected $field = '*';
protected $limit = null;
protected $join = null;
protected $oldTable = null;
const EXISTS_VAILIDATE = 1; // 表单存在字段则验证
const MUST_VALIDATE = 2; // 必须验证
const MODEL_INSERT = 1; // 插入模型数据
const MODEL_UPDATE = 2; // 更新模型数据
const MODEL_BOTH = 3; // 包含上面两种方式
const NO_AND = 1; // where不需要and
const YES_AND = 2; // where需要and
const YES_OR = 3; // where需要or
protected $cachePath=SYSDIR_SMARTY_TEMPLATE_C; //缓存存放路径
protected function initVar(){
$this->field='*';
$this->where='';
$this->group=null;
$this->limit=null;
$this->join=null;
$this->order=null;
}
/**
* 构造函数
*
* @param string $table
* @param array $config
*/
public function __construct($table = null, $config = null) {
$this->config = empty ( $config ) ? C () : $config;
empty ( $table ) or $this->table ( $table );
// $this->connect ();
}
private function filter($name) {
if (is_array ( $name )) {
foreach ( $name as $k => $v ) {
$name [$k] = $this->filter ( $v );
}
return $name;
} else {
if(!$this->conn){
$this->connect();
}
return mysql_real_escape_string ( trim ( $name ));
}
}
/**
* mysql连接
*/
private function connect() {
if (! ($this->conn = mysql_connect ( $this->config ['host'], $this->config ['user'], $this->config ['passwd'] ))) {
$this->error = "连接不上Mysql数据库";
}
mysql_select_db ( $this->config ['dbName'] );
if (mysql_get_server_info () > '4.1') {
mysql_query ( "set names utf8");
}
}
/**
* 执行sql语句
*
* @param string $sql
* @throws Exception
* @return 查询语句返回查询结构
*/
public function query($sql) {
if (! $this->conn)
$this->connect ();
$this->sql = $sql;
$rs = mysql_query ( $sql );
$this->initVar();
if (is_bool ( $rs )) {
return $rs;
} else {
$data = array ();
while ( $row = mysql_fetch_array ( $rs, MYSQL_ASSOC ) ) {
$data [] = $row;
}
return $data;
}
}
/**
* 插入数据库
*
* @param array $data
* @return bool int 失败返回false
*/
public function save($data) {
if (! empty ( $data ) && is_array ( $data )) {
if (is_array ( $data [0] )) {
foreach ( $data as $value ) {
$rs=$this->save ( $value );
}
return $rs;
} else {
$field = '`' . implode ( '`,`', array_keys ( $data ) ) . '`';
$value = "'" . implode ( "','", $this->filter ( array_values ( $data ) ) ) . "'";
$this->sql = 'insert into ' . $this->table . '(' . $field . ')' . 'values(' . $value . ');';
$rs = $this->query ( $this->sql );
$structure = $this->getStructure ();
if ($structure [$structure ['_pk']] ['extra'] == 'auto_increment') {
return mysql_insert_id ();
} else {
return $rs;
}
}
} else {
return false;
}
}
/**
* 数据更新
*
* @param array $data
* @return bool
*/
public function update($data) {
if (! empty ( $data ) && is_array ( $data )) {
$structure = $this->getStructure ();
$data = $this->filter ( $data );
$flag = true;
$field = '';
foreach ( $data as $key => $value ) {
if ($key == $structure ['_pk'])
continue;
if ($flag) {
$flag = false;
} else {
$field .= ',';
}
$field .= "`{$key}`='{$value}'";
}
$this->sql = 'update ' . $this->table . ' set ' . $field . ' where ';
$this->sql .= empty ( $data [$structure ['_pk']] ) ? $this->where : "`{$structure['_pk']}`='{$data[$structure['_pk']]}'";
return $this->query ( $this->sql );
} else {
return false;
}
}
/**
* 删除数据
*
* @return bool
*/
public function delete($data = null) {
! empty ( $data ) and $this->where ( $data );
$this->sql = 'delete from ' . $this->table . ' where ' . $this->where;
return $this->query ( $this->sql );
}
/**
* 更改表名
*
* @param mix $table
* table(array('User'=>'user')); 查询前缀+User表,并重新命名为user
* table('user'); user
* @return Model
*/
public function table($table) {
if (is_string ( $table )) {
$this->table = '`' . C ( 'dbPrefix' ) . $table . '`';
$this->oldTable = C ( 'dbPrefix' ) . $table;
}
if (is_array ( $table )) {
$flag=true;
foreach($table as $key=>$vo){
if($flag){
$flag=false;
}else{
$this->table.=',';
}
$this->table.='`' . C ( 'dbPrefix' ) . $key . '` as `' . $vo . '`';
$this->oldTable=$key;
}
}
return $this;
}
// order排序
public function order($order) {
$this->order = $order;
return $this;
}
// join
public function join($join) {
$this->join = $join;
return $this;
}
// group分组
public function group($group) {
$this->group = $group;
return $this;
}
/**
* 查询的字段 默认为*
* field(array('user.id','user.username',array('title','t')))
* field('id,username');
*
* @param mix $fields
* @return Model
*/
public function field($fields = '*') {
if (is_string ( $fields )) {
$this->field = $fields;
} else if (is_array ( $fields )) {
$this->field = '';
$flag = NO_AND;
foreach ( $fields as $field ) {
if (is_array ( $field )) {
$flag = $this->getOneField ( $field [0], $flag );
$this->field .= ' as ';
$flag = NO_AND;
$flag = $this->getOneField ( $field [1], $flag );
} else {
$flag = $this->getOneField ( $field, $flag );
}
}
}
return $this;
}
/**
* 获取一个field 如果是date那么就是`date` 如果是user.id那么就是user.id
* @param string $field
* @param int $flag
* @return string
*/
private function getOneField($field, $flag) {
if (strpos ( $field, '.' ) == false) {
switch ($flag) {
case NO_AND :
$flag = YES_AND;
break;
case YES_AND :
$this->field .= ',';
break;
}
$this->field .= '`' . $field . '`';
} else {
switch ($flag) {
case NO_AND :
$flag = YES_AND;
break;
case YES_AND :
$this->field .= ',';
break;
}
$this->field .= $field;
}
return $flag;
}
// limit
public function limit($limit) {
$this->limit = $limit;
return $this;
}
// 事务开启
public function begin() {
return $this->query ( 'begin' );
}
// 事务提交
public function commit() {
return $this->query ( 'commit' );
}
// 事务回滚
public function rollback() {
return $this->query ( 'rollback' );
}
/**
* where条件查询
*
* @param mix $map
* @return Model
*/
public function where($map) {
if (is_array ( $map )) {
//$map = $this->filter ( $map );
$flag = NO_AND;
foreach ( $map as $key => $value ) {
if (strpos ( $key, '|' )) {
$keys = explode ( '|', $key );
$this->where .= '(';
foreach ( $keys as $i => $keyVo ) {
$i == 0 or $flag = YES_OR;
if (is_array ( $value )) {
$flag = $this->getOneWhere ( $keyVo, $value [$i], $flag );
} else {
$flag = $this->getOneWhere ( $keyVo, $value, $flag );
}
}
$this->where .= ')';
} else {
$flag = $this->getOneWhere ( $key, $value, $flag );
}
}
}
if (is_string ( $map )) {
$this->where = $map;
}
return $this;
}
private function getOneWhere($key, $value, $flag = NO_AND) {
switch ($flag) {
case NO_AND :
$flag = YES_AND;
break;
case YES_AND :
$this->where .= ' and ';
break;
case YES_OR :
$this->where .= ' or ';
$flag = YES_AND;
break;
}
$this->where .= '(';
$this->where .= strpos ( $key, '.' ) ? $key : '`' . $key . '`';
if (is_array ( $value )) {
switch ($value [0]) {
case 'in' :
is_array($value[1]) and $value[1]=implode(',',$value[1]);
$this->where .= ' in(' . $value [1] . ')';
break;
case 'between' :
$this->where .= ' between \'' . $value [1] [0] . '\' and \'' . $value [1] [1] . '\'';
break;
default :
$this->where .= ' ' . $value [0] . '\'' . $value [1] . '\'';
}
}else{
$this->where .= '=' . '\'' . $value . '\'';
}
$this->where .= ')';
return $flag;
}
// 根据条件获取sql语句
protected function getSql() {
$this->sql = 'select ' . $this->field . ' from ';
$this->sql .= $this->table;
empty ( $this->join ) or $this->sql .= ' ' . $this->join;
empty ( $this->where ) or $this->sql .= ' where ' . $this->where;
empty ( $this->group ) or $this->sql .= ' group by ' . $this->group;
empty ( $this->order ) or $this->sql .= ' order by ' . $this->order;
empty ( $this->limit ) or $this->sql .= ' limit ' . $this->limit;
return $this->sql;
}
/**
* 计算表中数据
* @return int
*/
public function count(){
$this->field='count(*) as `count`';
$rs=$this->find();
return $rs['count'];
}
/**
* 查询数据库,获取二维数组
*
* @return array bool
*/
public function select() {
$this->getSql ();
return $this->query ( $this->sql );
}
/**
* 查询数据
*
* @return array
*/
public function find() {
$this->getSql ();
$this->sql .= ' limit 1';
$data = $this->query ( $this->sql );
return $data [0];
}
/**
* 获取最后一条sql语句
*
* @return string
*/
public function getLastSql() {
return $this->sql;
}
/**
* 析构函数,释放mysql连接
*/
public function __destruct() {
if (isset ( $this->conn ))
mysql_close ( $this->conn );
}
/**
* 获取错误信息
*
* @return string
*/
public function getError() {
return $this->error;
}
/**
* 设置/读取缓存 和F的区别是不用序列化字段,小文件速度更快
*
* @param string $name
* @param anytype $data
* @param int $time
* 单位为秒 读取时才判断
* @throws Exception
* @return mixed NULL
*/
protected function fileExport($name, $data = null) {
$path = $cachePath;
if (isset ( $data )) {
if (file_put_contents ( $path . '/' . $name .'.php', '<?php return ' . var_export ( $data, true ) . ';' ) > 0) {
return true;
} else {
throw new Exception ( "生成缓存文件失败" );
}
} else {
if (file_exists ( $file = $path . '/' . $name . '.php' )) {
$content = require $file;
return $content;
} else {
return null;
}
}
}
/**
* 获取表结构
*/
protected function getStructure($table = null) {
empty ( $table ) and $table = $this->oldTable;
static $_structure = array ();
if (isset ( $_structure [$table] )) {
return $_structure [$table];
}
$fileName = $cachePath . '/' . $table . '.php';
$data = $this->fileExport ( $table);
if ($data == null) {
$structure = $this->query ( "describe " . $table );
$keys = array (
'Field',
'Type',
'Null',
'Key',
'Default',
'Extra'
);
foreach ( $structure as $vo ) {
if ($vo ['Key'] == 'PRI') {
$data ['_pk'] = $vo ['Field'];
}
foreach ( $keys as $key ) {
$data [$vo ['Field']] [strtolower ( $key )] = $vo [$key];
}
}
$this->fileExport ( $table, $data);
$_structure [$table] = $data;
}
return $data;
}
/**
* 获取当前操作,是新增数据还是更新数据
*
* @param array $data
* @return bool
*/
private function getOperation($data) {
$structure = $this->getStructure ();
if (array_key_exists ( $structure ['_pk'], $data )) {
return self::MODEL_UPDATE;
} else {
return self::MODEL_INSERT;
}
}
/**
* 进行自动验证,自动完成
* 自动验证规则
* array(
* @param string field 验证字段
* @param string rule 验证规则
* @param string error 错误提示
* @param mix [addition] 附加规则
* @param int [condition] 验证条件: 0:存在就验证EXISTS_VAILIDATE(默认) 1:必须验证:EXISTS_VAILIDATE
* @param int [operation] 验证条件2: 0:插入数据库时验证:MODEL_INSERT 1:更新数据库时验证:MODEL_UPDATE 2:插入数据库和更新数据库都验证:MODEL_BOTH
* )f
* @param array $data
* @return boolean
*/
public function create(&$data = null) {
$data==null and $data=&$_POST;
if (isset ( $this->_validate )) {
$keys = array (
'field',
'rule',
'error',
'addition',
'condition',
'operation'
);
foreach ( $this->_validate as $vo ) {
$keyList = $keys;
if (! $this->isValidate ( $data, array_combine ( array_splice ( $keyList, 0, count ( $vo ) ), $vo ) )) {
$this->error = $vo [2];
return false;
break;
}
}
}
if (isset ( $this->_auto )) {
$keys = array (
'field',
'rule',
'addition',
'operation'
);
foreach ( $this->_auto as $vo ) {
$keyList = $keys;
$this->isAuto ( $data, array_combine ( array_splice ( $keyList, 0, count ( $vo ) ), $vo ) );
}
}
return true;
}
/**
* 是否进行验证
*
* @param array $data
* @param array $value
* @return boolean
*/
private function isValidate($data, $value) {
( int ) $value ['condition'] = empty( $value ['condition']) ? self::EXISTS_VAILIDATE : $value ['condition'];
( int ) $value ['operation'] = empty( $value ['operation']) ? self::MODEL_BOTH : $value ['operation'];
switch ($value ['condition']) {
case self::EXISTS_VAILIDATE :
if ((isset ( $data [$value ['field']] )) && ($value ['operation'] == self::MODEL_BOTH || $value ['operation'] == $this->getOperation ( $data ))) {
return $this->validate ( $data, $value );
} else {
return true;
}
break;
case self::MUST_VALIDATE :
if (! isset ( $data [$value ['field']] )) {
return false;
} else if ($value ['operation'] == MODEL_BOTH || $value ['operation'] == $this->getOperation ( $data )) {
return $this->validate ( $data, $value );
} else {
return true;
}
break;
default :
return false;
break;
}
}
/**
* 进行自动验证
*
* @param array $data
* @param array $value
* @return boolean
*/
private function validate($data, $value) {
$validate = array (
'require' => '/.+/',
'email' => '/^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$/',
'url' => '/^http:\/\/[A-Za-z0-9]+\.[A-Za-z0-9]+[\/=\?%\-&_~`@[\]\':+!]*([^<>\"\"])*$/',
'currency' => '/^\d+(\.\d+)?$/',
'number' => '/^\d+$/',
'zip' => '/^[1-9]\d{5}$/',
'integer' => '/^[-\+]?\d+$/',
'double' => '/^[-\+]?\d+(\.\d+)?$/',
'english' => '/^[A-Za-z]+$/'
);
switch ($value ['rule']) {
case 'require' :
if (empty ( $data [$value ['field']] )) {
return false;
} else {
return true;
}
break;
case 'in' :
if (is_string ( $value ['addition'] )) {
$value ['addition'] = explode ( ',', $value ['addition'] );
}
if (in_array ( $data [$value ['field']], $value ['addition'] )) {
return true;
} else {
return false;
}
break;
case 'between' :
if (is_string ( $value ['addition'] )) {
$value ['addition'] = explode ( ',', $value ['addition'] );
}
if ($data [$value ['field']] >= $value ['addition'] [0] && $data [$value ['field']] <= $value ['addition'] [1]) {
return true;
} else {
return false;
}
break;
case 'email' :
if (preg_match ( $validate [$value ['rule']], $data [$value ['field']] )) {
return true;
} else {
return false;
}
break;
case 'url' :
if (preg_match ( $validate [$value ['rule']], $data [$value ['field']] )) {
return true;
} else {
return false;
}
break;
case 'confirm' :
if (md5 ( $data [$value ['field']] ) == md5 ( $data [$value ['addition']] )) {
return true;
} else {
return false;
}
break;
case 'number' :
if (preg_match ( $validate [$value ['rule']], $data [$value ['field']] )) {
return true;
} else {
return false;
}
break;
case 'function' :
if (is_array ( $value ['addition'] )) {
$function = $value ['addition'] [0];
array_shift ( $value ['addition'] );
if ($function ( implode ( ',', $value ['addition'] ) )) {
return true;
} else {
return false;
}
}
if (is_string ( $value ['addition'] )) {
if ($value ['addition'] ()) {
return true;
} else {
return false;
}
}
break;
case 'length' :
if (is_string ( $value ['addition'] )) {
$value ['addition'] = explode ( ',', $value ['addition'] );
}
switch (count ( $value ['addition'] )) {
case 1 :
if (strlen ( $data [$value ['field']] ) == $value ['addition'] [0]) {
return true;
} else {
return false;
}
break;
case 2 :
if (mb_strlen ( $data [$value ['field']],'UTF8' ) >= $value ['addition'] [0] && mb_strlen ( $data [$value ['field']],'UTF8' ) <= $value ['addition'] [1]) {
return true;
} else {
return false;
}
break;
default :
return false;
break;
}
break;
case 'unique' :
$rs = $this->where ( array (
$value ['field'] => $data [$value ['field']]
) )->find ();
if (empty ( $rs )) {
return true;
} else {
return false;
}
break;
case 'regex' :
if (preg_match ( $value ['addition'], $data [$value ['field']] )) {
return true;
} else {
return false;
}
break;
default :
return false;
break;
}
}
/**
* 是否进行自动完成
*
* @param array $data
* @param array $value
*/
private function isAuto(&$data, $value) {
( int ) $value ['operation'] = empty ( $value ['operation'] ) ? self::MODEL_INSERT : $value ['operation'];
if (($value ['operation'] == self::MODEL_BOTH) || ($value ['operation'] == $this->getOperation ( $data ))) {
$this->auto ( $data, $value );
}
}
/**
* 进行自动完成操作
*
* @param array $data
* @param array $value
*/
private function auto(&$data, $value) {
switch ($value ['rule']) {
case 'function' :
if (is_string ( $value ['addition'] )) {
$value ['addition'] = explode ( ',', $value ['addition'] );
}
$function=$value['addition']['0'];
array_shift($value ['addition'] );
$data [$value ['field']] = $function( implode ( ',', $value ['addition'] ) ) ;
break;
case 'const' :
isset($data [$value ['field']]) or $data [$value ['field']]=$value ['addition'];
break;
default :
break;
}
}
}
继续添加一个C方法,用于存储一些配置信息:
/**
* 设置/读取 配置
*
* @param string $name
* @param string $value
*/
function C($name = null, $value = null) {
static $_config = array ();
if (empty ( $name )) {
return $_config;
}
if (is_string ( $name )) {
if (empty ( $value ))
return $_config [$name];
else
$_config [$name] = $value;
}
if (is_array ( $name ))
$_config = array_merge ( $_config, $name );
return null;
}
下面就是使用方法:
查询方法:
<?php
$config=array('host'=>'127.0.0.1','user'=>'root','passwd','prefix'=>'m_');
C($config);
$db=new Model('user');//对m_news表操作
//取出全部数据
$list=$db->select();
//等同于 select * from `m_user`;
//条件查询
$list=$db->where("username='root'")->select();
//等同于 select * from `m_user` where `username='root'
//支持数组条件查询
$map['username']='root';
$list=$db->where($map)->select();
//等同于 select * from `m_user` where `username`='root';
//注意,尽量采用数组,因为会进行自动过滤
//条件查询or使用
$map[username|email]='root';
$list=$db->where($map)->select();
//等同于 select * from `m_user` where (`username`='root') or (`email`='root')
其他复杂条件查询
$id=array(1,2,3,4,5,6,7);
$map['id']=array('in',$id);
$list=$db->where($map)->select();
//等同于 select * from `m_user` where (`id` in (1,2,3,4,5,6,7));
$map['date']=array('>','2013-1-1');
$list=$db->where($map)->select();
//等同于 select * from `m_user` where `date`>'2013-1-1';
//选择字段查询
$list=$db->field('id','name','title')->select();
//等同于 selelect id,name,title from `m_news`
//支持数组
$list=$db->field(array('id','name','title'))->select();
//等同于 select `id`,`name`,`title` from `m_news`;
//注意,只要是数组里的,会根据字段默认都加上`符号,如果你是news.id这样的形式,就不会加`符号
//字段重命名方式(数组)
$list=$db->field(array(array('id','uid'),'name'))->select();
//等同于 select `id` as `uid`,`name` from `m_news`;
//limit order group 例子
$db->order("id desc,sort asc")->group("id")->limit(2,10);
//看到这里,不写,你也看出来了吧
//只获取一条数据
$user=$db->where(array('id'=>1))->find();
//获取总数
$count=$db->count();
//自己执行sql语句
$list=$db->query("select * from m_news");
多表查询:
<?php
$list=M('user'=>'user')->join('join m_role role on user.role_id=role.id')->find();
插入数据库:
<?php
//配置神马的都同上.我就直接$db代表实话的model
$data['username']='root';
$data['password']='111';
$data['email']=xx@gmail.com';
$rs=$db->save($data);
//如果数据库主键是自增长id,则返回新插入id值,否则返回true or false;
//支持批量插入
$data[0]['username']='root';
$data[0]['password']='111';
$data[0]['email']=xx@gmail.com';
$data[1]['username']='root';
$data[1]['password']='111';
$data[1]['email']=xx@gmail.com';
$rs=$db->save($data);
if($rs){
echo '成功';
}else{
echo '失败';
}
更新数据库:
<?php
$data['id']=1;
$data['username']=2;
$db->update($data);
//自动查询表结构,并缓存表结构结果,默认条件语句是表的主键,如果id是表的主键,那么本次sql为:
//update `m_user` set `username`='2' where `id`=1;
删除数据库:
<?php
$data['id']=1;
$db->where($data)->delete();
//或者
$db->delete($data);
//支持批量删除
$id=array(1,2,3,4,5);
$db->delete(array('id'=>$id));
接下来还有一些关于字段验证的方法:
需要新建一个model去继承这个model
格式如下:
array(
* @param string field 验证字段
* @param string rule 验证规则
* @param string error 错误提示
* @param mix [addition] 附加规则
* @param int [condition] 验证条件: 0:存在就验证EXISTS_VAILIDATE(默认) 1:必须验证:EXISTS_VAILIDATE
* @param int [operation] 验证条件2: 0:插入数据库时验证:MODEL_INSERT 1:更新数据库时验证:MODEL_UPDATE 2:插入数据库和更新数据库都验证:MODEL_BOTH
* )
例子如下:
<?php
class UserModel extends Model{
protected $_validate=array(
array('username','unique','用户名必须唯一'),
array('username','length','用户名必须在2到12个字符之间',array(2,12)),
array('email','email','email格式不正确'),
);
}
等等,并且还支持自定义函数验证
调用例子如下:
<?php
//假设db就是model实例化的对象
if(!$db->create()){
echo $db->getError();
};
当不通过验证时,就输出.
并且还支持字段完整功能.如下:
<?php
class UserModel extends Model{
protected $_aotu=array(
array('time','function','time'),
array('hidden','const','1'),
array('date','function',array('date','Y-m-j'))
);
}
这些分别表示:
time字段在新增时默认为time()函数的值
hidden字段在新增时默认为1
date字段在新增时默认为当前时间;格式为 Y-m-j