How to use null in find_in_set

#1

Dear all,

this is my query :

$query1 = 'select * from tbl_user_reg where
FIND_IN_SET(marital_status,:ms) or marital_status IS NULL and
FIND_IN_SET(caste_id,:caste) or caste_id IS NULL and
FIND_IN_SET(diet,:diet) or diet IS NULL and
FIND_IN_SET(mother_tongue,:mt) or mother_tongue IS NULL

but abovre query is not working…?

any suggestion please…?

thanks,

#2

that’s a messy information. Either you get a definitive error message, or there is some description what that should mean.

#3

is there any solution…?

i want to find the values if checked or else also query want to work…?

#4

What are you actually asking the database for? That seems like a lengthy query just to find null values.

#5

Ill tell u Example:

Capture12

here if he is not selecting any value in community query is not working so that i added IS NULL …

#6

The form being null is different than the database being null though.

And, when the form gets processed, it just wont have any values for the checkboxes. You are doing a select, not an insert, so the form is really irrelevant in the use case I see.

#7

i am using ajax request to get the values…
not using forms…

#8
  function funfilter() {
  var ms = new Array();
$( "input[name='ms[]']:checked" ).each( function() {
          	ms.push( $( this ).val() );
          } );
    var relig = new Array();
          $( "input[name='relig[]']:checked" ).each( function() {
          	relig.push( $( this ).val() );
          } );
	        var diet = new Array();
	        $( "input[name='diet[]']:checked" ).each( function() {
	        	diet.push( $( this ).val() );
	        });
	   
	        var mt = new Array();
	        $( "input[name='mt[]']:checked" ).each( function() {
	        	mt.push( $( this ).val() );
	        } );
	  
	    	var agefrom=document.getElementById('agefrom').value;
			var ageto=document.getElementById('ageto').value;
			var lookfor=document.getElementById('lookfor').value;
	        

	     
			$.ajax({
				url:'filterdata.php?filter&&ms='+ms+'&relig='+relig+'&diet='+diet+'&mt='+mt+'&agefrom='+agefrom+'&ageto='+ageto+'&lookfor='+lookfor,
				complete:function(data)
				{
					document.getElementById('filterdata').innerHTML=data.responseText;
					 $('#ld-roo').hide(); 
					 //alert(data.responseText);
				}
			});
		  
		 
	}
#9

It doesn’t matter how you are submitting the form, which is what you are doing regardless if it is ajax or not.

If nothing is checked, this array is empty. The same result if you submit the form or ajax does. And that still has nothing to do with the query itself.

Is the form to filter search criteria? Then nothing submitted means show everything, correct?

#10

No it showing empty…

query is not working

#11

The or column_name IS NULL that you added to the query will match null data in the column - martial_status, cast_id, diet, … I doubt that is what you want. Also, the use of OR … AND … OR … AND that you ended up with won’t produce the result you think, due to operator precedence. You would need to add ( ) around each pair of OR’ed expressions.

If there are no checkboxes checked, and you want this to match all values for a column, the simplest way is to just leave the term out of the WHERE clause.

To dynamically build the WHERE clause with only the terms that have input values, use an array to hold the term(s), then implode() the array using the ’ AND ’ keyword to produce the WHERE clause.

#12

So what i am using
FIND_IN_SET(diet,:diet) or diet IS NULL is wrong method…? give any solution please…!

#13

What should be returned if nothing is selected?

I very much doubt that you want to return those columns that are null; that means it should return something… So, what is that criteria? I already asked previously, I don’t know if it was overlooked or ignored.