Page 1 of 1

Advanced Database Search

Posted: Wed Jun 23, 2010 7:26 am
by lenton
What I basically need is a good search engine system.

These are the three values stored in my database:
- green plus red socks
- really big socks
- red socks

When the user searches the input 'red and green socks' I want the database items to appear in the order of how many matching keywords they contain:
- green plus red socks = 3 matches
- red socks = 2 matches
- really big socks = 1 match

This is the code that I have at the moment but it doesn't do what I need it to do:

Code: Select all

$input = explode(" ", "red and green socks"); // Explode string into key words
Foreach($input as $item)
{
  $keywords = $keywords . "'%$item%' OR ";
}
$keywords = substr_replace($words ,"",-3); // Remove last 'OR'

$result = mysql_query("SELECT * FROM brain WHERE input LIKE $keywords ORDER BY rand() LIMIT 1");
$count = mysql_num_rows($result);
if($count > 0)
{
  while($row = mysql_fetch_array($result)) // Get output from light search
  {
    $output = $row['output'];
  }
}
Do you have any ideas on how to make it order the results by how many keywords are matched. Thank you VERY much for your help.

Re: Advanced Database Search

Posted: Wed Jun 23, 2010 10:51 am
by andyhoneycutt
I'm wondering if you could do this on the mysql end using MATCH and Relevance. Taken from mysql Fulltext searching: Second post after documentation...
SELECT MATCH('Content') AGAINST ('keyword1
keyword2') as Relevance FROM table WHERE MATCH
('Content') AGAINST('+keyword1 +keyword2' IN
BOOLEAN MODE) HAVING Relevance > 0.2 ORDER
BY Relevance DESC

Re: Advanced Database Search

Posted: Wed Jun 23, 2010 11:07 am
by lenton
Maybe all I need is ORDER BY Relevance DESC ?

Re: Advanced Database Search

Posted: Wed Jun 23, 2010 11:26 am
by andyhoneycutt
Yea, you wouldn't need to run the exact query posted, but I think you could modify it to suit your needs.

Re: Advanced Database Search

Posted: Wed Jun 23, 2010 2:55 pm
by lenton
Ok, thanks for your help