whole words in a string

how to query in MYSQL

[selomelo]

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.

[proper_bo]

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)

[txbakers]

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'

[slade7]

Sounds like you need to use a FULLTEXT index - if you have room or the resources for it.

[dev.mysql.com...]

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.

[slade7]

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.

[selomelo]

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]