Is it possible for a PHP MYSQL query to ignore an empty variable in a WHERE clause?

Question

I don’t know how I can do this. Basically, I have variables that are populated using combobox and then passed to form filters for the MQSQL query through the where clause. What I need to do is allow the empty combo box to the empty user, and then ignore this variable in the where clause. Is it possible?

ie, from this code. Suppose that the combo box that fills the value of $ value1 remains empty, is there any way to ignore it and only the second filter is 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. WITH

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

Sure,

 $sql = ""; if(!empty($value1)) $sql = "AND location = '{$value1}' "; if(!empty($value2)) $sql .= "AND english_name = '{$value2}'"; $query = "SELECT * FROM moth_sightings WHERE user_id = '$username' {$sql} "; $result = mysql_query($query) or die(mysql_error()); $r = mysql_numrows($result); 

Remember to deploy sql and deprecate mysql_ *, use mysqli or PDO instead

Answer 3

 if ( isset($value1) ) $query = "SELECT * FROM moth_sightings WHERE user_id = '$username' AND location = '$value1' AND english_name = $value2 "; else $query = "SELECT * FROM moth_sightings WHERE user_id = '$username' AND english_name = $value2 "; 

But you can also make a function to return a request based on your input. Also, be sure to avoid $values before creating the query.

Answer 4

1.) don't just use the mysql php extension, use either the mysqli extension or the much more secure PDO / MDB2 wrappers.

2.) do not specify a complete operator like this (in addition, you do not even code or miss the specified values ​​...). Instead, use something like this:

 sprintf("SELECT * FROM moth_sightings WHERE 1=1 AND %s", ...); 

Then fill this raw request using an array containing all the values ​​that you actually get from your form:

 $clause=array( 'user_id="'.$username.'"', 'location="'.$value1.'"', 'english_name="'.$value2.'"' ); 

You can manipulate this array in some way, for example, test empty values ​​or whatever. Now just explode the array to complete the original question from above:

 sprintf("SELECT * FROM moth_sightings WHERE 1=1 AND %s", implode(' AND ', $clause) ); 

A big advantage: even if the array of sentences is completely empty, the query syntax is valid.

Answer 5

First, check out SQL Injections . Secondly, $ r = mysql_numrows ($ result) should be $ r = mysql_num_rows ($ result);

You can use IF in MySQL, something like this:

 SELECT * FROM moth_sightings WHERE user_id = '$username' AND IF('$value1'!='',location = '$value1',1) AND IF('$value2'!='',english_name = '$value2',1); -- BUT PLEASE READ ABOUT SQL Injections. Your code is not safe. 

Answer 6

I thought of two other ways to resolve this issue:

 SELECT * FROM moth_sightings WHERE user_id = '$username' AND location = '%$value1%' AND english_name = $value2 "; 

This will return results only for this user_id , where the location field contains $value1 . If $ value1 is empty , it will still return all rows for this user_id , empty or not.

---

OR

 SELECT * FROM moth_sightings WHERE user_id = '$username' AND (location = '$value1' OR location IS NULL OR location = '') AND english_name = $value2 "; 

This will give you all the rows for this user_id that have $value1 for the location or have empty values.

Answer 7

Several other answers mention the risk of embedding SQL code, and the couple explicitly mentions the use of prepared statements, but none of them explicitly show how you could do this, which can be a big request for a beginner.

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

So, here is an example using prepared MySQLi statements (assuming $ mysqli is an already created 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 assume $value2 is a string based on the field name, despite the lack of quotes in the OP example in SQL.)

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

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

and compare it to 0 .

Here is an example PDO using named parameters, because I think they lead to a much more readable code with a lower count:

 $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 named PDO parameters, we can refer to :location_id several times in the request, binding it only once.