万能sql标签 · finecms18系列手册

原作者：[coolcheng168](http://www.dayrui.net/home.php?mod=space&uid=2686) chm整合-yufan Q：956932910 个人网站[http://www.phper.kuphp.net/](http://www.phper.kuphp.net/) 论坛推荐[http://bbs.lampym.com/index.php?c=thread&fid=12](http://bbs.lampym.com/index.php?c=thread&fid=12) 程序自带了一个万能List标签，写SQL的extend看起来晦涩难懂，很怪异。我改进了一下，独立了一个SQL标签，有需要的可以拿去。先来一个例子： {sql:rs table="fn_content_1" where="catid=$catid" pagesize="2" urlrule="catid=$catid"} <li><span class="date">{date("Y-m-d", $t['updatetime'])}</span> <a href="{$t['url']}">{$t['title']}</a></li> {/sql:rs} 其他的写法： {sql:rs field="id,catid,title,thumb" table="fn_content_1" where="catid=$catid" pagesize="2" urlrull="catid=$catid"} {sql:rs field="id,catid,title,thumb" table="fn_content_1" where="catid=$catid" pagesize="2" urlrull="catid=$catid"} {sql:rs table="fn_content_1" where="catid=$catid" limit="1,10" urlrull="catid=$catid"} {sql:rs table="fn_content_1" where="catid=$catid" limit="1,10" order="id desc" urlrull="catid=$catid"} 多表链接： {sql:rs table="fn_content_1" join="fn_comment on fn_content_1.id=fn_comment.contentid" where="catid=$catid" limit="1,10" urlrull="catid=$catid"} 也可以多层嵌套，嵌套的时候别忘记将sql:rs中rs替换成其他的变量，加入pagesize这个就可以实现分页，分页输出为{$pagelist}和官方的一致。更多的就不举例了，等待大家去发现吧。是不是比官方的牛逼啊。呵呵，实现起来并不困难。要实现这样将会替换掉原有的官方SQL标签，官方的SQL标签如下： {sql:模型名称执行方法} 格式：{sql:user where("pid=1")->order("uid asc")->select();} 返回：$return数组替换后这个标签就没用了。如果要保留这个标签的话，就在下面的正则里修改一下就好了。现在开始，首先修改handle_view_file函数：找到： '#'.$this->left_delimiter.'sql:([a-z_0-9]+)\s+(.+?)'.$this->right_delimiter.'#is', 替换成 '#'.$this->left_delimiter.'sql:([a-z_0-9]+)\s+(.+?)'.$this->right_delimiter.'#is', '#'.$this->left_delimiter.'\s?\/sql:[a-z_0-9]+\s?'.$this->right_delimiter.'#i', 找到： "<?php \$sql_model = \$this->load_model('\\1');\$return = \$sql_model->\\2; ?>", 替换成 "<?php \$return_\\1 = \$this->_sqldata('\\1','\\2'); extract(\$return_\\1); \$count_\\1=count(\$return_\\1); if (is_array(\$\\1)) { foreach (\$\\1 as \$key=>\$t) { ?>", "<?php } } ?>", 下面新添加一个函数来实现： protected function _sqldata($r,$param){ //将变量替换成相应的值 $match = array(); preg_match_all('/\$([a-z_0-9]+)\s?/i', $param, $match); foreach($match[1] as $key => $value) { if(isset($GLOBALS[$value])) { $param = str_replace($match[0][$key], $GLOBALS[$value], $param); } } //将变量赋值到system数组中 $system=array(); preg_match_all("/([a-z]+)\=[\"]?([^\"]+)[\"]?/i", stripslashes($param), $matches, PREG_SET_ORDER); $arr = array('field','table','join', 'where','order','limit','pagesize','urlrule'); foreach ($matches as $v) { if(in_array($v[1], $arr)) { $system[$v[1]] = $v[2]; continue; } } $dbparams = Controller::load_config('database'); $db = mysql::getInstance($dbparams); if (isset($system['field']) && $system['field']){ $field = ' ' . $system['field'] . ' '; }else{ $field = ' * '; } if (isset($system['table']) && $system['table']){ $table = ' FROM ' . $system['table'] . ' '; }else{ $table = ' FROM ' . $dbparams['prefix'] . 'content_' . App::get_site_id(); } if (isset($system['join']) && $system['join']){ $join = ' LEFT JOIN ' . $system['join'] . ' '; } if (isset($system['where']) && $system['where']){ $where = ' WHERE ' . $system['where'] . ' '; } if (isset($system['order']) && $system['order']){ $order = ' ORDER BY ' . $system['order'] . ' '; } if (isset($system['limit']) && $system['limit']){ $limit = ' LIMIT ' . $system['limit']; } if (isset($system['pagesize']) && $system['pagesize']){ $pageurl = ''; $page = (int)$_GET['page'] ? (int)$_GET['page'] : 1; if ($system['urlrule'] && preg_match('/catid=(\d+)/i',$system['urlrule'],$catid)) { //获取栏目url,urlrule必须有catid字符 $cats = get_category_data(App::get_site_id()); $cat = $cats[$catid[1]]; $pageurl = getCaturl($cat, '{page}'); $pagesize = $system['pagesize'] ? $system['pagesize'] : 10; } elseif($system['urlrule']){ $pageurl = str_replace(array('_page_', ''), '{page}', $system['urlrule']); $pagesize = $system['pagesize'] ? $system['pagesize'] : 10; } else{ $pagesize = $system['pagesize'] ? $system['pagesize'] : 10; $pageurl = '{page}'; } $sql = 'SELECT count(*) AS total' . $table . $join . $where; $count = $db->fetch_row($sql); $total = $count['total']; $pagelist = Controller::instance('pagelist'); $pagelist->loadconfig(); $start_id = $pagesize * ($page - 1); $limit = ' LIMIT ' . $start_id . ',' . $pagesize; $pagelist = $pagelist->total($total)->url($pageurl)->num($pagesize)->page($page)->output(); } $sql = 'SELECT' . $field . $table . $join . $where . $order . $limit; //echo $sql; //die(); $data = $db->get_array($sql); //print_r($data); unset($field,$table,$join,$where,$order); return array('pagelist' => $pagelist, $r => $data, 'sql' => $sql, 'total' => isset($total) ? $total : count($data)); } 到此就完成了