[RESOLVED] Search by (all words, any word, exact phrase) - Query help!

[kpowning]

Ok guys I'm confused here. I'm trying to make an advanced search form for a website and I'm getting my self confused. I want visitors to be able to search by all three options. I have the following coding, is this how you would set this up or what would you guys recommend? I GREATLY appreciate any help on this!

I need this to work if someone searches with one word or multiple words. 🙂

	if ($wordtype == 'All'){

	$allwords = 'checked="checked"';

	$searchvalue = explode(" ",$searchvalue);
	for($i=0;$i<count($searchvalue);$i++){
		if($bytitle){
			$sorttitle = 'checked="checked"';
  			$aWhere[] = 'product LIKE "%' . $searchvalue[$i] . '%"';
		}
		if($byfulldescr){
			$sortfulldescr = 'checked="checked"';
  			$aWhere[] = 'fulldescr LIKE "%' . $searchvalue[$i] . '%"';
		}
		if($bysku){
			$sortsku = 'checked="checked"';
			$aWhere[] = 'productsku LIKE "%' . $searchvalue[$i] . '%"';
		}
	}
	$sWhere = 'WHERE ' . implode(' OR ', $aWhere);
	$sql = "" . $searchall . " " . $sWhere ." AND forsale = 'Y' GROUP BY productid";
}

if ($wordtype == 'Any'){

	$anyword = 'checked="checked"';
	$searchvalue = explode(" ",$searchvalue);
	for ($i=0;$i<count($searchvalue);$i++){
		$test .= ' +'.$searchvalue[$i];
	}
	if ($bytitle && $byfulldescr && $bysku){
		$sorttitle = 'checked="checked"';
		$sortfulldescr = 'checked="checked"';
		$sortsku = 'checked="checked"';

		$sql = "SELECT productid, price, product, descr, fulldescr FROM products
				WHERE MATCH (product, fulldescr, productsku)
				AGAINST ('$test' IN BOOLEAN MODE)";
	}
	if ($bytitle && $byfulldescr){
		$sorttitle = 'checked="checked"';
		$sortfulldescr = 'checked="checked"';

		$sql = "SELECT productid, price, product, descr, fulldescr FROM products
				WHERE MATCH (product, fulldescr)
				AGAINST ('$test' IN BOOLEAN MODE)";
	}
	if ($bytitle){
		$sorttitle = 'checked="checked"';

		$sql = "SELECT productid, price, product, descr, fulldescr FROM products
				WHERE MATCH (product)
				AGAINST ('$test' IN BOOLEAN MODE)";
	}
	if ($bysku){
		$sortsku = 'checked="checked"';
		$sql = "SELECT productid, price, product, descr, fulldescr FROM products
				WHERE MATCH (productsku)
				AGAINST ('$test' IN BOOLEAN MODE)";
	}
	if ($bytitle && $bysku){
		$sorttitle = 'checked="checked"';
		$sortsku = 'checked="checked"';

		$sql = "SELECT productid, price, product, descr, fulldescr FROM products
				WHERE MATCH (product, productsku)
				AGAINST ('$test' IN BOOLEAN MODE)";
	}
	if ($bysku && $byfulldescr){
		$sortfulldescr = 'checked="checked"';
		$sortsku = 'checked="checked"';

		$sql = "SELECT productid, price, product, descr, fulldescr FROM products
				WHERE MATCH (productsku, fulldescr)
				AGAINST ('$test' IN BOOLEAN MODE)";
	}
	if ($byfulldescr){
		$sortfulldescr = 'checked="checked"';
		$sql = "SELECT productid, price, product, descr, fulldescr FROM products
				WHERE MATCH (fulldescr)
				AGAINST ('$test' IN BOOLEAN MODE)";
	}
	echo $sql;
}

if ($wordtype == 'Exact'){

	$exactphrase = 'checked="checked"';

	if($bytitle)
		$sorttitle = 'checked="checked"';
  		$aWhere[] = 'product = "'.$searchvalue.'"';
	if($byfulldescr)
		$sortfulldescr = 'checked="checked"';
  		$aWhere[] = 'fulldescr = "'.$searchvalue.'"';
	if($bysku)
		$sortsku = 'checked="checked"';
		$aWhere[] = 'productsku = "'.$searchvalue.'"';

	$sWhere = 'WHERE ' . implode(' OR ', $aWhere);
	$sql = "" . $searchall . " " . $sWhere ." AND forsale = 'Y' GROUP BY productid";
}

[Weedpacket]

In the first case you'd need to make an array of all of the distinct words and generate an [font=monospace]"AND product LIKE '%....%'"[/font] condition for each one. Put parentheses around them in the query to group them together.

In the second case you'd do the same thing but use OR instead of AND.

The third case would be what you already have.

If AND/OR searches are going to be common you might find it more efficient to make a table linking each distinct word with the records that contain that word in its description. Save having all those LIKE conditions that are really going to hurt performance.

