• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1068
  • Last Modified:

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?
0
bliesveld
Asked:
bliesveld
1 Solution
 
webtransCommented:
can u explain this more
give a db design sample
0
 
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now