如何使用多个输入进行一次搜索来搜索多个列

here is my php code:

这是我的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);

Here is my html:

这是我的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>

What I'm trying to do is have my site searchable using any combination of city zip or street. I'm sure I'll have to concatenate or something, but as a backyard programmer I'm a bit stumped. Thanks!

我想要做的是让我的网站可以使用城市邮编或街道的任意组合进行搜索。我确定我必须连接或者其他东西，但作为一个后院程序员我有点难过。谢谢！

2 个解决方案

#1

To get it all in 1 query (not tested);

在1个查询中得到它（未经测试）;

    <?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;

#2

Here is a sample of how you can generate the query search based on the fields you define. The search will be performed only on the fields that have a value and the query is safe (the values are escaped so you don't need to worry about SQL Injection).

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

I added comments in the code

我在代码中添加了注释

<?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);

#1