我在 MySQL 中的 PHP 搜索返回整个表?为什么 - My PHP search in my MySQL is returning the whole table? Why?

从jQuery帖子传递到我的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'];
    }
}

我希望能够搜索，即使只有 1 个中的 4 个已填充或所有 4 个都已填充。

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

必填说明：

请不要在新代码中使用mysql_*函数。它们不再维护并被正式弃用。看到红框了吗？请改为了解预准备语句，并使用 PDO 或 MySQLi - 本文将帮助您决定哪个语句。如果您选择PDO，这是一个很好的教程。

_{参考： 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);
}

你可以

对它使用 isset（）函数，if(isset($var)) ，然后这个查询将生成，否则回显一条消息，当你使用 LIKE '%$var%' 时，$var留空，它会匹配所有内容。

您

在这里提出的问题很多"或"，因此它通过了所有条件
问题在于您的查询，尝试根据您的要求对其进行这样的更改。

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'

如果您想根据

提交的信息进行更精细的搜索$_POST，您可以尝试：

if ( $_REQUEST['startsearch'] ) {
    $shipper = isset($_POST['postshipper']) ? $_POST['postshipper'] : '';
    $dealer = isset($_POST['postdealer']) ? $_POST['postdealer'] : '';
    $customer = isset($_POST['postcustomer']) ? $_POST['postcustomer'] : '';
    $serial = isset($_POST['postserial']) ? $_POST['postserial'] : ''; 
    $search_term = '';
    if(!empty($shipper)){
         $search_term = 'shipper = "' . $shipper . '"';
    }
    if(!empty($dealer)){
         if(!empty($search_term)){
               $search_term = $search_term . ' AND ';
         }
         $search_term = $search_term . 'dealer = "' . $dealer . '"';
    }
    if(!empty($customer)){
         if(!empty($search_term)){
               $search_term = $search_term . ' AND ';
         }
         $search_term = $search_term . 'customer = "' . $customer . '"';
    }
    if(!empty($serial)){
         if(!empty($search_term)){
               $search_term = $search_term . ' AND ';
         }
         $search_term = $search_term . 'serial = "' . $serial . '"';
    }
    if(!empty($search_term))
    {
          $sql = 'SELECT * FROM orders WHERE ' . $search_term;
          $search = mysql_query($sql);
          while ($row = mysql_fetch_array($search)) {
              echo $row['dealer']."<br />";
         }
     }
 }