where clause with not null variables

Question

Some of the variables below ( us1 or sp1..) may have null value ( customer has the possibility not to fill the according textviews). How can i syntax "where" clause with only "not null " variables; For example, when us1 is not null and sp1 is null, "where" clause has to be: " where customer.username1 = 'us1' " Thanks in advance

$us1 = $_POST['username1'];
$sp1 = $_POST['startPoli1'];

SELECT `username1`,`startPoli1`, `finalPoli1`,`weight1` ,`phone1` 
 FROM customer ,registration1 
 where  customer.startPoli1 = 'sp1' and customer.username1 = 'us1'

possible duplicate of [Optional Arguments in WHERE Clause](http://stackoverflow.com/questions/10185638/optional-arguments-in-where-clause) — GSerg, Oct 26 '14 at 07:50
possible duplicate of [PHP and MySQL optional WHERE conditions](http://stackoverflow.com/questions/15212081/php-and-mysql-optional-where-conditions) — GSerg, Oct 26 '14 at 07:51

score 1 · Accepted Answer · answered Oct 26 '14 at 07:56

1

Write the condition in this way:

WHERE ('sp1' IS NULL OR customer.startPoli1 = 'sp1')
AND ('us1' IS NULL OR customer.username1 = 'us1')

answered Oct 26 '14 at 07:56

st mnmn

3,555
3
25
32

score 0 · Answer 2 · answered Oct 26 '14 at 08:16

Like this:

$us1 = $_POST['username1'];
$sp1 = $_POST['startPoli1'];

$sql = "SELECT `username1`,`startPoli1`, `finalPoli1`,`weight1` ,`phone1` 
 FROM customer ,registration1 where 1=1 ";

if($sp1 != null)
   $sql += "and customer.startPoli1 = 'sp1'";
if($us1 != null) 
   $sql += "and customer.username1 = 'us1'";

score 0 · Answer 3 · answered Oct 26 '14 at 08:48

I think - it's error in question. Forgotten about $sp1 and $us1 is variables

$us1 = $_POST['username1'];
$sp1 = $_POST['startPoli1'];

$sql = "SELECT `username1`,`startPoli1`, `finalPoli1`,`weight1` ,`phone1` 
 FROM customer ,registration1 where 1=1 ";

if($sp1 != null)
   $sql += "and customer.startPoli1 = '" + $sp1 + "'";
if($us1 != null) 
   $sql += "and customer.username1 = '" + $us1 + "'";

where clause with not null variables

3 Answers3