Codeigniter4通用自定义数据库操作类

于 2020-04-07 17:24:16 发布
阅读量1.4k 收藏 6
点赞数 1
CC 4.0 BY-SA版权
分类专栏： 数据库操作类 codeigniter4 php
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_33157758/article/details/105368404

 

CI4框架封装了一些方法，方便对数据库的操作和使用

  1. 单条/多条记录:获取、更新、插入、假删除、记录操作log 等
  2. 自动更新时间戳、排序更改、分页获取、树状获取、直接执行sql等
  3. 假定了数据库有指定的字段（如更新时间等），有日志表(消耗很大)，内容附后
  4. 基本用法是每个需要用到操作数据库的model文件，都继承此文件，并在实例化后调用方法就可以了。参数基本可以做到望文生义，可以自己琢磨,不懂的留言
  5. 用法：控制器+模型+自定义数据库操作类(重点)+数据表标准(重点)
  6. 控制器
  7. <?php
//控制器
namespace App\Controllers\MyTestController;
use App\Models\Role_model;
class MyTestController extends Api_apps_logged {
	function __construct() {
		$this->model_name = 'Jingkaiqu\Investigate_record_model';
		parent::__construct();
	}
function myTestFunction (){
			$this->Role_model= new Role_model();
			$record = $this->Role_model->db_row($table_name = '', $id = 1, $where = array(), $order_by = null, $sort = 'desc', $include_delete = false);
    }
		
}
?>
  8.  模型
  9. <?php   
//模型
namespace App\Models;
use CodeIgniter\Model;

class Role_model extends Base_model {//继承自定义数据库操作类
	public function __construct() {
		$this->table_name = 'role';
		parent::__construct();
	}
}
?>

     

  10. 自定义数据库操作类
  11. <?php

namespace App\Models;
use CodeIgniter\Model;
//use \Symfony\Component\VarDumper\Test\VarDumperTestTrait;

class Base_model extends Model{
	/* 此类中操作数据库的方法
	  1：如无特殊情况，获取数据时不获取有deleted_at时间的数据
	  2：其中的更新db_update_row 和 db_update_batch方法,使用时会尝试更新记录中的updated_at字段
	 * 3：推荐对应每张表新建一个模型文件
	 *  */

	function __construct() {
		parent::__construct();
	}

	public function setDatabase($databaseName) {
		$this->db = \Config\Database::connect($databaseName);
	}

	/* 优先使用传入的表名。如没有则使用model文件中定义的默认表名
	 * 推荐对应每张表新建一个模型文件
	 */

	public function default_table_name($table_name = '') {
		$table_name = trim($table_name);
		$table_name = !empty($table_name) ? $table_name : $this->table_name;
		return $table_name;
	}

	/* 获取数量
	 * @return db_count
	 */

	public function db_count($table_name = '', $where = array()) {
		$table_name = $table_name ? $table_name : $this->default_table_name($table_name);
		if (empty($table_name)) {
			return false;
		}
		$this->dbSource = $this->db->table($table_name);
		$this->dbSource->select('COUNT(*) AS num');
		if (!isset($where['deleted_at']) || strtolower($where['deleted_at']) == 'null') {
			$this->dbSource->where('deleted_at', null);
		} else {
			unset($where['deleted_at']);
		}
		$this->dbSource->where('display', 1);
		if (!empty($where)) {
			$this->dbSource->where($where);
		}
		$query = $this->dbSource->get();
		if ($query->resultID->num_rows > 0) {
			$getRowArray = $query->getRowArray();
			return $getRowArray['num'];
		} else {
			return null;
		}
	}
	//返回单条记录
	public function db_row($table_name = '', $id = '', $where = array(), $order_by = null, $sort = 'desc', $include_delete = false, $select = '*', $include_display = false, $where_in = array()) {
		$table_name = $table_name ? $table_name : $this->default_table_name($table_name);
		if (empty($table_name)) {
			return false;
		}
		$this->dbSource = $this->db->table($table_name);
		$this->dbSource->select($select);
		if ($id != '') {
			$this->dbSource->where('id', intval($id));
		}
		if (!$include_delete) {
			$this->dbSource->where('deleted_at', null);
		}
		if (!$include_display) {
			$this->dbSource->where('display', 1);
		}
		if (!empty($where)) {
			$this->dbSource->where($where);
		}
		if (!empty($order_by)) {
			$this->dbSource->orderBy($order_by, $sort);
		}
		if (!empty($where_in)) {
			foreach ($where_in as $field => $where_in_params) {
				$whereInFliter = array();
				if (!empty($where_in_params)) {
					foreach ($where_in_params as $w_value) {
						if (!empty($w_value)) {
							$whereInFliter[] = $w_value;
						}
					}
				}
				if (!empty($whereInFliter)) {
					$this->dbSource->whereIn($field, $whereInFliter);
				}
			}
		}
		$query = $this->dbSource->get();

		if ($query->resultID->num_rows > 0) {
			return $query->getRowArray();
		} else {
			return null;
		}
	}
	//必须选定select返回单条记录
	public function db_row_colum($select, $table_name = '', $id = '', $where = array(), $order_by = null, $sort = 'desc', $include_delete = false) {
		$table_name = $table_name ? $table_name : $this->default_table_name($table_name);
		if (empty($table_name)) {
			return false;
		}
		$this->dbSource = $this->db->table($table_name);
		$this->dbSource->select($select);
		if (intval($id)) {
			$this->dbSource->where('id', intval($id));
		}
		if (!$include_delete) {
			$this->dbSource->where('deleted_at', null);
		}
		$this->dbSource->where('display', 1);
		if (!empty($where)) {
			$this->dbSource->where($where);
		}
		if (!empty($order_by)) {
			$this->dbSource->orderBy($order_by, $sort);
		}
		$query = $this->dbSource->get();
		if ($query->resultID->num_rows > 0) {
			return $query->getRowArray();
		} else {
			return null;
		}
	}

