How to do dynamic query on null column with mySQL and php

How to do dynamic query on null column with mySQL and php

If you have to query null able column dynamically you have to use the like keyword

“select  emp_name from emp where emp_name like ‘%$emp_name%’;

How every this works fine if the emp_name column is not null able. But this will return no rows if the emp_name has null values in it. So we have to tune the query more we have to put one more condition.

“select  emp_name from emp where (emp_name like ‘%$emp_name%’ or emp_name is null);”

The above query works fine but will return every row from the table because it is always true.  So we have to use the third option which is using  ?: operator.

So we build the query this way.

$emp_name  = mysql_escape_string($_REQUEST['emp_name']);

$sqlstr=” select  emp_name from emp where 1=1 “;

$sqlstr= $sqlstr.($emp_name? " and emp_name like '%{$emp_name}%' ":"");

So as you can see if the user supply null in the emp_name field the query will be

select  emp_name from emp where 1=1

and this will return every thing in the table

and if the user supply value for emp_name the result will be according the query matching the emp_name.

you may ask what is the difference between this

1- “select  emp_name from emp where emp_name like ‘%%’;

2- “select  emp_name from emp where emp_name like ‘%smith%’;

The first query will not work if the column has null value and the second query will return every match to *smith*.

So I use  this and it is working fine with my dynamic selection form

$emp_name  = mysql_escape_string($_REQUEST['emp_name']);

$sqlstr=” select  emp_name from emp where 1=1 “;

$sqlstr= $sqlstr.($emp_name? " and emp_name like '%{$emp_name}%' ":"");