<?php
namespace station\api;
/**
* -------------------------
* 搜索结果检索类
* -------------------------
*
* $db 数据库连接配置数组
*
*/
class search {
static public function retrieve($db){
$keywords = filter_input( INPUT_GET, 'keywords', FILTER_SANITIZE_SPECIAL_CHARS );
$page = filter_input( INPUT_GET, 'page' , FILTER_VALIDATE_INT );
$keyfile = __DIR__.'/s/cache/key.txt';
$cachekey = file_get_contents($keyfile);
if ($keywords != $cachekey) {
file_put_contents($keyfile, $keywords);
if (!empty($keywords)) {
$sql = "
(SELECT level.title level,supplier.name,supplier.id vid,supplier.kid,supplier.location,supplier.profile FROM m_supplier_supplier supplier LEFT JOIN m_supplier_level level ON level.guid = supplier.level WHERE supplier.name LIKE '%{$keywords}%' OR supplier.location like '%{$keywords}%' ORDER BY supplier.order_is DESC)
UNION
(SELECT level.title level,person.title,person.id vid,person.kid,person.location,person.intro FROM m_supplier_person person LEFT JOIN m_supplier_level level ON person.level = level.guid WHERE person.title LIKE '%{$keywords}%' OR person.location like '%{$keywords}%' ORDER BY person.order_is DESC)
";
} else {
$sql = "
(SELECT level.title level,supplier.name,supplier.id vid,supplier.kid,supplier.location,supplier.profile FROM m_supplier_supplier supplier LEFT JOIN m_supplier_level level ON level.guid = supplier.level ORDER BY supplier.order_is DESC)
UNION
(SELECT level.title level,person.title,person.id vid,person.kid,person.location,person.intro FROM m_supplier_person person LEFT JOIN m_supplier_level level ON person.level = level.guid ORDER BY person.order_is DESC)
";
}
/* //example one
\nb\db\pdo::instance ($db[0],$db[1]);
$rows=\nb\db\pdo::execute('rows', $sql,null,$db[1]);
foreach ($rows as $row) {
$_row .= implode("','",$row) . "'),('";
}
$insert_values = "('" . rtrim($_row,",('");
$sql = "TRUNCATE TABLE s_retrieve;INSERT INTO s_retrieve(level,name,guid,location,profile) VALUES {$insert_values}";
*/
/*example two*/
$sql = "TRUNCATE TABLE s_retrieve;INSERT INTO s_retrieve(level,name,vid,kid,location,profile) $sql";
\nb\db\pdo::instance ($db[0],$db[1]);
$rows=\nb\db\pdo::execute('query', $sql,null,$db[1]);
}
//count total
\nb\db\pdo::instance ($db[0],$db[1]);
$count=\nb\db\pdo::execute('row', "SELECT COUNT(*) con FROM s_retrieve;",null,$db[1]);
$count = empty($count['con']) ? '1' : $count['con'];
$perpage = 10;
$pages = ceil($count/$perpage);
$page = is_numeric($page) ? $page : '1';
if (0 > $page) $page = 1;
if ( $pages < $page) $page = $pages;
$offset = ($page-1)*$perpage;
//pageation list
if ($pages<=5) {
for ($i=1;$i<=$pages;$i++) {
$active = ($page == $i) ? 'class="active"' : '';
$pagelist .= '<li '.$active.'><a href="retrieve.php?keywords='.$keywords.'&page='.$i.'">'.$i.'</a></li>';
}
} else {
$array = range(1,$pages);
$start = ($page-2>0) ? $page-2 : 1;
$splice = array_splice($array,$start,5);
foreach ($splice as $i) {
--$i;
$active = ($page == $i) ? 'class="active"' : '';
$pagelist .= '<li '.$active.'><a href="retrieve.php?keywords='.$keywords.'&page='.$i.'">'.$i.'</a></li>';
}
}
$sql = "SELECT * FROM s_retrieve ORDER BY ID LIMIT {$offset},$perpage";
\nb\db\pdo::instance ($db[0],$db[1]);
$rows=\nb\db\pdo::execute('rows', $sql,null,$db[1]);
$_rows['search'] = $rows;
$_rows['pagelist'] = $pagelist;
$_rows['pageinfo'] = array('perpage'=>$perpage,'count'=>$count,'pages'=>$pages,'page'=>$page);
// \nb\log($_rows);
return $_rows;
}
}
UNION联合多表查询代码举例
最新推荐文章于 2022-06-18 15:03:40 发布