通配符输入导致其他搜索输入以多输入搜索形式检索所有数据

3zwtqj6y 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(337)

我有一个多领域的php搜索表。将通配符添加到其中一个搜索字段时，会导致其他字段检索数据库中的所有数据。如果没有通配符，country只查找国家，cityname只查找城市：

$sql = " SELECT * FROM `epic_schools_tbl` WHERE ";

if (!empty($_POST['submit'])) { 

   if (isset($_POST['country'])) {
      $country = $_POST['country'];
      $sql .= " `country` = :country ";
   }
   if (isset($_POST['cityname'])) {
      $cityname = $_POST['cityname'];
      $sql .= " OR `city` = :cityname ";
   }
   $stmt = $pdo->prepare($sql);
   $stmt->execute(array($country,$cityname));

   while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
     echo "<p>" . $row['country'] . "</p><p>" . $row['city'] . "</p>" . PHP_EOL;
   }
}

添加通配符（学名）时：

$sql = " SELECT * FROM `epic_schools_tbl` WHERE ";

if (!empty($_POST['submit'])) { 

   if (isset($_POST['country'])) {
       $country = $_POST['country'];
       $sql .= " `country` = :country ";
   }
   if (isset($_POST['schoolname'])) {
       $schoolname = $_POST['schoolname'];
       $sql .= " OR `school_name` LIKE :schoolname ";
   }
   if (isset($_POST['cityname'])) {
       $cityname = $_POST['cityname'];
       $sql .= " OR `city` = :cityname ";
   }
   $stmt = $pdo->prepare($sql);
   $stmt->execute(array($country,"%$schoolname%",$cityname));

   while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      echo "<p>" . $row['country'] . "</p><p>" . $row['school_name'] . "</p><p>" . $row['city'] . "</p>" . PHP_EOL;
   }
}

它使country和/或cityname检索数据库中的所有数据。select语句是否可以编写为接受一个带有通配符的输入，而其他输入的格式不是通配符？

mysql wildcard pdo search prepared-statement

来源：https://stackoverflow.com/questions/50628889/wildcard-input-causes-other-search-inputs-to-retrieve-all-data-in-multiple-input

1条答案

按热度按时间

ltskdhd11#

部分解决方案包括在select语句中放置if函数。此外，如果保留为空，则替换或使用并有助于消除city字段或schoolname字段返回整个数据库。

$sql = "select * from epic_schools_tbl where country = if(length(:countryname)=0, country, :countryname2) and city = if(length(:cityname)=0, city, :cityname2) and (school_name like concat('%', if(length(:schoolname)=0, school_name, :schoolname2) , '%') or alternate_names like concat('%', if(length(:alternatenames)=0, alternate_names, :alternatenames2) , '%') and aka_names like concat('%', if(length(:akanames)=0, aka_names, :akanames2) , '%'))";

$stmt = $pdo->prepare($sql);

$countryname = $_POST['country'];
$cityname = $_POST['cityname'];
$schoolname = $_POST['schoolname'];
$alternatenames = $_POST['schoolname'];
$akanames = $_POST['schoolname'];

//Bind the variables to the prepared statement. Maybe each bind variable must have unique name.
$stmt->bindParam(':countryname', $countryname);
$stmt->bindParam(':countryname2', $countryname);
$stmt->bindParam(':cityname', $cityname);
$stmt->bindParam(':cityname2', $cityname);
$stmt->bindParam(':schoolname', $schoolname);   
$stmt->bindParam(':schoolname2', $schoolname); 
$stmt->bindParam(':alternatenames', $alternatenames);   
$stmt->bindParam(':alternatenames2', $alternatenames);
$stmt->bindParam(':akanames', $akanames);   
$stmt->bindParam(':akanames2', $akanames);

//Nothing to pass in. Everything is already bound.  
$stmt->execute();

赞(0）回复(0）举报 2021-06-20

我来回答

通配符输入导致其他搜索输入以多输入搜索形式检索所有数据

1条答案

相关问题

热门标签

最新问答