如何在 SQL、代码点火器中的一个查询中设置不同的限制不同列?

Question

我有一个数据表，我需要按列名显示搜索结果

我有这张桌子

id address        city      zipcode  state  country
1  12, street     arizona   1234     abcd   new island
2  abcd,stree     abcd      7895     abcd   us
3  new av         mumbai    6875     abcd   uk
4  abcd, street   test ci   5687     abcd   new island
5  6741, street   df df     12345    abcd   uae

如果有人搜索"abcd"，我需要什么，我希望结果像这样显示

5 个地址，与"ABCD"类似

城市、邮政编码、州和国家各 1

个 所以基本上我想在一个查询中限制地址以获得 5 个地址，限制 1 个城市(如果有(，限制 1 个邮政编码(如果可用(，限制 1 个州(如果可用(，限制 1 个国家(如果可用(。

此外，关键字应该查找每一列，而不仅仅是地址。 我确实通过使用多个查询来实现这一点

$query = $this->db->query("SELECT city FROM ".$this->db->dbprefix('prison')." WHERE city like'%".$keyword."%' LIMIT 1");
$query2 = $this->db->query("SELECT zipcode FROM ".$this->db->dbprefix('prison')." WHERE zipcode like'%".$keyword."%' LIMIT 1");
$query3 = $this->db->query("SELECT state FROM ".$this->db->dbprefix('prison')." WHERE state like'%".$keyword."%' LIMIT 1");
$query4 = $this->db->query("SELECT address FROM ".$this->db->dbprefix('prison')." WHERE address like'%".$keyword."%' LIMIT 10");
$query5 = $this->db->query("SELECT country FROM ".$this->db->dbprefix('prison')." WHERE country like'%".$keyword."%' LIMIT 1");
$result_array = [];
$result_array['cities'] =  $query->row();
$result_array['zipcode'] =  $query2->row();
$result_array['state'] =  $query3->row();
$result_array['address'] =  $query4->result();
$result_array['country'] =  $query5->row();
return $result_array;

无论如何我可以在一个查询中实现这一点吗？ 这个解决方案是否可行 我在Keyup上通过Ajax显示结果，所以我担心运行多个查询会影响结果的加载时间。

我还尝试了一种使用 UNION 运算符的解决方案，它确实给了我需要的结果，但没有提供标识符以查看它是城市、州还是邮政编码

$query = $this->db->query("SELECT prison_city FROM ".$this->db->dbprefix('prison')." WHERE prison_city like'%".$keyword."%' LIMIT 1 UNION SELECT prison_zipcode FROM ".$this->db->dbprefix('prison')." WHERE prison_zipcode like'%".$keyword."%' LIMIT 1 UNION SELECT prison_state FROM ".$this->db->dbprefix('prison')." WHERE prison_state like'%".$keyword."%' LIMIT 1 UNION SELECT prison_country FROM ".$this->db->dbprefix('prison')." WHERE prison_country like'%".$keyword."%' LIMIT 1 UNION SELECT prison_address FROM ".$this->db->dbprefix('prison')." WHERE prison_address like'%".$keyword."%' LIMIT 10");

return $query->result();

它输出此数据

Array ( 
[0] => stdClass Object ( [prison_city] => Susanville ) 
[1] => stdClass Object ( [prison_city] => California ) 
[2] => stdClass Object ( [prison_city] => 24900 CA-202 ) 
[3] => stdClass Object ( [prison_city] => New York, America ) 
[4] => stdClass Object ( [prison_city] => 1 Kings Way, Avenal, CA 93204, USA ) 
[5] => stdClass Object ( [prison_city] => 14901 Central Ave ) 
[6] => stdClass Object ( [prison_city] => 19025 Wiley's Well Road ) 
[7] => stdClass Object ( [prison_city] => 23500 Kasson Rd ) 
[8] => stdClass Object ( [prison_city] => 475-750 Rice Canyon Rd ) 
[9] => stdClass Object ( [prison_city] => 19005 Wiley's Well Road ) 
)

它有两个问题，首先它没有提供标识符，其次是我仅为应用于整个结果的地址设置的限制。

Answer 1

您可以将联合与自定义值一起使用。例如：-

SELECT * FROM (SELECT prison_city , 1 AS type FROM ".$this->db->dbprefix('prison')." WHERE prison_city like'%".$keyword."%' LIMIT 1
) tb1  UNION (SELECT prison_zipcode ,2 FROM ".$this->db->dbprefix('prison')." WHERE prison_zipcode like'%".$keyword."%' LIMIT 1) 
UNION (SELECT prison_state,3 FROM ".$this->db->dbprefix('prison')." WHERE prison_state like'%".$keyword."%' LIMIT 1) 
UNION (SELECT prison_country,4 FROM ".$this->db->dbprefix('prison')." WHERE prison_country like'%".$keyword."%' LIMIT 1) 
UNION (SELECT prison_address,5 FROM ".$this->db->dbprefix('prison')." WHERE prison_address like'%".$keyword."%' LIMIT 10) 

并对单独的 LIMIT 子句使用一组子查询。

这是一个工作小提琴。

http://sqlfiddle.com/#!9/a6c585/74700