php - 我在 MySQL 中的 PHP 搜索返回整个表？为什么？

Question

信息从 jquery post 传递到我的 php 函数，但即使我没有提交任何数据，这也会返回我表中的所有信息

if ( $_REQUEST['startsearch'] ) {
   $shipper = $_POST['postshipper'];
   $dealer = $_POST['postdealer'];
   $customer = $_POST['postcustomer'];
   $serial = $_POST['postserial']; 

   $sql = "SELECT * FROM `orders` WHERE shipper LIKE '%".$shipper."%' and active = '1' OR dealer     LIKE '%".$dealer."%' and active = '1' OR upper(customer) LIKE upper('%".$customer."%') and active = '1' OR serial LIKE '%".$serial."%' and active = '1'";
    $search = mysql_query($sql);
    while ($row = mysql_fetch_array($search)) {
        echo $row['dealer'];
    }
}

我希望即使在 4 个中只有 1 个被填充或者所有 4 个都被填充时也能进行搜索。

Answer 1

您可以动态添加您的查询。如果该值未设置/为空，则不包括它。否则，包括。

强制性说明:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

_{Ref: https://stackoverflow.com/a/12860140/3859027}

这是通过带有准备好的语句的 PDO 的方式:

当然这是未经测试的，但这应该让您了解包含那些已设置的内容的基本概念。那些未设置的字段将不会包含在查询中。

$db = new PDO('mysql:host=localhost;dbname=DBNAME', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if (isset($_REQUEST['startsearch'])) { // if submitted

    $params = array(); // hold the container which the values will be put in
    $base_sql = "SELECT * FROM `orders` WHERE"; // the starting query
    $sql = array(); // this will contain those queries that will be glued after its done

    // if shipper is set
    if(isset($_POST['postshipper'])) {
        // push inside the container
        $sql[] = " shipper LIKE :postshipper AND active = '1' ";
        $params[':postshipper'] = '%' . $_POST['postshipper'] . '%'; // push also the value
    }

    if(isset($_POST['postdealer'])) {
        $sql[] = " dealer LIKE :postdealer AND active = '1' ";
        $params[':postdealer'] = '%' . $_POST['postdealer'] . '%';
    }

    if(isset($_POST['postcustomer'])) {
        $sql[] = " upper(customer) LIKE upper(:customer) AND active = '1' ";
        $params[':postdealer'] = '%' . $_POST['customer'] . '%';
    }

    if(isset($_POST['postserial'])) {
        $sql[] = " serial LIKE :postserial AND active = '1' ";
        $params[':postserial'] = '%' . $_POST['postserial'] . '%';
    }

    // after its done, glue/implode them with `OR`
    $sql = implode(' OR ', $sql);

    $select = $db->prepare($sql);
    $select->execute($params);

    $results = $select->fetchAll(PDO::FETCH_ASSOC);
    print_r($results);
}