Possible to have PHP MYSQL query ignore empty variable in WHERE clause?

Question

Not sure how I can do this. Basically I have variables that are populated with a combobox and then passed on to form the filters for a MQSQL query via the where clause. What I need to do is allow the combo box to be left empty by the user and then have that variable ignored in the where clause. Is this possible?

i.e., from this code. Assume that the combobox that populates $value1 is left empty, is there any way to have this ignored and only the 2nd filter applied.

$query = "SELECT * FROM moth_sightings WHERE user_id = '$username' AND location = '$value1' AND english_name = $value2 ";
$result = mysql_query($query) or die(mysql_error());
$r = mysql_numrows($result);

Thanks for any help. C

Answer 1

Use

$where = "WHERE user_id = '$username'";

if(!empty($value1)){
$where .= "and location = '$value1'";
}

if(!empty($value2 )){
$where .= "and english_name= '$value2 '";
}


$query = "SELECT * FROM moth_sightings $where";
$result = mysql_query($query) or die(mysql_error());
$r = mysql_numrows($result);

Answer 2

Several other answers mention the risk of SQL injection, and a couple explicitly mention using prepared statements, but none of them explicitly show how you might do that, which might be a big ask for a beginner.

My current preferred method of solving this problem uses a MySQL "IF" statement to check whether the parameter in question is null/empty/0 (depending on type). If it is empty, then it compares the field value against itself ( WHERE field1=field1 always returns true). If the parameter is not empty/null/zero, the field value is compared against the parameter.

So here's an example using MySQLi prepared statements (assuming $mysqli is an already-instantiated mysqli object):

$sql = "SELECT * 
        FROM moth_sightings 
        WHERE user_id = ? 
            AND location = IF(? = '', location, ?)
            AND english_name = ?";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('ssss', $username, $value1, $value1, $value2);
$stmt->execute();

(I'm assuming that $value2 is a string based on the field name, despite the lack of quotes in OP's example SQL.)

There is no way in MySQLi to bind the same parameter to multiple placeholders within the statement, so we have to explicitly bind $value1 twice. The advantage that MySQLi has in this case is the explicit typing of the parameter - if we pass in $value1 as a string, we know that we need to compare it against the empty string ''. If $value1 were an integer value, we could explicitly declare that like so:

$stmt->bind_param('siis', $username, $value1, $value1, $value2);

and compare it against 0 instead.

Here is a PDO example using named parameters, because I think they result in much more readable code with less counting:

$sql = "SELECT * 
    FROM moth_sightings 
    WHERE user_id = :user_id 
        AND location = IF(:location_id = '', location, :location_id)
        AND english_name = :name";
$stmt = $pdo->prepare($sql);
$params = [
    ':user_id' => $username,
    ':location_id' => $value1,
    ':name' => $value2
];
$stmt->execute($params);

Note that with PDO named parameters, we can refer to :location_id multiple times in the query while only having to bind it once.