在框架中，经常看到对数据库的操作进行链式操作，我们综合前面两个类，来实现一下这种效果。
老规矩，首先构造好我们的类
class model extends sp_obj {
//表名
public $tab;
//存放where的字符串
private $__where;
//存放第二个参数
private $_arg;
//表示where是否已经进行过拼接
private $_begin;
//字段
private $_fields;
//排序段
private $_orderby;
//限制段
private $_limit;
//分组段
private $_groupby;
//错误信息
private $error;
//调试开关
private $_debug;
//预处理方法参数容器
private $_store;
private $cacheKey;
private $cacheTime;
function __construct($tab) {
$this->tab = $tab;
$f = DB::M($tab);
if (file_exists($f)) {
$this->config = include $f;
} else {
throw new Exception($f . "不存在", 1);
}
$this->resetStatus();
// $this->_store['_where'] = [];
}
}
接下来就是定义一些功能方法了，过滤字段值的model::qf(),fqute():
function qf($v) {
if ($v == '*' || stripos($v, ',') !== false) {
return $v;
}
if (stripos($v, ':') === 0) {
return trim($v, ':');
} else {
return '`' . trim($this->filterField($v)) . '`';
}
}
function fqute($arr) {
return implode(',', array_map(function ($v) {
if (stripos($v, '`') === 0) {
return trim($v, '`');
}
return $this->qf($v);
}, $arr));
}
在过滤这段，可以借助我们在前面提到的生成的模型文件，其实就是相当于一个配置文件，利用它我们可以提前对字段和值进行检测，手动修改该文件的内容还可以自定义一些实用功能.
function filterField($f) {
if (isset($this->config['setting'][$f])) {
return $f;
} else {
throw new fieldNotExitsException($f . "不是一个合法的字段");
return false;
}
}
/**
* 验证数据
* @param [type] $f [description]
* @param [type] $v [description]
* @return [type] [description]
*/
function filterVar($f, $v, $flag = 0) {
if (is_array($v) || is_object($v)) {
$v = json_encode($v);
}
if (stripos($v, '`') === 0) {
return trim($v, '`');
}
$ini = $this->config['setting'][$f];
if ($ini['min'] && strlen($v) < $ini['min']) {
throw new valueErrorException($ini['lang'] . "不得少于{$ini['min']}字符");
return '';
}
if (isset($ini['max']) && strlen($v) > $ini['max']) {
throw new valueErrorException($ini['lang'] . "不得多于{$ini['max']}字符");
return '';
}
//在这里我们可以对数据的值进行处理，使得只有符合我们定义条件 的值才能传入。
return $v;
}
定义一个重置方法model::resetStatus()，将类中的属性重置。
function resetStatus() {
$this->cacheKey = "";
$this->cacheTime = 0;
$this->__where = [];
$this->_arg = [];
$this->_begin = 1;
$this->_fields = [];
$this->_orderby = "";
$this->_limit = "";
$this->_groupby = "";
$this->_debug = false;
$this->_store['_where'] = [];
}
这个方法将类中的所有属性全部重置，这样可以为一次全新的查询作准备。注意这里的model::_store变量，它在这个类中有着很重要的作用。先构造两个对它进行操作的方法model::store()和model::dostore()。
function store($medth, $arg) {
array_push($this->_store[$medth], $arg);
return $this;
}
function doStore($medth) {
foreach ($this->_store[$medth] as $arg) {
call_user_func_array([$this, $medth], $arg);
}
return $this;
}
很明显，用了自身的方法名作为键名，而参数数组为值，等下我们将看到哪些方法会放在这里面并且它们的用途是什么。
现在可以来看下一个核心方法_sql()， 这个方法将根据类中收集的各个信息进行sql语句的构造。
function _sql($t = "select", $data = "", $replace = 0) {
if ($t == "select") {
//构造select语句，_field()方法返回字段部分，getwhere()返回条件部分_groupby属性保存的就是groupby段的sql,_orderby对应orderby, _limit对应limit
$sql = "SELECT " . $this->_field() . " FROM {$this->tab} " . $this->getwhere() . $this->_groupby . $this->_orderby . $this->_limit;
} elseif ($t == "update") {
//修改，修改的数据在$data中，我们需要对其进行拆盒组装成字符串，
if (is_array($data)) {
///传递的是数组
$strs = trim(array_reduce(array_map(function ($k, $v) {
return $this->qf($this->filterField($k)) . "=" . $this->filterVar($k, $v, 1);
}, array_keys($data), $data), function (&$strs, $v) {
return $strs . "," . $v;
}), ',');
} elseif (is_string($data)) {
$strs = $data;
} elseif (is_callable($data)) {
return $this->edit($data());
}
$this->doStore("_where");
$sql = "UPDATE {$this->tab} SET $strs " . $this->getwhere() . $this->_limit;
} elseif ($t == "insert") {
if (is_array($data) || is_object($data)) {
$fieds = "";
$values = "";
foreach ($data as $key => $value) {
$fieds .= $this->qf($this->filterField($key)) . ',';
$values .= ",'" . $this->filterVar($key, $value) . "'";
}
$fieds = trim($fieds, ',');
$values = trim($values, ',');
$cmd = $replace ? 'REPLACE INTO' : ' INSERT INTO ';
$sql = $cmd . " {$this->tab} ({$fieds}) values ({$values}) ";
return $sql;
} elseif (is_callable($data)) {
return $this->insert($data());
}
} elseif ($t == 'delete') {
$this->doStore('_where');
$sql = "DELETE FROM {$this->tab} " . $this->getwhere() . $this->_orderby . $this->_limit;
}
if ($this->_debug) {
echo $sql;
}
return $sql;
}
首先是select类型构造，主要是_field()方法会返回相应的字段组合，这个在后面一看就懂了，接下来的是update,你只可以直接传入相应的语句，如"f1='abc',f2='bcd'",或者传入数组['f1'=>'abc', 'f2'=>'bcd']会自动构造成sql语句。insert类型则可以传入数组或对象，最后一种则是delete类型，在这个函数里面我们看到了doStore('_where')，我们前面说过这个是个方法，从这里不难看出，它的功能应该是结合getwhere()方法来构造sql语句的where部分。
function _where($f, $v = null, $op = '=', $relation = 'AND') {
//第一个参数为数组，将依次对键值进行递归
//第一个参数为字段，第二个参数为值，当字段为级数组时，会将其依次分解，当值为数组，如果元素大于2会自动构造成in类型,你也可以传入一个函数 ，再将第二个参数置为false进行另一个条件组合
if (is_array($f)) {
foreach ($f as $key => $value) {
$this->_where($key, $value, $op, $relation);
}
return $this;
}
if ($this->_begin) {
$relation = "";
$this->_begin = 0;
}
if (is_null($v)) {
array_push($this->__where, "{$relation} {$f}");
return $this;
} elseif ($v === true) {
array_push($this->__where, "{$relation} {$f}");
return $this;
}
if (is_array($v) and $op == '=') {
if (count($v) < 2) {
$v = $v[0];
} else {
$op = "in";
}
}
if (is_string($f) && is_callable($f)) {
if (!$this->_begin) {
array_push($this->__where, "{$relation} (");
}
$this->_begin = 1;
$f($this);
if (!$this->_begin) {
array_push($this->__where, ')');
}
return $this;
}
if (is_null($v)) {
return $this;
}
switch ($op) {
case '=':
case '>':
case '<':
case '>=':
case '<=':
case '<>':
array_push($this->__where, sprintf("%s %s %s '%s' ", $relation, $this->qf($f), $op, $v));
break;
case 'in':
case 'notin':
$op = $op == 'notin' ? "NOT IN" : "IN";
if (is_array($v)) {
array_push($this->__where, sprintf("%s %s %s (%s)", $relation, $this->qf($f), $op, implode(',', array_map(function ($a) {
return "'{$a}'";
}, $v))));
} elseif (is_string($v)) {
array_push($this->__where, sprintf("%s %s %s (%s)", $relation, $this->qf($f), $op, $v));
}
break;
case 'like':
array_push($this->__where, sprintf("%s %s %s '%s' ", $relation, $this->qf($f), $op, str_replace("**", '%', $v)));
break;
default:
break;
}
return $this;
}
构造需要查询的字段语句
public function select() {
$this->_fields = array_merge($this->_fields, array_map(function($k){
if (is_array($k)){
return implode(',', $k);
}
return $k;
}, func_get_args()));
return $this;
}
构造where语句
public function where($f, $v = null, $op = '=') {
return $this->store("_where", [$f, $v, $op, 'AND']);
}
public function whereAnd($f, $v = null, $op = '=') {
return $this->store("_where", [$f, $v, $op, 'AND']);
}
public function whereOr($f, $v = null, $op = '=') {
return $this->store("_where", [$f, $v, $op, 'OR']);
构造limit ,groupby orderby语句
//将内容存在model::_limit中待拼接
public function limit($b, $size = 0) {
$b = intval($b);
if ($size == 0) {
$this->_limit = " LIMIT {$b} ";
} else {
$size = intval($size);
$this->_limit = " LIMIT {$b}, {$size} ";
}
return $this;
}
//除了分开传值，也可以 orderBy('field asc')的方式，这样第二个参数 会自动失效。
function orderBy($field, $type = "ASC") {
if (empty($field)) {
return $this;
}
if (stripos($field, " ") !== false) {
list($field, $type) = explode(' ', $field);
}
if (empty($this->_orderby)) {
$this->_orderby = " ORDER BY {$field} {$type} ";
} else {
$this->_orderby .= ", {$field} {$type} ";
}
return $this;
}
//传入字段并将其传入model::_groupby中待拼接
function groupBy($field) {
$this->_groupby = " GROUP BY {$field} ";
return $this;
}
获取where语句
function getwhere($type = 1) {
$this->wheretype = $type;
if (empty($this->__where)) {
$this->dostore('_where');
}
if ($this->wheretype !== 1) {
$this->wheretype = 1;
}
return empty($this->__where) ? '' : "WHERE " . implode(' ', $this->__where);
}
接下来就是把简单的curd操作暴露出去使用了
//这是最终操作端了，参数会给_sql()方法，用来构造出相应的sql语句，再交给DBmysqli处理DB只是一个中介
function insert($data, $return_insert_id = false, $replace = false, $silent = false) {
$sql = $this->sql('insert', $data, $replace);
if (!$sql) {
$this->resetStatus();
return false;
}
$res = DB::queryInsert($sql, false, $return_insert_id, $silent);
$this->resetStatus();
return $res;
}
function edit($data, $effect = 0, $slient = 0) {
$sql = $this->sql('update', $data);
if (!$sql) {
$this->resetStatus();
return false;
}
$res = DB::queryUpdate($sql, $this->_arg, $effect, $slient);
$this->resetStatus();
return $res;
}
public function del() {
$sql = $this->_sql("delete");
if (!$sql) {
$this->resetStatus();
return false;
}
$res = DB::queryUpdate($sql, false, 1);
return $res;
}
//获取一条数据，结果信中的第一条
public function one() {
$this->limit(0,1);
$sql = $this->sql();
if (!$sql) {
$this->resetStatus();
return false;
}
$res = DB::fetchOne($sql, false);
$this->resetStatus();
return $res;
}
public function all() {
$sql = $this->sql();
if (!$sql) {
$this->resetStatus();
return false;
}
$res = DB::fetchAll($sql, false);
$this->resetStatus();
return $res;
}
public function iter() {
$sql = $this->sql();
if (!$sql) {
$this->resetStatus();
return false;
}
$res = DB::iter($sql, false);
$this->resetStatus();
return $res;
}
public function getsvar(){
$sql = $this->sql();
echo $sql;
if (!$sql) {
$this->resetStatus();
return false;
}
$res = DB::fetchVar($sql, false);
$this->resetStatus();
return $res;
}
public function getscol($field) {
$res = $this->iter();
$col = array();
foreach($res as $key => $value) {
$col[$key] = isset($value->$field) ? $value->$field : null;
}
return $col;
}
public function getsall() {
return $this->all();
}
public function getsone() {
return $this->one();
}
public function getscount($f = "*") {
return $this->select("`count($f) as num`")->getsvar();
}
public function fields() {
return call_user_func_array([$this, "select"], func_get_args());
}
public function F() {
return call_user_func_array([$this, "select"], func_get_args());
}
public function rand($num) {
$sql = "SELECT MAX({$this->pri}) as m, MIN($this->pri) as n FROM {$this->tab} ";
$res = DB::fetchOne($sql);
$id = mt_rand($res->n, $res->m - $num > $res->n ? $res->m - $num :$res->m);
$this->where($this->pri, $id, '>');
$this->where($this->pri, $id + $num *2, '<');
$this->limit(0, $num);
$data = $this->all();
return $data;
}
function get_page($page_now = 1, $page_size = 10, $url, $lastid = 0) {
$addwhere = '';
$t = is_array($this->table) ? implode(',', $this->table) : $this->table;
$where = $this->getwhere(3);
$sql = sprintf("SELECT COUNT(%s) FROM %s %s ", !empty($this->_groupby) && $this->groupby ? "DISTICT $this->_groupby" : "*", $this->tab, $where);
$num = DB::fetchVar($sql);
//echo $num, $sql;die;
$page_num = max(1, ceil($num / $page_size));
$page_now = max(1, $page_now);
$b = max(0, ($page_now - 1) * $page_size);
if (!$lastid) {
$sql = "SELECT {$this->pri} AS ID FROM {$this->tab} " . $where . " {$this->_groupby} {$this->_orderby} limit $b,$page_size";
} else {
if (stripos($this->_orderby, "desc") !== false) {
$addwhere = empty($where) ? "where `{$this->pri}`>'{$lastid}' " : " and `{$this->pri}'<'{$lastid}'";
} else {
$addwhere = empty($where) ? "where `{$this->pri}`>'{$lastid}' " : "and `{$this->pri}`>'{$lastid}'";
}
$sql = "SELECT {$this->pri} AS ID FROM {$this->tab} " . $where . $addwhere . " {$this->_groupby} {$this->orderby} limit 0,$page_size";
}
//echo $sql;die;
$res = DB::fetchAll($sql);
$col = array();
foreach($res as $key => $value) {
$col[$key] = $value->ID;
}
if (empty($this->__where)) {
$this->_begin = 1;
}
$this->_where($this->pri, $col);
$sql = $this->sql();
$list = DB::fetchAll($sql, false);
$pagestr = '';
$data = array("list" => $list, "pagelist" => $pagestr, "num" => $page_num, 'count' => $num, "pagenow" => $page_now);
$this->resetStatus();
return $data;
}
在这里，有用到一个分页的函数，可以自行设计符合需求的
网友评论