萬能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)); ??? } ? ??? 到此就完成了 ? ?