我有一个类似的表:
id Continent Country State Area Sub Area City
77 North America USA California California Desert Ridgecrest
78 North America USA California California Desert Thousand Palms
79 North America USA California California Desert Yucca Valley
80 North America USA California Lake Tahoe Area Lake Tahoe North Shore Agate Bay
82 North America USA California California Desert Yucca Valley
83 North America USA California Lake Tahoe Area Lake Tahoe North Shore Alpine Meadows
185 North America USA California Lake Tahoe Area Twin Bridges
188 North America USA California Central Coast California Santa Cruz County Aptos
189 North America USA California Central Coast California Santa Cruz County Ben Lomond
我试图请求这样的数据,如果我搜索"加利福尼亚"(州),那么只有区域(下一列)的名称,而不是子区域或城市。同样的,如果搜索"加利福尼亚沙漠"(地区),子区域应该显示…(PS在某些地方,我没有子区域,所以它应该显示子区域在任何适用的地方,如果没有,移动到下一个是城市)
if($result = $con->query("SELECT * FROM MasterData WHERE state LIKE '%".$_POST['search']."%' OR city LIKE '%".$_POST['search']."%'")){
if($count = $result->num_rows) {
while($rows = $result->fetch_object()) {
echo $rows->city, '</br>';
}
$result->free();
}
首先,你的结构可能比你看到的要复杂得多:在奥地利,有一个叫做维也纳的州("Wien"),由一个维也纳县组成,由一个维也纳市组成。我认为"华盛顿"也作为一个州、一个县(不止一次)和一个市(不止一次)而存在。
也就是说,您可以简单地进行涓滴向下搜索。这在PHP中做得更好,但如果必须这样做,您可以滥用UNION:
SELECT
txt
FROM (
SELECT 1 AS level, Country AS txt FROM MasterData WHERE Continent LIKE '$criterion'
UNION
SELECT 2 AS level, State AS txt FROM MasterData WHERE Country LIKE '$criterion'
UNION
SELECT 3 AS level, Area AS txt FROM MasterData WHERE State LIKE '$criterion'
UNION
SELECT 4 AS level, Subarea AS txt FROM MasterData WHERE Area LIKE '$criterion'
UNION
SELECT 5 AS level, City AS txt FROM MasterData WHERE Subarea LIKE '$criterion'
) AS abuseview
ORDER BY level
LIMIT 1