	/* 执行sql字符串
	 * $return  [rowArray,getResultArray]对应返回单条/多条
	 */

	public function db_query_sql($sql, $return = 'getResultArray') {
		$query = $this->db->query($sql);
		if ($return === 'bool') {
			if($query->resultID !== true && $query->resultID !== false && $query->resultID !== null && isset($query->resultID->num_rows) ){
				if($query->resultID->num_rows > 0){
					$return = true;
				}else{
					$return = false;
				}
			}else{
				$return = $query->resultID;
			}
			return $return;
		} else {
			if ($query) {
				switch ($return) {
					case 'getResultArray':
						$return = $query->getResultArray();
						break;
					case 'rowArray':
						$return = $query->getRowArray();
						break;
					default:
						return false;
				}
				return $return;
			} else {
				return null;
			}
		}
	}

	/* 获取多条记录,可指定分页
	 * $page = null, $num = null 传入指定参数设置分页
	 * @return multiple array
	 * $where_in = array();
	 * $where_in['id'] = explode(',', $announcement['area_group_ids']);
	 */

	public function db_array($table_name = '', $page = null, $num = null, $where = array(), $where_in = array(), $like = array(), $or_like = array(), $timeRange = array(), $order_by = null, $sort = 'desc', $or_where = array()) {
		$table_name = $table_name ? $table_name : $this->default_table_name($table_name);
		if (empty($table_name)) {
			return false;
		}
		$this->dbSource = $this->db->table($table_name);
		$this->dbSource->select('*');
		if (!empty($where)) {
			$this->dbSource->where($where);
		}
		if (!empty($where_in)) {
			foreach ($where_in as $field => $where_in_params) {
				$whereInFliter = array();
				if (!empty($where_in_params)) {
					foreach ($where_in_params as $w_value) {
						if (!empty($w_value)) {
							$whereInFliter[] = $w_value;
						}
					}
				}
				if (!empty($whereInFliter)) {
					$this->dbSource->whereIn($field, $whereInFliter);
				}
			}
		}
		if (!empty($or_like)) {
			$this->dbSource->groupStart();
			$this->dbSource->orLike($or_like);
			$this->dbSource->groupEnd();
		}
		if (!empty($order_by) && !empty($sort)) {
			$this->dbSource->orderBy($order_by, $sort);
		}
		if (!empty($or_where)) {
			$this->dbSource->groupStart();
			foreach ($or_where as $o_key => $o_value) {
				if (is_array($o_value) && count($o_value) == 3 && isset($o_value['field']) && isset($o_value['value']) && isset($o_value['escape'])) {
					$this->dbSource->orWhere($o_value['field'], $o_value['value'], $o_value['escape']);
				}
			}
			$this->dbSource->groupEnd();
		}
		if (!empty($timeRange)) {
			if (isset($timeRange['timeRangeStyle'])) {
				$timeRangeStyle = intval($timeRange['timeRangeStyle']);
				if ($timeRangeStyle === 1) {
					if (isset($timeRange['start']) && $timeRange['start']) {
						$this->dbSource->where('created_at >=', $timeRange['start']);
					}
					if (isset($timeRange['end']) && $timeRange['end']) {
						$this->dbSource->where('created_at <=', $timeRange['end']);
					}
				} else if ($timeRangeStyle === 2) {
					$this->dbSource->where('created_at IS NULL');
				}
			}
		}
		$this->dbSource->where('deleted_at', null);
		$this->dbSource->where('display', 1);

		if ($page != null && $num != null) {
			$offset = ($page - 1) * $num;
			$this->dbSource->limit($num, $offset);
		}
		$query = $this->dbSource->get();
		if ($query->resultID->num_rows > 0) {
			$return = $query->getResultArray();
			return $return;
		} else {
			return null;
		}
	}

