Welcome to WebmasterWorld Guest from 54.234.114.182

Forum Moderators: open

Message Too Old, No Replies

whole words in a string

how to query in MYSQL

     
1:47 pm on May 2, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:July 22, 2005
posts:151
votes: 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)

Junior Member

10+ Year Member

joined:Mar 8, 2004
posts:168
votes: 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)

Senior Member

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Sept 1, 2001
posts:4392
votes: 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)

Junior Member

10+ Year Member

joined:Nov 20, 2003
posts:132
votes: 0


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.

2:19 pm on May 2, 2006 (gmt 0)

Junior Member

10+ Year Member

joined:Nov 20, 2003
posts:132
votes: 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)

Junior Member

10+ Year Member

joined:July 22, 2005
posts:151
votes: 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]