从数据库过滤信息时，SQL查询忽略WHERE条件

Question

The following section of code is used to to filter data from an html page in real time from an SQL database using ajax. For some reason the condition in the SQL statement ignores the following condition samples_database.sample_storage != 'discarded' but it works in the second part of the else statement.

if(isset($_POST["query"])) {
    $search = mysqli_real_escape_string($conn, $_POST["query"]);
    $query .= "
    SELECT * FROM samples_database JOIN storage_database on samples_database.storage_location=storage_database.id
    WHERE samples_database.sample_storage != 'discarded'
    AND samples_database.env_sam_id LIKE '%".$search."%'
    OR samples_database.c_sam_id LIKE '%".$search."%' 
    OR samples_database.sample_type LIKE '%".$search."%' 
    OR storage_database.storage_name LIKE '%".$search."%'
    ORDER BY samples_database.env_sam_id;";
} else {
    $query .= "
    SELECT * FROM samples_database JOIN storage_database on samples_database.storage_location=storage_database.id WHERE sample_storage != 'discarded' ORDER BY samples_database.env_sam_id;";
}

Can anybody help me this this silly problem please?

Answer 1

There's an order of precedence: AND is calculated before OR:

if(isset($_POST["query"])) {
    $search = mysqli_real_escape_string($conn, $_POST["query"]);
    $query .= "
    SELECT * FROM samples_database JOIN storage_database on samples_database.storage_location=storage_database.id
    WHERE samples_database.sample_storage != 'discarded'
    AND (samples_database.env_sam_id LIKE '%".$search."%' OR
         samples_database.c_sam_id LIKE '%".$search."%' OR
         samples_database.sample_type LIKE '%".$search."%' OR 
         storage_database.storage_name LIKE '%".$search."%')
    ORDER BY samples_database.env_sam_id;";
} else {
    $query .= "
    SELECT * FROM samples_database JOIN storage_database on samples_database.storage_location=storage_database.id WHERE sample_storage != 'discarded' ORDER BY samples_database.env_sam_id;";
}