php - 从已输入关键字的下一行获取数据

Question

我有一个与此类似的表格:

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();
}

Answer 1

首先，您的构造可能比表面上看到的更多:在奥地利，有一个名为维也纳(“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