如何使用多个输入在一次搜索中搜索多个列 - How to search multiple columns with one search using multiple inputs

How to search multiple columns with one search using multiple inputs

本文关键字：搜索一次何使用输入 | 更新日期: 2023-09-27

这是我的php代码：

if (($_REQUEST['Search']) || ($_REQUEST['CSV'])){
$street = $_REQUEST['street'];
$zip = $_REQUEST['zip'];
$city = $_REQUEST['city'];
$type = $_REQUEST['type'];
if ($_REQUEST['street']){
    $q = " SELECT * FROM $usertable WHERE address LIKE '%$street%' ORDER BY address ";
}
if ($_REQUEST['zip']){
    $q = " SELECT * FROM $usertable WHERE zip LIKE '%$zip%' ORDER BY address ";
}
if ($_REQUEST['city']){
    $q = " SELECT * FROM $usertable WHERE city LIKE '%$city%' ORDER BY address ";
}
$qu = mysql_query($q);

这是我的html:

<form action="" method="post">
Zip:<input name="zip" type="text" />
Street:<input name="street" type="text" />
City:<input name ="city" type="text" />
Type:<select id="type" name="type">
<option value="Invasion">Invasion</option>
<option value="Burglary">Burglary</option>
<option value="Theft">Theft</option>
</select>
<input name="Search" type="submit" value="Search" />
<input name="CSV" type="submit" value="Get CSV" />
</form>

我想做的是使用城市邮政编码或街道的任何组合来搜索我的网站。我确信我将不得不连接或其他什么，但作为一个后院程序员，我有点不知所措。谢谢

在一个查询中获取所有信息（未测试）；

    <?php
    $q = " SELECT * FROM ". $usertable ." WHERE 1 %s ORDER BY address ";
    if ($_REQUEST['street']){
        $where[] = " address LIKE '%".$street."%' ";
    }
    if ($_REQUEST['zip']){
        $where[] = " zip LIKE '%".$zip."%' ";
    }
    if ($_REQUEST['city']){
        $where[] = " city LIKE '%".$city."%' ";
    }
    $q = sprintf($q, implode(" AND ", $where));
    echo $q;

以下是如何根据定义的字段生成查询搜索的示例。搜索将只在具有值且查询是安全的字段上执行（值是转义的，因此您不必担心SQL注入）。

我在代码中添加了注释

<?php
if (($_REQUEST['Search']) || ($_REQUEST['CSV'])){
$conditions = array();
// define the fields that are searchable (we assumed that the table field names match the form input names)
$search_fields = array('street', 'zip', 'city', 'type');
foreach($search_fields as $field) {
    // if the field is set and it is not empty
    if (isset($_REQUEST[$field]) && strlen($_REQUEST[$field]) > 0){
        // escape the value
        $conditions[] = "`$field` LIKE '%". mysql_real_escape($field) ."%'";
    }
}
$q = "SELECT * FROM $usertable ";
// if there are conditions defined
if(count($conditions) > 0)
{
    // concatenate them and append to the query
    // we use operator AND to retrieve results that match all defined criteria
    $q .= "WHERE ".  implode(" AND ", $conditions);
}
$qu = mysql_query($q);