Jump to content


Photo

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


  • Please log in to reply
6 replies to this topic

#1 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 22 October 2006 - 05:13 PM

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:

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

Or:

SELECT field1, field2 FROM Table_Name WHERE field2 LIKE '%search_string%'

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!

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 22 October 2006 - 06:36 PM

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

then
<?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%' ";
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 22 October 2006 - 11:33 PM

Barand:

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

<input type='text' size='20' name='keyword'>


Or is there some special tags I have to use to summon it to work with the %keyword% tags?

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 23 October 2006 - 06:10 AM

The "%" are just wildcard characters
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 23 October 2006 - 02:35 PM

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:

<?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());
?>

You can test this to see what I mean at http://www.plateaupr....com/search.php

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.

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 23 October 2006 - 05:48 PM

I haven't tested yet but does adding ()'s help?

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%')

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 23 October 2006 - 06:40 PM

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.

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...-stopwords.html


Does this conjure up any ideas/solution? :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users