* 1.实现了数据库增删改查 * 2.实现model关联查询 * 3.分页显示，join 多张表链接的复杂查询，自动区分字段（重点） * 4.自动过滤数据库中不存在的字段 * 5.自动维护create_time,update_time的值
/**
* Class DB_Model
* 1.实现了数据库增删改查
* 2.实现model关联查询
* 3.分页显示，join 多张表链接的复杂查询，自动区分字段（重点）
* 4.自动过滤数据库中不存在的字段
* 5.自动维护create_time,update_time的值
* 6.表别名设置
* 7.多表关联，魔法给字段添加表别名
* 8.条件组合查询，自动匹配
*/
class DB_Model extends MY_Model {
protected $db = null;
protected $table_name = null;
protected $alias = '';// 数据表别名
protected $join = [];
public function __construct() {
parent::__construct();
$this->db = $this->init_db();
if(!$this->table_name){
$this->table_name = $this->db->dbprefix(str_ireplace('_model','',strtolower(get_class($this))));
}
if (!$this->db->table_exists($this->table_name)){
show_error($this->table_name." 数据表存在，请检查你的Model命名!",200,"错误提示");
}
}
// 获取一条数据
public function one($where=null,$fields=null) {
if($fields){
$this->db->select($fields);
}
if($where){
$this->db->where($where);
}
$query = $this->db->get($this->table_name);
$row = $query->row_array();
return $row;
}
// 获取多条数据
public function all($where=null,$fields=null,$order_by=null,$limit=null) {
if($fields){
$this->db->select($fields);
}
if($where){
$this->db->where($where);
}
if($order_by){
$this->db->order_by($order_by);
}
if($limit){
$this->db->limit($limit);
}
$query = $this->db->get($this->table_name);
$row = $query->result_array();
return $row;
}
// 分页获取数据
public function page($where=null,$fields=null,$order_by=null){
if(!$fields){
$fields = $this->alias?$this->alias.'.* ':'* ';
}
$this->db->select('SQL_CALC_FOUND_ROWS '.$fields,false);
if($this->alias){
$odr = $this->alias;
$this->db->from($this->table_name.' as '.$this->alias);
}else{
$odr = $this->table_name;
$this->db->from($this->table_name);
}
if($this->join&&is_array($this->join)){
$this->join();
}
if($where){
foreach ($where as $k=>$v) if($v=="")unset($where[$k]);
if($where)$this->db->where($where);
}
if($order_by){
$this->db->order_by($order_by);
}else{
$this->db->order_by($odr.'.id desc');// 没有则按id排序
}
// 分页
$page = $this->input->get('page');
if(!$page)$page = 1;
$page_size = $this->input->get('page_size');
if(!$page_size)$page_size = $this->config->item('page_size');
if(!$page_size)show_error('请在配置文件中配置分页参数，page_size');
$this->db->limit($page_size,($page-1)*$page_size);
$list = $this->db->get()->result_array();
$nums = $this->db->count_all_results($this->table_name,false);
$this->load->library('page');
$page_htm = $this->page->setpage($page,$nums,$page_size);
$info['current_page'] = $page;
$info['page_size'] = $page_size;
$info['nums'] = $nums;
$info['page_htm'] = $page_htm;
$info['list'] = $list;
return $info;
}
// 添加数据
public function add($params) {
if(!$params)return false;
if(!is_array($params))return false;
$params['create_time'] = time();
$params['update_time'] = time();
$fields = $this->db->list_fields($this->table_name);
foreach ($params as $k=>$v){
$m = false;
foreach ($fields as $field) {
if($k==$field){
$m = true;
break;
}
}
if(!$m)unset($params[$k]);
}
$res = $this->db->insert($this->table_name,$params);
if($res){
$id = $this->db->insert_id();
$query = $this->db->query('SELECT * FROM '.$this->table_name.' WHERE id ='.$id);
$row = $query->row_array();
return $row;
}
return $res;
}
// 修改数据
public function edit($where,$params) {
if(!$params)return false;
if(!is_array($params))return false;
$params['update_time'] = time();
$fields = $this->db->list_fields($this->table_name);
foreach ($params as $k=>$v){
$m = false;
foreach ($fields as $field) {
if($k==$field){
$m = true;
break;
}
}
if(!$m)unset($params[$k]);
}
$this->db->set($params);
$this->db->where($where);
$this->db->update($this->table_name);
$line = $this->db->affected_rows();
if($line){
$query = $this->db->get_where($this->table_name,$where);
if($line===1){
$row = $query->row_array();
}else{
$row = $query->result_array();
}
return $row;
}else{
return false;
}
}
// 删除数据
public function del($where) {
if(!$where)return false;
$this->db->delete($this->table_name, $where);
return $this->db->affected_rows();
}
/**
* 获取配置数组字段的值
* @param $arr
* @param $k
* @return mixed
*/
private function get_val($arr,$k){
if(!is_array($arr))show_error('未定义'.$k);
if(!isset($arr[$k]))show_error('未定义'.$k);
return $arr[$k];
}
/**
* 加入第三方表
*/
private function join(){
foreach ($this->join as $k =>$v){
$this->load->model($k);
$a = $this->get_val($v,'alias');// 别名
$this->db->join($this->$k->table_name.' '.$a , $this->get_val($v,'cond'));
// 处理字段（自动加上前缀）
$f = $this->get_val($v,'fields');
if($f){
if(!$a)$a = $this->$k->table_name;
if(is_array($f)){
foreach ($f as $k=>$v){
if(!stripos($f[$k],'.'))$f[$k] = $a.'.'.$f[$k];
}
}else{
$arr = explode(",",$f);
$f = '';
foreach ($arr as $k =>$v){
if(!stripos($arr[$k],'.'))$arr[$k] = $a.'.'.$arr[$k];
if($k==0){
$f.= $arr[$k];
}else{
$f.= ','.$arr[$k];
}
}
}
}else{
$f = $a?$a.'.* ':'* ';
}
$this->db->select($f,false);// 追加join表字段
}
}
}