Null Value in any field hinders recordset 1

[hovercraft]

I have a form with 4 fields for returning a recordset. The idea is if there is any field left empty, the value is the wildcard '%'. Such that if they are all 4 empty, every record is returned.
This works fine, except when there is as null value in any of the given fields in the source table. Then no matter if you complete all 4 fields nothing is returned. How can I allow for null or empty values?
here is what I have:

$search_last = $_REQUEST['srchlast']."%";
$search_case = $_REQUEST['srchcase']."%";
$search_cit = $_REQUEST['srchcit']."%";
$search_status = $_REQUEST['srchstatus']."%";


$mydeflist = ("SELECT * FROM tmp_tbl_mysqlactive WHERE cust_id =  '".$_SESSION['mycustid']."' AND def_nmlast LIKE '".$search_last."' AND def_casenbr LIKE '".$search_case."' AND def_citnbr LIKE '".$search_cit."' AND def_casestatus LIKE '".$search_status."' GROUP BY scs_id ORDER BY def_nmlast");

Thanks for your help
Dave

 

[jpadie]

they are AND's so each must be true.

if you want OR's just change accordingly.

if you want something like

$sql = "
SELECT * 
FROM tmp_tbl_mysqlactive 
WHERE 
	(
		cust_id =  '$_SESSION[mycustid]'
	)
	AND
	( 
		def_nmlast LIKE '$search_last' 
		OR is null def_nmlast
		OR def_nmlast= ''
	)
	AND 
	(
		def_casenbr LIKE '$search_case' 
		OR is null def_casenbr
		OR def_casenbr = ''
	)
	AND 
	(
		def_citnbr LIKE '".$search_cit."' 
		OR is null def_citnbr
		OR def_citnbr= ''
	)
	AND 
	(
		def_casestatus LIKE '$search_status' 
		OR is null def_casestatus
		OR def_casestatus = ''
	)
GROUP BY scs_id 
ORDER BY def_nmlast
";

note that you should ensure that all your variables are mysql escaped before using them in a query.

 

[hovercraft]

thanks JP! The solution you provided doesn't return any records. Even if a value is supplied.

 

[hovercraft]

If I use the wildcard '%' why wouldn't it return every record regardless of value?

 

[jpadie]

echo out the sql and run it through phpmyadmin

 

[hovercraft]

I'm not sure what you mean by "echo out".
I don't use phpmyadmin but I did run a simple select for null values in MySQL query builder for the def_citnbr field and it returned fine.

Maybe I'm going about this the wrong way entirely.

Here's the latest sql string I've been trying.

$mydeflist ="SELECT * FROM tmp_tbl_mysqlactive WHERE (cust_id = '".$_SESSION[mycustid]."') AND ( tmp_tbl_mysqlactive.def_nmlast LIKE '".$search_last."'         OR is null tmp_tbl_mysqlactive.def_nmlast OR tmp_tbl_mysqlactive.def_nmlast= '' ) AND ( tmp_tbl_mysqlactive.def_casenbr LIKE '".$search_case."' OR IS NULL tmp_tbl_mysqlactive.def_casenbr OR tmp_tbl_mysqlactive.def_casenbr = '') AND ( tmp_tbl_mysqlactive.def_citnbr LIKE '".$search_cit."' OR IS NULL tmp_tbl_mysqlactive.def_citnbr OR tmp_tbl_mysqlactive.def_citnbr= '' ) AND ( tmp_tbl_mysqlactive.def_casestatus LIKE '".$search_status."' OR IS NULL tmp_tbl_mysqlactive.def_casestatus OR tmp_tbl_mysqlactive.def_casestatus = '')GROUP BY scs_id ORDER BY def_nmlast";

 

[vacunita]

He means print out the query so all variables are replaced with their values, and run the resultant query in whichever mysql front end you are using like Query Browser to check for any errors with the final query.

echo $mydeflist;

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[jpadie]

with anything other than the most basic of queries I recommend formatting the query via some vertical paradigm. it makes it much easier to read.

also you do not need to break out of double quotes in order to expand variables in php.

lastly, the is null operator is placed after the operand.

SELECT * 
FROM tmp_tbl_mysqlactive 
WHERE 
	(
		cust_id = '$_SESSION[mycustid]'
	) 
	AND 
	( 
		tmp_tbl_mysqlactive.def_nmlast LIKE '$search_last'         		
		OR  tmp_tbl_mysqlactive.def_nmlast is null
		OR tmp_tbl_mysqlactive.def_nmlast = '' 
	) 
	AND 
	( 
		tmp_tbl_mysqlactive.def_casenbr LIKE '$search_case' 
		OR  tmp_tbl_mysqlactive.def_casenbr is null
		OR tmp_tbl_mysqlactive.def_casenbr = ''
	) 
	AND 
	( 
		tmp_tbl_mysqlactive.def_citnbr LIKE '$search_cit' 
		OR  tmp_tbl_mysqlactive.def_citnbr is null
		OR tmp_tbl_mysqlactive.def_citnbr = '' 
	) 
	AND 
	( 
		tmp_tbl_mysqlactive.def_casestatus LIKE '$search_status' 
		OR  tmp_tbl_mysqlactive.def_casestatus is null
		OR tmp_tbl_mysqlactive.def_casestatus = ''
	)
GROUP BY scs_id 
ORDER BY def_nmlast

I am not convinced by the group by clause here. particularly given that scs_id is not one of the selected columns nor are there any aggregate functions. none of these are killers but it makes me suspicious about the value that the group by is offering. perhaps consult the mysql forum from here on in as your question is actually about mysql syntax and not php

 

[hovercraft]

THANK YOU JPADIE!! (...again)
Perhaps the syntax of the IS NULL is what was messing me up. I just copied your code and it worked perfectly!

Hovercraft