UNION联合多表查询代码举例

<?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;
	}
}