PHP mySQL 搜索函数加入各个字段 - PHP mySQL search function join various field

PHP mySQL search function join various field

本文关键字：字段 mySQL 搜索函数 PHP | 更新日期: 2023-09-27

下面的代码用于从数据库上的表中搜索姓氏。用于获取搜索的字段是"q" 这是查询

 $sims = mysql_query("SELECT * FROM electors 
WHERE constituency = '$constituency'
 AND ward = '$ward' AND (surname RLIKE '$q') LIMIT 18");

但是，如果用户输入以下任何内容或按任何顺序组合在一起的任何组合，则该查询将不起作用，因为它只是搜索姓氏

标题， first_name，首字母，姓氏，后缀，卷号，电子邮件，电话

我希望它从这些字段中搜索匹配的结果，而无需重复结果。

这意味着，例如，如果我输入KNIGHT，它会找到我所有姓KNIGHT的人，如果我输入ROBIN KNIGHT，它会找到我任何名字为ROBIN和姓KNIGHT的人，反之亦然。

只需将它们拆分为一个OR语句：

$sims = mysql_query("SELECT * 
                     FROM `electors` 
                     WHERE `constituency` = '$constituency' 
                     AND `ward` = '$ward' 
                     AND (`title` RLIKE '$q' 
                         OR `first_name` RLIKE '$q' 
                         OR `initial` RLIKE '$q' 
                         OR `surname` RLIKE '$q' 
                         OR `suffix` RLIKE '$q' 
                         OR `roll_no` RLIKE '$q' 
                         OR `email` RLIKE '$q' 
                         OR `telephone` RLIKE '$q') 
                     LIMIT 18");

不是最有效的查询，但它会完成工作。

此外，请确保在执行该查询之前清理$q。SQL注入是一个主要问题。

$q = str_replace(" ", "|", $q);
$sims = mysql_query("SELECT DISTINCT * FROM electors 
                     WHERE constituency = '$constituency'
                     AND ward = '$ward' 
                     AND (title RLIKE '$q'
                     OR first_name RLIKE '$q'
                     OR initial RLIKE '$q'
                     OR surname RLIKE '$q'
                     OR suffix RLIKE '$q'
                     OR roll_no RLIKE '$q'
                     OR email RLIKE '$q'
                     OR telephone RLIKE '$q')
                     LIMIT 18");

但实际上你应该使用 mysqli 和准备好的语句。

$stmt = $mysqli->prepare("SELECT DISTINCT * FROM electors 
                         WHERE constituency = ?
                         AND ward = ?
                         AND (title RLIKE ?
                         OR first_name RLIKE ?
                         OR initial RLIKE ?
                         OR surname RLIKE ?
                         OR suffix RLIKE ?
                         OR roll_no RLIKE ?
                         OR email RLIKE ?
                         OR telephone RLIKE ?)
                         LIMIT 18");
$stmt->bind_param('ssssssssss', $constituency, $ward, 
                  $q, $q, $q, $q, $q, $q, $q, $q, $q);
$stmt->execute();