Function to generate WHERE sql from user search terms.

carllagerfeld 1 Tallied Votes 457 Views Share

This is a function that I wrote and have re-used many times. I share it with you DaniWebers to use, laud or destroy!

In a nutshell, this takes a user's search box input phrase and generates a series of LIKE clauses for your SELECT statement. Looks like "col1 LIKE '%s_term1%' OR col1 LIKE '%s_term2%' OR col2 LIKE '%s_term1' .." The column names you want it to use is the second parameter of the function.

/* --------------db_sqlWhereSearch
DESC a generic function to support standard user site search box. Generates the WHERE sql needed to search a table.    
- supports double-quoted text for phrase search
- supports maximum search term count, gives precedence to quoted user text
RETURNS sql statement like "col1 LIKE '%s_term1%' OR col1 LIKE '%s_term2%' OR col2 LIKE '%s_term1' .."
PARAM1 $userSearchPhrase - string, the search terms entered by a user
PARAM2 $dbfields - array of column names in db to search to search against
function db_sqlWhereSearch($userSearchPhrase, $dbFields) {
	$sql_where = ""; // the return value
	$arr_likes = array(); // elements are sql LIKE clauses ie. "dbField LIKE '%searchTerrm%'"
	$reg_double = '~\"[^\"\r]*\"~'; //matches double-quoted text
	$reg_ws = '~\s+~'; //matches whitespace
	$maxTerms = 3; // the rest will be ignored
	$terms_all = array(); //collects valid, processed search terms, used to generate return sql
	$terms_quoted = array();
	$terms_unquoted = array();
	// get quoted subphrases from $userSearchPhrase and store in 2 dim array $terms_quoted
	preg_match_all($reg_double, $userSearchPhrase, $terms_quoted); 
	$terms_quoted = $terms_quoted[0]; //preg_match_all is annoying
	// remove quoted subphrases from $userSearchPhrase and trim ends
	$userSearchPhrase = preg_replace($reg_double ,'', $userSearchPhrase);
	$userSearchPhrase = trim($userSearchPhrase, "\" ");
	// get unquoted search terms
	if ($userSearchPhrase) //if empty str then leave $terms_unquoted empty
		$terms_unquoted = preg_split($reg_ws, $userSearchPhrase);
	// add quoted to $terms_all
	if (count($terms_quoted))
		$terms_all = array_merge($terms_all, $terms_quoted); // give precedence to quoted text
	// add unquoted to $terms_all
	if (count($terms_unquoted))
		$terms_all = array_merge($terms_all, $terms_unquoted); // give precedence to quoted text
	// chop $terms_all to maxTerms length
	array_splice($terms_all, $maxTerms);
	// trim search terms of crap
	foreach ($terms_all as &$s)
		$s = trim($s, "\" "); // remove all surrounding double-quotes and spaces

	// generate array of sql LIKE clauses needed for user search query	
	for ($i = 0; $i < count($terms_all); $i++) {
		for ($j = 0; $j < count($dbFields); $j++) 	{
			$arr_likes[] = "$dbFields[$j] LIKE '%$terms_all[$i]%'" ;
	$sql_where = implode(" OR ", $arr_likes);
	return $sql_where;