homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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]

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved