Page 1 of 2

Combining OR with BOOLEAN MODE in Query

Posted: Sun Mar 12, 2006 6:30 pm
by BZorch
I have a form with 5 fields to input search parameters. So I have 25 else if statements ( I hope this is the correct approach.) I have figured out a number of problems with various combinations of my MYSQL query, but for the life of me I can not figure this one out:

$query = "SELECT Field1, Field2 FROM TEST_DB WHERE (Field1='$value1') OR (Field2='$value1') OR (Field3='$value1') AND MATCH (Field4) AGAINST ('$value4' IN BOOLEAN MODE) ORDER BY MATCH (Field4) AGAINST ('$value4' IN BOOLEAN MODE)";

All fields in query are indexed.

I can get the searches to work when everything is using AND and the BOOLEAN MODE instead of the OR, but it does not produce the results I want. Not all records in Field 1, Field2 and Field 3 have a value some are NULL. Searching for $value1 is no problem when it only searches with OR, but as soon as I add the BOOLEAN MODE is does not work.

Can anyone offer any insight?

Thank You.

Posted: Sun Mar 12, 2006 8:14 pm
by feyd
maybe it's a precedence problem?

Code: Select all

SELECT Field1, Field2 FROM TEST_DB WHERE (Field1='$value1' OR Field2='$value1' OR Field3='$value1') AND MATCH (Field4) AGAINST ('$value4' IN BOOLEAN MODE) ORDER BY MATCH (Field4) AGAINST ('$value4' IN BOOLEAN MODE)

Posted: Mon Mar 13, 2006 9:44 am
by ody
Have you got a table dump and some examples of what you expect to happen? Could be of more help then.

Posted: Mon Mar 13, 2006 9:12 pm
by BZorch
I appreciate the help. I know with so little info it is a bit difficult to understand the whole picture.

For the query I mentioned, I combined the OR Statements into a single set of paratheses and it has worked.

Though the precedence is an issue. I am using

