Advanced Database Search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
lenton
Forum Commoner
Posts: 49
Joined: Sun Jun 20, 2010 6:45 am

Advanced Database Search

Post 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.
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Advanced Database Search

Post 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
User avatar
lenton
Forum Commoner
Posts: 49
Joined: Sun Jun 20, 2010 6:45 am

Re: Advanced Database Search

Post by lenton »

Maybe all I need is ORDER BY Relevance DESC ?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Advanced Database Search

Post by andyhoneycutt »

Yea, you wouldn't need to run the exact query posted, but I think you could modify it to suit your needs.
User avatar
lenton
Forum Commoner
Posts: 49
Joined: Sun Jun 20, 2010 6:45 am

Re: Advanced Database Search

Post by lenton »

Ok, thanks for your help
Post Reply