[johanafm]

If you're using MySQL, you could put the texts in a table which uses MyISAM as engine instead of InnoDb (but never use anything but innodb otherwise). This allows you to create a full text index on the text field and then use MATCH ... AGAINST.

As far as I know, all other DBMS handle full text indexing without gimping what's usually important (ACID). There's also the option to use a separate layer for full text indexing (e.g. Lucene, Sphinx). But I've never used them so I don't know how hard they are to get working. What I do know is they will usually take configuring to get anywhere near optimal for your particular application, even though they might perform ok out of the box.

[kpowning]

Weedpacket - Thx I ended up with the following. However how would you setup the php for a search input thats on everypage? Basically this is the coding on my advanced search page below, but how would you handle just a basic search from one input field and a submit thats on all pages?

This is my advanced search coding:

	if ($wordtype == 'All'){

	$allwords = 'checked="checked"';

	$searchvalue = explode(" ",$searchvalue);
	$aProduct = array();
	$aFulldescr = array();
	$aSku = array();
	$aquery = array();

	for($i=0;$i<count($searchvalue);$i++){
		if($bytitle){
			$sorttitle = 'checked="checked"';
  			$aProduct[]= "product LIKE '%$searchvalue[$i]%'";
		}
		if($byfulldescr){
			$sortfulldescr = 'checked="checked"';
  			$aFulldescr[]= "fulldescr LIKE '%$searchvalue[$i]%'";
		}
		if($bysku){
			$sortsku = 'checked="checked"';
			$aSku[]= "productsku LIKE '%$searchvalue[$i]%'";
		}
	}
	if (!empty($aProduct))
		$aquery[] = '('.implode(' AND ', $aProduct).')';
	if (!empty($aFulldescr))
		$aquery[] = '('.implode(' AND ', $aFulldescr).')';
	if (!empty($aSku))
		$aquery[] = '('.implode(' AND ', $aSku).')';


	for ($r=0;$r<count($aquery);$r++){
		if ($r == 0){
			$mainquery .= $aquery[$r];
		} else {
			$mainquery .= ' OR '.$aquery[$r];
		}
	}

	$sql = "".$searchall." WHERE ".$mainquery." AND forsale = 'Y' GROUP BY productid";
}

if ($wordtype == 'Any'){

	$anyword = 'checked="checked"';

	$searchvalue = explode(" ",$searchvalue);
	$aProduct = array();
	$aFulldescr = array();
	$aSku = array();
	$aquery = array();

	for($i=0;$i<count($searchvalue);$i++){
		if($bytitle){
			$sorttitle = 'checked="checked"';
  			$aProduct[]= "product LIKE '%$searchvalue[$i]%'";
		}
		if($byfulldescr){
			$sortfulldescr = 'checked="checked"';
  			$aFulldescr[]= "fulldescr LIKE '%$searchvalue[$i]%'";
		}
		if($bysku){
			$sortsku = 'checked="checked"';
			$aSku[]= "productsku LIKE '%$searchvalue[$i]%'";
		}
	}
	if (!empty($aProduct))
		$aquery[] = '('.implode(' OR ', $aProduct).')';
	if (!empty($aFulldescr))
		$aquery[] = '('.implode(' OR ', $aFulldescr).')';
	if (!empty($aSku))
		$aquery[] = '('.implode(' OR ', $aSku).')';


	for ($r=0;$r<count($aquery);$r++){
		if ($r == 0){
			$mainquery .= $aquery[$r];
		} else {
			$mainquery .= ' OR '.$aquery[$r];
		}
	}

	$sql = "".$searchall." WHERE ".$mainquery." AND forsale = 'Y' GROUP BY productid";
}

if ($wordtype == 'Exact'){

	$exactphrase = 'checked="checked"';

	if($bytitle)
		$sorttitle = 'checked="checked"';
  		$aWhere[] = 'product = "'.$searchvalue.'"';
	if($byfulldescr)
		$sortfulldescr = 'checked="checked"';
  		$aWhere[] = 'fulldescr = "'.$searchvalue.'"';
	if($bysku)
		$sortsku = 'checked="checked"';
		$aWhere[] = 'productsku = "'.$searchvalue.'"';

	$sWhere = 'WHERE ' . implode(' OR ', $aWhere);
	$sql = "" . $searchall . " " . $sWhere ." AND forsale = 'Y' GROUP BY productid";
}

This is my basic search coding. Would you change this?

	$searchvalue = mysql_real_escape_string($searchvalue);
	$searchvalue = str_replace(" ", "%", $searchvalue);
	$searchall = 'SELECT * FROM products WHERE product LIKE "%'.$searchvalue.'%" OR productsku LIKE "%'.$searchvalue.'%" AND forsale = "Y" GROUP BY productid';

[kpowning]

Actually I changed the basic to use one of the others I have for the advanced. Thx Weedpacket for the help. However, if you see anything I should change on my coding, let me know. Thx AGAIN! 🙂