	/* 按条件获取表内数据（无论有无deleted_at数据）
	 * 返回数组
	 *  @return multiple array
	 */

	public function db_all_include_deleted($table_name, $where, $or_like, $timeRange, $order_by, $sort) {
		$order_by = $order_by === null ? 'created_at' : $order_by;
		$sort = $sort === null ? 'DESC' : $sort;
		$table_name = $table_name ? $table_name : $this->default_table_name($table_name);
		if (empty($table_name)) {
			return false;
		}
		$this->dbSource = $this->db->table($table_name);
		/* 获取数据 */
		$this->dbSource->select('*');
		if (!empty($where)) {
			$this->dbSource->where($where);
		}
		if (!empty($or_like)) {
			$this->dbSource->groupStart();
			$this->dbSource->orLike($or_like);
			$this->dbSource->groupEnd();
		}

		if (!empty($timeRange)) {
			if (isset($timeRange['timeRangeStyle'])) {
				$timeRangeStyle = intval($timeRange['timeRangeStyle']);
				if ($timeRangeStyle === 1) {
					if (isset($timeRange['start']) && $timeRange['start']) {
						$this->dbSource->where('created_at >=', $timeRange['start']);
					}
					if (isset($timeRange['end']) && $timeRange['end']) {
						$this->dbSource->where('created_at <=', $timeRange['end']);
					}
				} else if ($timeRangeStyle === 2) {
					$this->dbSource->where('created_at IS NULL');
				}
			}
		}
		$this->dbSource->orderBy($order_by, $sort);
		$query = $this->dbSource->get();
		if ($query->resultID->num_rows > 0) {
			$result = $query->getResultArray();
			return $result;
		} else {
			return null;
		}
	}

	/* 按条件获取表内数据（不包含deleted_at时间数据）
	 * 返回数组
	 *  @return multiple array
	 */

	public function db_all_data($table_name = '', $where = array(), $where_in = array(), $or_like = array(), $timeRange = array(), $order_by = 'sort', $sort = 'desc') {
		$order_by = $order_by === null ? 'created_at' : $order_by;
		$sort = $sort === null ? 'DESC' : $sort;
		$table_name = $table_name ? $table_name : $this->default_table_name($table_name);
		if (empty($table_name)) {
			return false;
		}
		$this->dbSource = $this->db->table($table_name);
		/* 获取数据 */
		$this->dbSource->select('*');
		if (!empty($where)) {
			$this->dbSource->where($where);
		}
		if (!empty($or_like)) {
			$this->dbSource->groupStart();
			$this->dbSource->orLike($or_like);
			$this->dbSource->groupEnd();
		}
		if (!empty($where_in)) {
			foreach ($where_in as $field => $where_in_params) {
				$whereInFliter = array();
				if (!empty($where_in_params)) {
					foreach ($where_in_params as $w_value) {
						if (!empty($w_value)) {
							$whereInFliter[] = $w_value;
						}
					}
				}
				if (!empty($whereInFliter)) {
					$this->dbSource->whereIn($field, $whereInFliter);
				}
			}
		}

		if (!empty($timeRange)) {
			if (isset($timeRange['timeRangeStyle'])) {
				$timeRangeStyle = intval($timeRange['timeRangeStyle']);
				if ($timeRangeStyle === 1) {
					if (isset($timeRange['start']) && $timeRange['start']) {
						$this->dbSource->where('created_at >=', $timeRange['start']);
					}
					if (isset($timeRange['end']) && $timeRange['end']) {
						$this->dbSource->where('created_at <=', $timeRange['end']);
					}
				} else if ($timeRangeStyle === 2) {
					$this->dbSource->where('created_at IS NULL');
				}
			}
		}
		$this->dbSource->where('deleted_at', null);
		$this->dbSource->where('display', 1);
		$this->dbSource->orderBy($order_by, $sort);
		$query = $this->dbS
最低0.47元/天 解锁文章

200万优质内容无限畅学
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值