Jump to content

What's the best way create a search for keywords in specific mysql fields?


simcoweb

Recommended Posts

I need a simple search that will query two fields in a membership database, 'details' and 'specialties', where these two are in separate tables. I've done some research on the various methods. For example, a simple query:

[code]SELECT * FROM Table_Name WHERE MATCH(field2) against(`search string`);[/code]

Or:

[code]SELECT field1, field2 FROM Table_Name WHERE field2 LIKE '%search_string%' [/code]

Basically this should be a 'keyword' match type thing then return specific info from the query into links to the member's profile that contain the results.

So, say they want a chiropractor and enter that as their keyword. It should query the fields and return the results displayed like:

1. Dr. Robert Jones - Chiropractic Care
2. Dr. Billy Bob Thornton - Chiropractic Center
etc.

With each linking to their respective member profile. To avoid hours of coding in the wrong direction i'm asking for some help in getting this started so I can get 'er done :) Thanks in advance!
Link to comment
Share on other sites

Assuming
[pre]
profile            specialty
--------            ---------------
member_id  -------  member_id
membername          specialty
details
[/pre]

then
[code]<?php
$keyword = 'chiro';

$sql = "SELECT DISTINCT p.membername, p.details
        FROM profile p
        INNER JOIN specialty s ON p.member_id = s.member_id
        WHERE p.details LIKE '%$keyword%'
            OR s.specialty LIKE '%$keyword%' ";
?>[/code]
Link to comment
Share on other sites

Barand:

Can the $keyword variable just be a simple $_POST['keyword']; call? The search form is basically:

[quote]<input type='text' size='20' name='keyword'>[/quote]

Or is there some special tags I have to use to summon it to work with the %keyword% tags?
Link to comment
Share on other sites

Ok, through some tweaking to get the actual database parameters and field names correct I got the search working for the most part and displaying the results. However, here's the rest of the puzzle now that I see if work.

If I type in a single word and the single word matches a 'specialties' in the database that is also a single word then it returns a result. However, if the single word search word matches against a sentence in the 'specialties' field then it returns nothing. To clarify, it only returns a result for a single word search IF the item is finds is also a single word. Here's an example:

a search for 'wangers' comes up with a member specializing in 'wangers' as 'wangers' is the only word in his specialties field

If I search for 'bananas' which is mentioned in 5 member profiles in the 'specialties' field but as part of sentences like 'I eat bananas' it returns nothing.

The search should display the results of any member matching the keywords from their 'details' and 'specialties' fields. Is there an adjustment to make to this to do so?

Here's the working query:

[code]<?php
$sql = "SELECT DISTINCT p.firstname, p.lastname, p.title, p.comments, p.memberid FROM plateau_pros p INNER JOIN specialties s ON p.memberid = s.memberid WHERE p.comments LIKE '%$keyword%' OR s.specialties LIKE '%$keyword%'";
$results = mysql_query($sql) or die(mysql_error());
?>[/code]

You can test this to see what I mean at [url=http://www.plateauprofessionals.com/search.php]http://www.plateauprofessionals.com/search.php[/url]

The specialites fields, for example, contain these items:

        ID    MEMBERID    SPECIALTIES
3      65      I eat bananas faster than any monkey.
4 66 I eat bananas
5 67 I eat bananas
6 68 eating bananas
7 69 eat bananans
8 70 eat bananas

Searching for 'bananas' returns a fat 0.
Link to comment
Share on other sites

Hmmm...nope. Added them and still produces nothing if I search for 'bananas' which occurs in 6 places. Still, if I search for 'wangers' I get a result. The word 'wanger' is the only word in that field for that member.

I read something on this that mentioned 'Full Text' searches. Here's the summary. Maybe this sheds light on it or sparks an idea.

[quote]MORE ON FULL-TEXT...

Full-Text is a type of index created on a table. The Full-Text index basically creates an index of all of the words of the field specified in your table. Each entry in this index references a row in your table. These indexes are created in separate index files by MySQL. When you perform a search against the Full-Text index, the MySQL Full-Text search functions use this index to return the related rows from the table back through the result set. Through this index, the search performance is enhanced through its speed in returning the results and could also give you the ability to sort the results according to its relevance.

SETTING UP THE DATABASE...

As an example let us use the table with the structure defined below:

CREATE TABLE `Table_Name` (
`field1` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`field2` VARCHAR( 100 ) NOT NULL
);

The field where we will match our search queries against is the string field2. to create the Full-Text index for field2 use the sql statement below:

ALTER TABLE Table_Name ADD FULLTEXT (field2);

THE QUERY...

With the database set, let us move on the query statement that would be used to return the resulting records from the search�.

SELECT * FROM Table_Name WHERE MATCH(field2) against(`search string`);

Full-Text searching uses the MATCH() and AGAINST() syntax. As parameters, MATCH() takes the list of all the field names from where we will �match� the search string into. While AGAINST() takes the search string. The search string can contain any phrase that you would like to search in database. Stopwords like `else`, `any`, `at`, `as` would not be considered by the full-text search. For a list of all the stopwords visit http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html[/quote]

Does this conjure up any ideas/solution? :)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.