php - 使用php mysql结果相关记录进行多关键字搜索

ID    CODE      STATE    CITY            AREA
1   SBIN0000952 ORISSA  JAIPUR          TOWN
2   SBIN0000953 ORISSA  KURAPUT         VILLAGE
3   SBIN0000954 DELHI   DELHI           TOWN
4   SBIN0000955 DELHI   NEW DELHI       VILLAGE
5   SBIN0000956 GOA     SOUTH GOA       VILLAGE
6   SBIN0000957 GOA     PANAJI          TOWN
7   SBIN0000958 KERLA   CHOCHIN         TOWN
8   SBIN0000959 KERLA   TRIVANDRAM      VILLAGE
9   SBIN0000960 ANDHRA  VIZAG           TOWN
10  SBIN0000961 ANDHRA  HYDERABAD       VILLAGE

上面是我的表格，例如，我想搜索多个关键字

喀拉拉邦，那么它应该显示记录

7   SBIN0000958 KERLA   CHOCHIN    TOWN

下面我目前在PHP中的代码

include('dbConnect.inc.php');

//collect
if(!isset($_POST['search'])){
    header("Location:index.php");

}
    $searchq = mysql_real_escape_string( $_POST['search']);
    $search_sql = "SELECT * FROM `bankifscin` WHERE STATE LIKE '%$searchq%' OR CITY LIKE '%$searchq%' OR CODE LIKE '%$searchq%' ";
    $search_query = mysql_query($search_sql) or die(mysql_error());
    if(mysql_num_rows($search_query)!=0){
    $search_rs = mysql_fetch_assoc($search_query);
    }

打印

<?php if(mysql_num_rows($search_query)!=0){
    do{ ?>

    <?php

    $bank = $search_rs['BANK'];
    $ifsc = $search_rs['IFSC'];
    $branch= $search_rs['BRANCH'];
    $micr = $search_rs['MICR_CODE'];
    $address = $search_rs['ADDRESS'];
    $contact = $search_rs['CONTACT'];
    $city = $search_rs['CITY'];
    $district = $search_rs['DISTRICT'];
    $state = $search_rs['STATE'];
         ?>
         <table width="100%" border="2" bordercolor="#000" class="bdrcolor">


    <tbody>
  <tr>
    <td width="15%"><?=$bank?></td>
       <td width="15%"><?=$branch?></td>
       <td width="15%"><b>IFSC:</b><?=$ifsc?> <br /><b>MICR:</b><?=$micr?></td>
       <td width="20%"><?=$address?><br /> <b>City :</b><?=$city?> <br /> <b>District :</b><?=$district?> <br /> <b>State:</b> <?=$state?></td>
       <td width="10%"><?=$contact?></td>
  </tr>
  <br />
  </tbody>
</table>



    <?php   }while ($search_rs = mysql_fetch_assoc($search_query));

}else{
        echo "No Results";

        }


    ?>

它只给出一个关键字的结果或没有结果

最佳答案

您应该迭代结果集，以查看所有记录。

    $result = mysql_query($query);
    if ($result != NULL)
    {
      while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
      {
        foreach ($row as $key => $value)
        {
          // todo: use $key and $value
        }
      }
      mysql_free_result($result);
   }

编辑：

如果您想知道，呼叫mysql_fetch_array($result, MYSQL_ASSOC)确实与mysql_fetch_assoc($result)是同一回事。

编辑：

如果只想查看包含ALL关键字的记录。然后，您应该更改SQL查询，您需要在SQL查询中使用AND而不是OR。

"SELECT * FROM `bankifscin` WHERE STATE LIKE '%$state%' AND CITY LIKE '%$city%' AND CODE LIKE '%$code%' ";

我想并不是所有的关键字都在使用。因此，您可能希望在运行时构建该查询。

$query = "SELECT * FROM `bankifscin` WHERE 1=1";
if ($useCity) $query .= " AND city like '%{$city_keyword}%'";
if ($useState) $query .= " AND state like '%{$state_keyword}%'";
if ($useCode) $query .= " AND code like '%{$code_keyword}%'";

我对你的问题不太了解。如果只指定了关键词，也许您只想包含这些关键词？（例如$useCity = ($city_keyword != null);）

编辑：

根据我对您上一则帖子的了解。您希望将输入分成单词。每个单词必须至少匹配一列。意思是所有单词都必须用于查询。然后，您可以尝试以下操作：

// split the input in words
$searchq = $_POST['search'];
$words = explode(" ", $searchq);
$wordCount = count($words);

// declare which columns should be searched
$columns = array("city", "town", "code", "area");
$columnCount = count($columns);

// start from a basic query, which we will expand later
$query = "SELECT * FROM `bankifscin` WHERE ";

// all words should be used
// that's why we are adding them with the 'AND' operator.
for($i=0;$i<$wordCount;$i++)
{
  $word = mysql_escape_string($words[$i]);
  if ($i > 0) $query .= " AND ";

  // build the condition
  $condition = " (";
  for($j=0;$j<$columnCount;$j++)
  {
    // but each word can match any column, doesn't matter which one.
    // that's why we are using the 'OR' operator here.
    if ($j > 0) $condition .= " OR ";
    $column = $columns[$j];
    $condition .= " {$column} like '%{$word}%' ";
  }
  $condition .= ") ";
  $query .= $condition;
}
echo $query;

这里的窍门是，通过在运行时构建sql，它将根据用户使用的单词数产生完全不同的查询。

关于php - 使用php mysql结果相关记录进行多关键字搜索，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30137197/