|whole words in a string|
how to query in MYSQL
| 1:47 pm on May 2, 2006 (gmt 0)|
My problem is this:
I am trying to query a dictionary database for partial matches regardless of the position of the word(s) searched.
Let us assume that there are multiple entries for the word "bird" like:
entry 1: "a babe in arms"
Entry 2: "a babe in the woods"
Now, when I use LIKE, and the user enters "babe," no matches found, since the query does not start with "b"
What I trying to achieve:
When I search "bird", all strings containing the term bird should be displayed (in our example, 2 results).
Or when I search "woods" entry 2 should be displayed.
How can I accomplish this with MYSQL (php)?
Please note that I am a nevbie.
Thank you in advance.
| 1:56 pm on May 2, 2006 (gmt 0)|
WHERE entry LIKE '%babe%'
OR entry LIKE 'babe%'
OR entry LIKE '%babe';
that would match the start middle or end of an entry but beware that it would also match words that have babe in them. (babey / bababe etc)
| 2:10 pm on May 2, 2006 (gmt 0)|
I would have a field with keyword, then other fields with def1, def2, def3, ef4, etc.
"Bird" is no where in your definitions,which would make it hard to find.
If you had "bird" in a keyword column, then you could query:
SELECT * from DEFS where keyword='bird'
| 2:14 pm on May 2, 2006 (gmt 0)|
Sounds like you need to use a FULLTEXT index - if you have room or the resources for it.
See the part about sorting rows in decreasing order of relevance - I use that little query structure regularly and it works pretty well for me.
| 2:19 pm on May 2, 2006 (gmt 0)|
BUT... I typically use that for user input tables like comments, etc. Usually these do NOT have hundreds of thousands of rows.
I don't know how efficient it is on much larger tables - txbakers would probably know this.
| 6:17 pm on May 2, 2006 (gmt 0)|
Thank you very much for all the posts.
Well, the simplest solution would be LIKE "%babe%" approach. But the navigation leads to a problem here, since each letter in the navigation bar (A-Z) is treated as a query string as well. However, there is a roundabout solution for this: It is possible to solve this problem by an additional IF.
Nevertheless, I will try FULLTEXT and post the results here.
Thank you again.
[edited by: trillianjedi at 11:46 am (utc) on May 3, 2006]
[edit reason] Fixing word-trapped typo ;) [/edit]