[Resolved] Advanced search w/ and or options

[dleone]

All - I have a generic search function that takes the string a user inputs on a website, $criteria, passes it to a query (below) and returns rows that either the item or description contains the term.

	$criteriaUP = strtoupper($criteria);
	$criteriaLW = strtlower($criteria);
	$criteriaUF = ucfirst((strtolower($criteria));
	$criteriaUW = ucwords(strtolower($criteria));


  $query = "SELECT image_4,  
                   item_no,
                   title,
                   price,
                   description
      FROM remains
      WHERE (description like '%$criteria%'
      OR title like '%$criteria%'
		 OR description like '%$criteriaUP%'
      OR title like '%$criteriaUP%'          
		 OR description like '%$criteriaLW%'
      OR title like '%$criteriaLW%'          
		 OR description like '%$criteriaUF%'
      OR title like '%$criteriaUF%'          
		 OR description like '%$criteriaUW%'
      OR title like '%$criteriaUW%'          
      )
      AND visible =1";

So, if you input "cat" it will find Cat, cat, CAT, catch, etc, etc. However if you type in find "cat and dog" it will only find:
cat and dog, Cat And Dog, Cat and dog, etc etc etc.

I actually want it it break a string up, find the "and"s and "or"s, take the data and concantenate the search string. After looking over various string functions, I have found a way to do this. However, it looks like it will take a function like the above and turn it into something more than 10 times as long and complex.

Are there other options to this - keeping the code small and tight, or why re-invent the wheel if it is already there?
-Don

[dleone]

Ended up writing it myself. Here is the "and" version if you like...

<?php

function andquery($criteria,&$query){//begin function

	$length = strlen($criteria);
	$andsearch = strpos($criteria, " and ");		

	if ($andsearch === false) { // note: three equal signs
		 // not found...
		 if($query != "") //last chunk to concatenate...
		 	{ 
				//$criteriaA = substr($criteria, 0, $andsearch);
				//$criteriaAR = substr($criteria, ($andsearch+5), $length); //leftover to check
				$criteriaUP = strtoupper($criteria);
				$criteriaLW = strtolower($criteria);
				$criteriaUF = ucfirst(strtolower($criteria));
				$criteriaUW = ucwords(strtolower($criteria));
				$query .= " AND (description like '%$criteria%'
				 OR title like '%$criteria%'
				 OR description like '%$criteriaUP%'
				 OR title like '%$criteriaUP%'          
				 OR description like '%$criteriaLW%'
				 OR title like '%$criteriaLW%'          
				 OR description like '%$criteriaUF%'
				 OR title like '%$criteriaUF%'          
				 OR description like '%$criteriaUW%'
				 OR title like '%$criteriaUW%'          
				 )";
      }
		 else{//only if the item has no ands or ors...
				$criteriaUP = strtoupper($criteria);
				$criteriaLW = strtolower($criteria);
				$criteriaUF = ucfirst(strtolower($criteria));
				$criteriaUW = ucwords(strtolower($criteria));


				$query .= "SELECT image_4,  
									  item_no,
									  title,
									  price,
									  description
					 FROM remains
					 WHERE (description like '%$criteria%'
					 OR title like '%$criteria%'
					 OR description like '%$criteriaUP%'
					 OR title like '%$criteriaUP%'          
					 OR description like '%$criteriaLW%'
					 OR title like '%$criteriaLW%'          
					 OR description like '%$criteriaUF%'
					 OR title like '%$criteriaUF%'          
					 OR description like '%$criteriaUW%'
					 OR title like '%$criteriaUW%'          
					 )
					 AND visible =1";
      }
	}		
	else{
		//you got an AND search $andsearch = position of " and " in string...
		$criteriaA = substr($criteria, 0, $andsearch);
		$criteriaAR = substr($criteria, ($andsearch+5), $length); //leftover to check
		$criteriaUP = strtoupper($criteriaA);
		$criteriaLW = strtolower($criteriaA);
		$criteriaUF = ucfirst(strtolower($criteriaA));
		$criteriaUW = ucwords(strtolower($criteriaA));

				if($query == ""){
								$query = "SELECT image_4,  
												  item_no,
												  title,
												  price,
												  description
								 FROM remains
								 WHERE visible =1 AND (description like '%$criteriaA%'
								 OR title like '%$criteriaA%'
								 OR description like '%$criteriaUP%'
								 OR title like '%$criteriaUP%'          
								 OR description like '%$criteriaLW%'
								 OR title like '%$criteriaLW%'          
								 OR description like '%$criteriaUF%'
								 OR title like '%$criteriaUF%'          
								 OR description like '%$criteriaUW%'
								 OR title like '%$criteriaUW%'          
								 )";
				   }
					else{
						$query .= " AND (description like '%$criteriaA%'
						 OR title like '%$criteriaA%'
						 OR description like '%$criteriaUP%'
						 OR title like '%$criteriaUP%'          
						 OR description like '%$criteriaLW%'
						 OR title like '%$criteriaLW%'          
						 OR description like '%$criteriaUF%'
						 OR title like '%$criteriaUF%'          
						 OR description like '%$criteriaUW%'
						 OR title like '%$criteriaUW%'          
						 )";	
					 }

	if($criteriaAR !="") andquery($criteriaAR, $query);	
	}

// return $query;

}//end function

$criteria = "hot and dog and cat and hobo";
$myQ = "";

andquery($criteria, $myQ);

echo $myQ;

?>