7 Replies - 581 Views - Last Post: 05 November 2011 - 01:37 PM Rate Topic: -----

#1 hampee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 25-October 11

Search thru multiple columns

Posted 25 October 2011 - 12:03 PM

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;
	}


Is This A Good Question/Topic? 0
  • +

Replies To: Search thru multiple columns

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Search thru multiple columns

Posted 25 October 2011 - 02:24 PM

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;
}


Was This Post Helpful? 1
  • +
  • -

#3 hampee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 25-October 11

Re: Search thru multiple columns

Posted 26 October 2011 - 12:13 AM

View Poste_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 :(
Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Search thru multiple columns

Posted 26 October 2011 - 01:10 PM

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

Was This Post Helpful? 0
  • +
  • -

#5 hampee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 25-October 11

Re: Search thru multiple columns

Posted 27 October 2011 - 03:23 AM

View Poste_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.
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Search thru multiple columns

Posted 27 October 2011 - 01:25 PM

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

Was This Post Helpful? 0
  • +
  • -

#7 hampee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 25-October 11

Re: Search thru multiple columns

Posted 05 November 2011 - 03:39 AM

Still cant get it to work
Was This Post Helpful? 0
  • +
  • -

#8 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 801
  • View blog
  • Posts: 1,700
  • Joined: 30-January 09

Re: Search thru multiple columns

Posted 05 November 2011 - 01:37 PM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1