homepage Welcome to WebmasterWorld Guest from 54.166.105.24
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
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
selomelo

5+ Year Member



 
Msg#: 498 posted 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.

 

proper_bo

10+ Year Member



 
Msg#: 498 posted 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)

txbakers

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



 
Msg#: 498 posted 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'

slade7

10+ Year Member



 
Msg#: 498 posted 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.

[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

10+ Year Member



 
Msg#: 498 posted 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.

selomelo

5+ Year Member



 
Msg#: 498 posted 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