simcoweb Posted October 22, 2006 Share Posted October 22, 2006 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 Care2. Dr. Billy Bob Thornton - Chiropractic Centeretc.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! Quote Link to comment Share on other sites More sharing options...
Barand Posted October 22, 2006 Share Posted October 22, 2006 Assuming[pre]profile specialty-------- ---------------member_id ------- member_idmembername specialtydetails[/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] Quote Link to comment Share on other sites More sharing options...
simcoweb Posted October 22, 2006 Author Share Posted October 22, 2006 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? Quote Link to comment Share on other sites More sharing options...
Barand Posted October 23, 2006 Share Posted October 23, 2006 The "%" are just wildcard characters Quote Link to comment Share on other sites More sharing options...
simcoweb Posted October 23, 2006 Author Share Posted October 23, 2006 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 fieldIf 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 bananasSearching for 'bananas' returns a fat 0. Quote Link to comment Share on other sites More sharing options...
Barand Posted October 23, 2006 Share Posted October 23, 2006 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%') Quote Link to comment Share on other sites More sharing options...
simcoweb Posted October 23, 2006 Author Share Posted October 23, 2006 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? :) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.