[hampee]

I need some help with a search function. I need a function that can search thru multible columns in a table and return the results with the row with most matches first. I got this code now but its only returning in the same order as they are in the database.

	function searchTables($table,$columns,$searchTermBits){
		$conditions = array();

		foreach($columns as $column){
			foreach($searchTermBits as $search){
				$conditions[] = $column . "='".mysql_real_escape_string($search)."'";
			}
		}
		
		$implode = implode(" OR ", $conditions);
		$result = mysql_query("SELECT * FROM $table WHERE $implode") or die ("kunde inte köra frågan searchTables");
		
		return $result;
	}

[e_i_pi]

The problem you have with that function is that it is only returning the rows that contain matches, it is not applying any sort of "best match" algorithm, or any sorting.

Try this function out. It returns the IDs of every row that contains at least one match, as well as the number of matches on that row. (Note: I haven't tested this, but it should work. If it doesn't, just post back and I'll run some thorough tests)

// Function to get the number of search term matches for rows in a table
// Rows with no matches are not returned
function searchTables($table,$columns,$searchTermBits){

	// First, declare an array to hold subqueries
	$query_parts = array();
	
	// Traverse the columns
	foreach($columns as $column)
	{
		// Traverse the search term bits
		foreach($searchTermBits as $search)
		{
			// Generate a subquery for this search term bit
			$query_parts[] = "SELECT `ID` FROM $table WHERE $column='" . mysql_real_escape_string($search) . "'";
		}
	}
	
	// UNION all the subqueries
	$imploded_query = implode(" UNION ", $query_parts);
	
	// Finally, use a superquery to get the complete result set
	$final_query = "SELECT `ID`, COUNT(*) FROM (" . $imploded_query . ") GROUP BY ID ORDER BY COUNT(*)";
	
	// Get the mysql result and return
	$result = mysql_query($final_query);
	return $result;
}

[hampee]

e_i_pi, on 25 October 2011 - 02:24 PM, said:

The problem you have with that function is that it is only returning the rows that contain matches, it is not applying any sort of "best match" algorithm, or any sorting.

Try this function out. It returns the IDs of every row that contains at least one match, as well as the number of matches on that row. (Note: I haven't tested this, but it should work. If it doesn't, just post back and I'll run some thorough tests)

// Function to get the number of search term matches for rows in a table
// Rows with no matches are not returned
function searchTables($table,$columns,$searchTermBits){

	// First, declare an array to hold subqueries
	$query_parts = array();
	
	// Traverse the columns
	foreach($columns as $column)
	{
		// Traverse the search term bits
		foreach($searchTermBits as $search)
		{
			// Generate a subquery for this search term bit
			$query_parts[] = "SELECT `ID` FROM $table WHERE $column='" . mysql_real_escape_string($search) . "'";
		}
	}
	
	// UNION all the subqueries
	$imploded_query = implode(" UNION ", $query_parts);
	
	// Finally, use a superquery to get the complete result set
	$final_query = "SELECT `ID`, COUNT(*) FROM (" . $imploded_query . ") GROUP BY ID ORDER BY COUNT(*)";
	
	// Get the mysql result and return
	$result = mysql_query($final_query);
	return $result;
}

Thanks for your help but I cant get i to work i only get some wierd mysql_fetch_array() error

[e_i_pi]

EDIT

Change line 23 to:

	$final_query = "SELECT `ID`, COUNT(*) AS SearchCount FROM (" . $imploded_query . ") AS SearchResults GROUP BY ID ORDER BY COUNT(*) DESC";

The query was failing because the derived table needed an alias.

This post has been edited by e_i_pi: 26 October 2011 - 01:42 PM

[hampee]

e_i_pi, on 26 October 2011 - 01:10 PM, said:

EDIT

Change line 23 to:

	$final_query = "SELECT `ID`, COUNT(*) AS SearchCount FROM (" . $imploded_query . ") AS SearchResults GROUP BY ID ORDER BY COUNT(*) DESC";

The query was failing because the derived table needed an alias.

I dont get any errors now but the SearchCount is always return 1.

[e_i_pi]

Strange. When I ran the query generated by the function, I got search counts equal to the number of hits.

There's a few things you'll have to check:

1) Are you expecting partial matches? Because this function only checks full matches. i.e. - if you search for "word", it will match against a row-column content of "word", but not "words" or "a word" for instance.

2) Are you utilising the fetched array properly? Try this code to see what the array contents actually are:

$result = searchTables($table,$columns,$searchTermBits);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
  var_dump($row);
}

This will output the array contents to the screen.

3) Dump the query and run it yourself in phpMyAdmin (or whatever your database management software is). Before the return statement in the function, add this line:

error_log($final_query);

Then go into the error log, copy the query string, paste it in the Query window of phpMyAdmin, and run it. See what sort of dataset you get returned.

-----

As I said, when I ran the function, it worked fine. So, it's either the way you're fetching the arrays, working with the arrays, or calling the function that the error is happening in

This post has been edited by e_i_pi: 27 October 2011 - 01:28 PM

[hampee]

Still cant get it to work

[e_i_pi]

Post your code that utilises the result from the searchTables function. It should be a block of code with mysql_fetch_array in it somewhere. I suspect that there is a bug in the way you are utilising the result set.