Search mysql db with multiple keywords

I am trying to figure out how to search 3 columns in a mysql table with multiple keywords.

So if I get - Blue book apple – from a text field I want to search my db and return records that contain these keywords.  I want the users to be able to enter the search criteria from a text field; this means I don’t know the number of search terms.  

I am really stuck on this one?
LVL 1
bliesveldAsked:
Who is Participating?
 
SaoiCommented:
For a quite rudimentry search engine, first I'd specify that the search terms must be seperated with a " " (space)

if($SearchTerms){
  $Keywords=explode(" ", $SearchTerms);
  foreach($Keywords as $Keyword){
    // run your sql search query (some thing that returns the ID of the record it matches) and add results to an array for handling later
    // this is likely to return the same result more than once so I would increment the value of the array variable with every result
    // (therefore the results with MORE of the words will have a higher number, allowing us to show results with a higher relevance.
    $Resource=mysql_query("SELECT ID FROM my_search_table WHERE my_search_field LIKE '%$Keyword%'");
    while($Results=mysql_fetch_array($Resource)){
      $SearchResults[$Results['ID']]+=1;
  }
}

//The contents of $SearchResults should look something like this:
Array
(
      [3] => 1  // Article ID 3 in the table matched one word in the search
      [15] => 5 // Article ID 15 matched 5 times
      [27] => 2 // matched 2 times
 )

Its then a fairly simle case of using that array to display your output. Hope that helps!

Saoi
0
 
webtransCommented:
can u explain this more
give a db design sample
0
 
bliesveldAuthor Commented:
Saoi  - this looks like what I am looking for... I am still learning php though? How exactly do I extract the data out of your $SearchResults Array?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.