if else (strlen($value1>0 && strlen($value1)>0 && strlen($value1)>0) in a series of 25 combinations. I reordered them starting with 5 options to 4 all the way to 1 as the last. This has solved many of the issues, but for instance if $value1 is not at the beginning of the if else then it will skip it and go to the first instances of the if else where it occurs at the begining. Instead of just making sure all variables exist then excuting the action. It is very frustrating. Is this typical?

What would you suggest to use in an if else to check if a value exist? I first used

if else ($value1 && $value2 && value4)

This did not work very well. I have had more success with strlen(). Is the strlen() a good idea? If the field is blank I am setting the variable to FALSE.

Posted: Mon Mar 13, 2006 9:15 pm
by feyd
so the query works now, and you've moved on to code? Post the code, maybe there's a more simple solution to what you are trying to do..

Posted: Tue Mar 14, 2006 9:49 pm
by BZorch
The script has gotten very long. Because the logic below is repeated twice and each of the 5 variable from my form are validated. But the code below shows how I have structured the logic to pick which query to use. I took out the queries to shorten the length. Is the logic sound? Like I said I orginally tried using just if else ($value1 && $value2){}, but it did not work as well as the strlen(). Any insight is appreciated.

Code: Select all

if (isset($_GET['np']) && eregi("[0-9]", $_GET['np']) && $_GET['np'] < 600) {$num_pages = $_GET['np'];
} else {						
if (strlen($value1)>0 && strlen ($value2)>0 && strlen($value3)>0 &&  strlen($value4)>0 && strlen($value5)>0) { 
$query = 
}else if (strlen($value1)>0 && strlen ($value2)>0 && strlen($value3)>0 &&  strlen($value4)>0){ 
$query = 
}else if (strlen($value2)>0 && strlen ($value3)>0 && strlen ($value4) >0 && strlen ($value5)>0){
$query = 
}else if (strlen ($value4) >0 && strlen ($value5)>0 && strlen ($value1)>0 && strlen ($value2) >0){
$query = 
}else if (strlen ($value2)>0 && strlen ($value3)>0 &&  strlen($value1)>0 && strlen ($value5)>0){							
$query =
}else if (strlen ($value1)>0 && strlen ($value2)>0 && strlen ($value5)>0){
$query=
}else if (strlen($value1)>0 && strlen ($value2)>0 && strlen($value3)>0){
$query = 
}else if (strlen($value2)>0 && strlen ($value3)>0 && strlen ($value4) >0 ){
$query 
}else if (strlen ($value3) >0 &&  strlen ($value4)>0 && strlen($value5)>0){
$query = 
}else if (strlen ($value1) >0 && strlen ($value4)>0 && strlen ($value5)>0){
$query =
}else  if (strlen ($value1)>0 && strlen ($value5)>0){
$query =
	}else if (strlen($value1)>0 && strlen ($value2)>0){
$query = 
}else if (strlen($value2)>0 && strlen ($value3)>0){
$query = 
}else if (strlen ($value3)>0 &&  strlen ($value4)>0){
$query = 
}else if (strlen ($value1)>0 && strlen ($value3)>0){
$query = 
}else if (strlen ($value3)>0 && strlen ($value5)>0){
$query = 
}else if (strlen($value2)>0  && strlen ($value1)>0){
$query 
}else if (strlen ($value4)>0 && strlen ($value5)>0 ){
$query = 
}else if (strlen ($value4)>0 && strlen ($value1)>0){
$query 
}else if (strlen ($value2)>0 && strlen ($value5)>0){
$query = 
}else if (strlen ($value1)>0){
$query =							    
}else if (strlen($value2)>0 ){
$query =
}else if (strlen ($value3) >0){
$query = 
}else if ( strlen ($value4)>0){
$query = 
}else if (strlen ($value5)>0){
$query = 
} else {						
echo ' There was an error. Please try again';
exit(); 
}

Posted: Tue Mar 14, 2006 10:35 pm
by feyd
the logic appears quite, well, insane. But it's hard to say for sure if I can't really see how they affect your queries..

Posted: Wed Mar 15, 2006 7:48 pm
by BZorch
If you have five fields in a form and some can be left blank, how would you suggest the logic be for figuring out which query to run? I think logically it makes sense to say if this value is present and this value are present run this query, but since I am an amateur there obviously may be a better way to do it.

Some insight into why you think it is insane would be very helpful.

Posted: Wed Mar 15, 2006 7:57 pm
by feyd
You'll need to post some of the queries. As it stands, I have no idea how the if insanity affects how the query is created. Maybe building the query dynamically would be better.

Posted: Wed Mar 15, 2006 9:52 pm
by BZorch
feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


I am always a little leery putting too much code online since I have no idea who will be reading this. This is a portion of my script that will help you understand what I am trying to do or least I hope so. I know most people do not like people to post long code in this forum. I did replace all of my variables with dummy variables, but that should not make a difference. I know this is a bit difficult.

 I do appreciate your patience.

Code: Select all

if (eregi (".[[]+-,]{3,30}$",stripslashes(trim($_GET['value5']))))

$value5=$_GET['value5']; //assign variable
$value5=str_replace (",","",$value5); //replace commas with whitespace
$value5=str_replace ("+"," +",$value5); //add white space in front of +
$value5=str_replace ("-"," -",$value5); //add white space in front of -
$value5_error_msg= FALSE;

}else{
	if (empty($_GET['value5'])){// if it is blank make it false 
		$value5_error_msg= FALSE;
		$value5 = FALSE;
	} else { //if it is invalid set error message
		$value5 = FALSE;
		$value5_error_msg= "Your search string submitted was not valid.";
		}
}

if (eregi (".[[]]{3,30}$",stripslashes(trim($_GET['value1'])))) {//validate value5
		$value1=$_GET['value1']; //assign variable
		$value1=str_replace (",","",$value1); //replace commas with whitespace
		$value1_error_msg= FALSE;
}else{
		if (empty($_GET['value1'])){// if it is blank make it false 
			$value1_error_msg= FALSE;
			$value1 = FALSE;
		} else { //if it is invalid print error message
			$value1 = FALSE;
$value1_error_msg= "Your search string submitted was not valid.";
		}
}

if (eregi (".[[]]{3,30}$",stripslashes(trim($_GET['value2'])))) {//validate value5
		$value2=$_GET['value2']; //assign variable
		$value2=str_replace (",","",$value2); //replace commas with whitespace
		$value2_error_msg= FALSE;
}else{
		if (empty($_GET['value2'])){// if it is blank make it false 
			$value2_error_msg= FALSE;
			$value2 = FALSE;
		} else { //if it is invalid print error message
			$value2= FALSE;
$value2_error_msg= "Your search string submitted was not valid.";
		}
}

if (eregi (".[[]]{3,30}$",stripslashes(trim($_GET['value3'])))) {//validate value5
		$value3=$_GET['value3']; //assign variable
		$value3=str_replace (",","",$value3); //replace commas with whitespace
		$value3_error_msg= FALSE;
}else{
		if (empty($_GET['value3'])){// if it is blank make it false 
			$value3_error_msg= FALSE;
			$value3 = FALSE;
		} else { //if it is invalid print error message
			$value3= FALSE;
$value3_error_msg= "Your search string submitted was not valid.";
		}
}

if (eregi (".[[]]{3,30}$",stripslashes(trim($_GET['value4'])))) {//validate value5
		$value4=$_GET['value4']; //assign variable
		$value4=str_replace (",","",$value4); //replace commas with whitespace
		$value4_error_msg= FALSE;
}else{
		if (empty($_GET['value4'])){// if it is blank make it false 
			$value4_error_msg= FALSE;
			$value4 = FALSE;
		} else { //if it is invalid print error message
			$value4= FALSE;
$value4_error_msg= "Your search string submitted was not valid.";
		}
}

//check if there are any error messages and print them or move to query 

if ($value5_error_msg OR $value1_error_msg OR $value2_error_msg OR $value3_error_msg OR $value4_error_msg){
	echo '<div id="results_wrapper">';
		if ($value5_error_msg) {echo  $value5_error_msg;} else {echo '';} 
		if ($value1_error_msg) {echo  $value1_error_msg;} else {echo '';} 
		if ($value2_error_msg) {echo  $value2_error_msg;} else {echo '';} 
		if ($value3_error_msg) {echo  $value3_error_msg;} else {echo '';} 
		if ($value4_error_msg) {echo  $value4_error_msg;} else {echo '';} 
		echo '</div>';
		include ('../included/footer.html');
		exit();

}else {//everything is ok set confirmation as true and then move on to query 
		
		$confirmation=TRUE;
}

if ($confirmation){//beginning of if query 

	require_once ('mysql_connect.php'); // Connect to the db.

	// Number of records to show per page:
	$display = 12;
	$value1=escape_data ($value1);
	$value2=escape_data ($value2);
	$value3=escape_data ($value3);
	$value4=escape_data ($value4);
	$value5=escape_data ($value5);
	
// Determine how many pages there are. 
if (isset($_GET['np']) && eregi("[0-9]", $_GET['np']) && $_GET['np'] < 600) { 
	$num_pages = $_GET['np'];
	
} else { 
						
if (strlen($value1)>0 && strlen ($value2)>0 && strlen($value3)>0 &&  strlen($value4)>0 && strlen($value5)>0) { 

$query = "SELECT Full_File_Name, ID, File_Name FROM TEST_DB WHERE (Field1='$value1') AND (Field2='$value2') AND (Field3='$value3') OR (Field4='$value4' OR Field5='$value4' OR Field6='$value4') AND MATCH (Field6) AGAINST ('$value5' IN BOOLEAN MODE) ORDER BY MATCH (Field6) AGAINST  ('$value5' IN BOOLEAN MODE)";
						
}else if (strlen($value1)>0 && strlen ($value2)>0 && strlen($value3)>0 &&  strlen($value4)>0){ 

query = "SELECT Full_File_Name, ID, File_Name FROM TEST_DB WHERE (Field1='$value1') AND (Field2='$value2') AND (Field3='$value3') OR (Field4='$value4') OR (Field5='$value4') OR (Field6='$value4') ORDER BY (Field1)"; 

}else if (strlen($value2)>0 && strlen ($value3)>0 && strlen ($value4) >0 && strlen ($value5)>0){

$query = "SELECT Full_File_Name, ID, File_Name FROM TEST_DB WHERE (Field2='$value2') AND (Field3='$value3') OR (Field4='$value4' OR Field5='$value4' OR Field6='$value4') AND MATCH (Field6) AGAINST ('$value5' IN BOOLEAN MODE) ORDER BY (Field2)";
	
}else if (strlen ($value4) >0 && strlen ($value5)>0 && strlen ($value1)>0 && strlen ($value2) >0){
		
$query = "SELECT Full_File_Name, ID, File_Name FROM TEST_DB WHERE (Field1='$value1') AND (Field2='$value2') OR (Field4='$value4' OR Field5='$value4' OR Field6='$value4') AND MATCH (Field6) AGAINST ('$value5' IN BOOLEAN MODE) ORDER BY MATCH (Field6) AGAINST  ('$value5' IN BOOLEAN MODE)";

}else if (strlen ($value2)>0 && strlen ($value3)>0 &&  strlen($value1)>0 && strlen ($value5)>0){
		
$query = "SELECT Full_File_Name, ID, File_Name FROM TEST_DB WHERE (Field1='$value1') AND (Field2='$value2') AND (Field3='$value3') AND MATCH (Field6) AGAINST ('$value5' IN BOOLEAN MODE) ORDER BY MATCH (Field6) AGAINST  ('$value5' IN BOOLEAN MODE)";

// I then continue as I posted before creating a query for every combination.  With 25 combinations it is getting crazy. The rest of the script is a simple pagination and while() loop to post results of query.

feyd | Please use

Code: Select all

and

Code: Select all

tags where appropriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Wed Mar 15, 2006 9:58 pm
by feyd
it looks to me like you could generate the query string instead of hardcoding however many versions of the same query that are only minutely different.

Posted: Fri Mar 17, 2006 9:27 pm
by BZorch
Thanks for the tip. I will try and work on a dynamic version. I imagine what your talking about is when I set the variable after validating it that I would then set it to what is should look like in the query and then put them together into a single variable that represents the query. So instead of a two step process it cuts it down to one. Please let me know if this is not what you meant.

Posted: Sat Mar 18, 2006 9:17 am
by BZorch
If a user leaves the field blank in the form. How can I account for this in a single query?

I have tried this as an example. The user fills in String1 and String2, but leaves 3,4,5 which would have searched Field 3,4, and 5. When all are entered this query works fine. When anything is left blank and the value for that field is changed to % for the query it results in the following query that does not work. I have tried to use the * in the where clause instead of the %, but it does not work either. Is there a way to say that anything in the blank field will be selected?

SELECT Field1, Field2, Field3, Field4, Field5 FROM Test_DB WHERE (Field1='String1_entered' ) AND (Field2='%' ) AND (Field3='%') AND (Field4='%' OR Field4='%' OR Field4='%') AND MATCH (Field2) AGAINST ('String2_entered' IN BOOLEAN MODE) ORDER BY MATCH (Field2) AGAINST ('String2_entered' IN BOOLEAN MODE)

Posted: Sat Mar 18, 2006 9:45 am
by feyd
Don't add it to the where clause. Simple enough.

Posted: Sat Mar 18, 2006 9:51 am
by BZorch
If I do not add it to the WHERE clause then I will have to do every instance won't I? Just like I did it before.