Searching more than 1 field with multiple words

[davcom1]

First off, hello all!

 

I want to improve our website's search but unfortunately don't know enough to get there!

 

I'm not a PHP programmer but I'm generally able to tweak things... 

 

Our search page has 3 options - All words, Any word, Exact. 

 

I want to concentrate on the default "All words" which is the default parameter.

 

The code is essentially as follows -

 

$gotcriteria=TRUE;
	$Xstext = mysql_escape_string($stext);
	$aText = split(" ",$Xstext);
	$aFields[0]="products.pId";
	$aFields[1]="products.pName";
	$aFields[2]="products.pDescription";
	$aFields[3]="products.pLongDescription";
	if($stype=="exact")
		$sSQL .= "AND (products.pId LIKE '%" . $Xstext . "%' OR ".getlangid("pName",1)." LIKE '%" . $Xstext . "%' OR ".getlangid("pDescription",2)." LIKE '%" . $Xstext . "%' OR ".getlangid("pLongDescription",4)." LIKE '%" . $Xstext . "%') ";
	else{
		$sJoin="AND ";
		if($stype=="any") $sJoin="OR ";
		$sSQL .= "AND (";
		for($index=0;$index<=3;$index++){
			//$sSQL .= "(";
			$rowcounter=0;
			$arrelms=count($aText);
			foreach($aText as $theopt){
				if(is_array($theopt))$theopt=$theopt[0];
				$sSQL .= $aFields[$index] . " LIKE '%" . $theopt . "%' ";
				if(++$rowcounter < $arrelms) $sSQL .= $sJoin;
			}
			//$sSQL .= ") ";
			if($index < 3) $sSQL .= "OR ";
		}
		$sSQL .= ") ";
	}

 

Here's the important bit - as far as I can see, the search looks at each of the 4 fields in the array individually?  So if the search text is "RED APPLE", to get a result for the "All Words" option will require that both "RED" AND "APPLE" will need to be present in one of the searched fields...?

 

What I want to achieve is that if the product name is "RED FRUIT" and the description mentions "APPLE", then an "All words" search for "RED APPLE" will pick this up.

 

I'd guess that the easiest way would be to string the table fields together for searching but I don't know how to do this as a query... now I'm thinking I'll get flamed for not posting this in the MySQL forum too!!  :-\

 

Thanks in advance for any help!

 

 

 

[QuickOldCar]

Look into using fulltext search , as it will tailor to your needs more versus using like.

 

You can use a checkbox stating which type of search you want, have one set up as a default if no search type selected.

 

Have multiple queries using if/else or a switch statement that will call upon a specific mysql query depending on the search type..

[QuickOldCar]

I found another post I responded to explaining it in more detail with some example fulltext queries that I use.

You should create fulltext indexes in mysql for any AND,OR values you will be looking within to speed the search queries up.

 

http://www.phpfreaks.com/forums/index.php?topic=337024.msg1588290#msg1588290

 

I might as well mention sphinx search as well.

[davcom1]

Hi QOC,

 

Many thanks for the 2 rapid responses - that's really good of you.

 

To be honest, it all looks beyond my capabilities tbh!

 

The search page already has a drop-down selector for the search type and "All words" is the default which works best for our data.

 

I was hoping there was just a simple way of searching the 4 fields which contain the relevant data as if they were one long string.

 

Seems like we'll need to find a programmer! 

 

David