Solved

Search mysql db with multiple keywords

Posted on 2004-09-06
4
1,054 Views
Last Modified: 2013-12-12
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
Comment
Question by:bliesveld
4 Comments
 
LVL 5

Expert Comment

by:webtrans
ID: 11988099
can u explain this more
give a db design sample
0
 
LVL 7

Accepted Solution

by:
Saoi earned 500 total points
ID: 11988111
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
 
LVL 32

Expert Comment

by:ldbkutty
ID: 11988671
0
 
LVL 1

Author Comment

by:bliesveld
ID: 11993534
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now