MarkJane
4/12/2017 - 7:05 AM
CI框架 自定义数据表模型 表名可通过构造函数传递
CI框架 自定义数据表模型 表名可通过构造函数传递
<?php defined('BASEPATH') OR exit('No direct script access allowed');
/**
* +-----------------------------------
* 自定义数据表模型 表名可通过构造函数传递
* +-----------------------------------
*
*/
class CI_Custom_table_model {
/**
* Reference to CodeIgniter instance
*
* @var object
*/
protected $CI;
private $table_name = NULL;
private $master_db = NULL;
function __construct($init = array())
{
// Assign the CodeIgniter super-object
$this->CI =& get_instance();
if (isset($init['table_name']) && !empty($init['table_name']))
{
$this->table_name = $init['table_name'];
}
}
/**
* 获取单条数据中某字段值
* @param array $where where条件
* @param string $table_name 表名
* @param string $field 返回字段
* @return array
*/
public function c_get_record_field($where = array(), $table_name = '', $field = '')
{
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
$return = NULL;
if (!empty($this->table_name) && !empty($where) && !empty($field))
{
$this->CI->db->select($field)->from($this->table_name);
$this->_where_sql_builder($where);
$result = $this->CI->db->get()->row_array();
if (isset($result[$field]))
{
$return = $result[$field];
}
}
return $return;
}
/**
* 获取单条数据
* @param array $where where条件
* @param string $table_name 表名
* @param string $field select字段
* @return array
*/
public function c_get_record($where = array(), $table_name = '', $field = '*')
{
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
$return = NULL;
if (!empty($this->table_name) && !empty($where))
{
$this->CI->db->select($field)->from($this->table_name);
$this->_where_sql_builder($where);
$return = $this->CI->db->get()->row_array();
}
return $return;
}
/**
* 获取数据列表
* @param array $where where条件
* @param string $table_name 表名
* @param string $field select字段
* @param string $get 获取list:列表 count:条数
* @param int $page 分页用 当前第几页
* @param int $limit 分页用 获取多少数据
* @param string $order_by 排序字段
* @param string $direction 排序方向 DESC ASC RANDOW
* @return array
*/
public function c_get_record_list($where = array(), $table_name = '', $field = '*', $get = 'list', $page = '', $limit = '', $order_by = '', $direction = 'ASC')
{
$return = $get == "list" ? array() : 0;
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
if (!empty($this->table_name))
{
$this->CI->db->select($field)->from($this->table_name);
$this->_where_sql_builder($where);
if ($get == 'list')
{
if (!empty($page) && !empty($limit))
{
$_orderby = isset($order_by) ? $order_by : "{$this->table_name}_id";
$_offset = ($page - 1) * $limit;
$this->CI->db->order_by($_orderby, $direction);
$this->CI->db->limit($limit, $_offset);
}
$return = $this->CI->db->get()->result_array();
}
else
{
$return = $this->CI->db->count_all_results();
}
}
return $return;
}
/**
* WHERE条件SQL BUILD
* @param array $where 字段对值的数组
* 兼容特殊的比较符，写法为字段后加 _XXX， 如 $where['name_NOT'] = 'php'
* _NOT 视值是否为数组 等同 name != 'x' 和 name not in ('x')
* _LKE 等同 name like 'x'
* _NKE 等同 name not like 'x'
* _MAX 等同 name <= 'x'
* _MIN 等同 name >= 'x'
* _LTN 等同 name < 'x'
* _GTN 等同 name > 'x'
*/
public function _where_sql_builder($where, $master_db = NULL)
{
foreach($where as $key => $val)
{
$mmflg = NULL;
$modkey = NULL;
$strlen = strlen($key);
if ($strlen > 4){
$mmflg = strtoupper(substr($key, $strlen - 4));
$modkey = substr($key, 0, $strlen - 4);
}
if ($mmflg === '_NOT')
{
// != 和 not in 比较符\
$option[] = is_array($val) ? "{$modkey} NOT IN ('" . implode("','", $val) . "')" : "{$modkey} != '{$val}'";
}
elseif ($mmflg === '_LKE')
{
// like 比较符
$option[] = "{$modkey} LIKE '{$val}'";
}
elseif ($mmflg === '_NKE')
{
// not like 比较符
$option[] = "{$modkey} NOT LIKE '{$val}'";
}
elseif ($mmflg === '_MAX')
{
// <= 比较符
$option[] = "{$modkey} <= '{$val}'";
}
elseif ($mmflg === '_MIN')
{
// >= 比较符
$option[] = "{$modkey} >= '{$val}'";
}
elseif ($mmflg === '_LTN')
{
// < 比较符
$option[] = "{$modkey} < '{$val}'";
}
elseif ($mmflg === '_GTN')
{
// > 比较符
$option[] = "{$modkey} > '{$val}'";
}
else
{
// 常见
$option[] = is_array($val) ? "{$key} IN ('" . implode("','", $val) . "')" : "{$key} = '{$val}'";
}
}
if (count($option) > 0)
{
$where = implode(' AND ', $option);
if (!empty($master_db))
{
$master_db->where($where);
}
else
{
$this->CI->db->where($where);
}
}
}
/**
* 从ID获取该条记录
* @param int $id 主键ID
* @param string $table_name 表名
* @param string $field select字段
* @return array
*/
public function c_get_record_by_id($id, $table_name = '', $field = "*", $primary = '')
{
$return = NULL;
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
if (!empty($id) && !empty($this->table_name))
{
$primary_key = !empty($primary) ? $primary : "{$this->table_name}_id";
$return = $this->CI->db->select($field)->from($this->table_name)->where($primary_key, $id)->get()->row_array();
}
return $return;
}
/**
* 添加一条记录
* @param array $data 记录详细值
* @param string $table_name 表名
* @return boolean 是否成功
*/
public function c_add_record($data = array(), $table_name = '')
{
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
$return = FALSE;
if (!empty($this->table_name) && !empty($data))
{
//切换主库更新插入操作
$this->master_db = $this->CI->load->database('master', TRUE);
$this->master_db->insert($this->table_name, $data);
$return = $this->master_db->insert_id();
$this->master_db->close();
}
return $return;
}
/**
* 添加多条记录
* @param array $data 记录详细值数组
* @param string $table_name 表名
* @return boolean 是否成功
*/
public function c_batch_add_record($data = array(), $table_name = '')
{
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
$return = FALSE;
if (!empty($this->table_name) && !empty($data))
{
//切换主库更新插入操作
$this->master_db = $this->CI->load->database('master', TRUE);
$this->master_db->insert_batch($this->table_name, $data);
$return = $this->master_db->affected_rows();
$this->master_db->close();
}
return $return;
}
/**
* 更新一条记录
* @param int $id 主键ID
* @param array $data 记录详细值
* @param string $table_name 表名
* @return boolean 是否成功
*/
public function c_update_record($id, $data = array(), $table_name = '', $primary = '')
{
$return = FALSE;
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
if (!empty($id) && !empty($data) && !empty($this->table_name))
{
//切换主库更新操作
$primary_key = !empty($primary) ? $primary : "{$this->table_name}_id";
$this->master_db = $this->CI->load->database('master', TRUE);
$this->master_db->where($primary_key, $id);
$return = $this->master_db->update($this->table_name, $data);
$this->master_db->close();
}
return $return;
}
/**
* 通过WHERE条件更新记录
*/
public function c_update_record_by_where($where = array(), $data = array(), $table_name = '')
{
$return = FALSE;
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
if (!empty($data) && !empty($data) && !empty($this->table_name))
{
//切换主库更新操作
$this->master_db = $this->CI->load->database('master', TRUE);
$this->_where_sql_builder($where, $this->master_db);
$return = $this->master_db->update($this->table_name, $data);
$this->master_db->close();
}
return $return;
}
/**
* 删除一条记录(物理删除)
* @param int $id 主键ID
* @param string $table_name 表名
* @return boolean 是否成功
*/
public function c_delete_record($id, $table_name = '', $primary = '')
{
$return = FALSE;
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
if (!empty($id) && !empty($this->table_name))
{
//切换主库更新操作
$primary_key = !empty($primary) ? $primary : "{$this->table_name}_id";
$this->master_db = $this->CI->load->database('master', TRUE);
if (is_array($id))
{
$this->master_db->where_in($primary_key, $id);
}
else
{
$this->master_db->where($primary_key, $id);
}
$return = $this->master_db->delete($this->table_name);
$this->master_db->close();
}
return $return;
}
/**
* 删除一条记录(软删除)
* @param int $id 主键ID
* @param string $table_name 表名
* @return boolean 是否成功
*/
public function c_soft_delete_record($id, $table_name = '', $soft_filed = 'delete_flg', $primary = '')
{
$return = FALSE;
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
if (!empty($id) && !empty($this->table_name))
{
//切换主库更新操作
$primary_key = !empty($primary) ? $primary : "{$this->table_name}_id";
$this->master_db = $this->CI->load->database('master', TRUE);
if (is_array($id))
{
$this->master_db->where_in($primary_key, $id);
}
else
{
$this->master_db->where($primary_key, $id);
}
$_data = array($soft_filed => 1);
$return = $this->master_db->update($this->table_name, $_data);
$this->master_db->close();
}
return $return;
}
/**
* 删除记录(软删除)
* @param int $id 主键ID
* @param string $table_name 表名
* @param string $soft_filed标识软删除的字段 默认delete_flg
* @return boolean 是否成功
*/
public function c_batch_soft_delete_record($where, $table_name = '', $soft_filed = 'delete_flg', $primary = '')
{
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
$return = FALSE;
if (!empty($where) && !empty($this->table_name))
{
//切换主库更新操作
$this->master_db = $this->CI->load->database('master', TRUE);
$this->master_db->from($this->table_name);
$this->_where_sql_builder($where, $this->master_db);
$_data = array($soft_filed => 1);
$return = $this->master_db->update($this->table_name, $_data);
$this->master_db->close();
}
return $return;
}
/**
* 删除记录(物理删除)
* @param int $id 主键ID
* @param string $table_name 表名
* @return boolean 是否成功
*/
public function c_batch_delete_record($where, $table_name = '')
{
$return = FALSE;
$this->table_name = !empty($table_name) ? $table_name : $this->table_name;
if (!empty($where) && !empty($this->table_name))
{
$this->master_db = $this->CI->load->database('master', TRUE);
$this->master_db->from($this->table_name);
$this->_where_sql_builder($where, $this->master_db);
$return = $this->master_db->delete($this->table_name);
$this->master_db->close();
}
return $return;
}
}