[SOLVED] Query problem

[Tsukiyomi]

I built a simple search for a client which is working beautifully so far except for one issue I can seem to solve and I've been having trouble these last two weeks hunting for a solution.

 

I've been using MATCH() AGAINST() to perform simple boolean searchs when I have multiple terms and thats fine, and using LIKE when I'm only searching a single word.  The problem with each method is it keeps finding words within words.

 

For example when they search "tea" it brings up words like "team", "instead" and "steady".  What my client wants is for it to only bring up whole words.  Is there any query keywords or formats I can use to ensure this?

 

Thank you.

[Ken2k7]

What do you mean by whole words? The three you listed aren't considered whole words? If that's the case, I have no idea what you mean by that. Please clarify.

[Tsukiyomi]

Sorry I should have explained that better.

 

I mean that if they search for the word "tea" I want it to only find the word "tea" and not words that happen to have the letters "t", "e" and "a" in them like "team", "instead" and "steady".

 

Is that clearer?

[warhead2020]

Sorry I should have explained that better.

 

I mean that if they search for the word "tea" I want it to only find the word "tea" and not words that happen to have the letters "t", "e" and "a" in them like "team", "instead" and "steady".

 

Is that clearer?

 

maybe im wrong in comprehending ur question. Based on ur question, y not just do like this..

 

SELECT * FROM TABLE WHERE COLUMN = 'TEA'

[fenway]

Um, no.... you'll need LIKE '%TEA%'

[Tsukiyomi]

Um, no.... you'll need LIKE '%TEA%'

 

Sorry.

 

My version is: MySQL 5.0.67

 

Here is my mysql query:

 

SELECT `id`, `content` FROM `mapContent` WHERE LOWER(`content`) LIKE '%tea%'

 

What is happening is that its grabbing words that happen to have "tea" inside of them such as "team", "instead" and "steady".

 

What my client wants is for it to only grab the word "tea".  For example if the record had.

 

"I like to drink tea in the morning"  <= it would grab this

 

But if it said:

 

"I want to drink coffee instead" <= it wouldn't grab this despite there being the letters "tea" in part of the text.

 

Is that descriptive enough?  I've tried using the like statement and I've also tried this:

 

 SELECT `id`, `content` FROM `mapContent` WHERE MATCH(`content`) AGAINST('+tea' IN BOOLEAN MODE)

 

And this:

 

 SELECT `id`, `content` FROM `mapContent` WHERE MATCH(`content`) AGAINST('+"tea"' IN BOOLEAN MODE)

 

I'm not getting any error messages, but i'm still not getting the desired result.

[rivan]

Did you try regexp like variant?

 

select * from mapcontent where lower(content) rlike '[[:<:]]tea[[:>:]]'

 

(I am not expert in mysql so I am not sure how its regexp support works, this is just a hint  )

[Tsukiyomi]

Did you try regexp like variant?

 

select * from mapcontent where lower(content) rlike '[[:<:]]tea[[:>:]]'

 

(I am not expert in mysql so I am not sure how its regexp support works, this is just a hint  )

 

Wow, I wasn't aware you could do regular expression in MySQL.  Interesting timing since I only recently started teaching myself how to use them in PHP and AS3.  I'll try that.

 

Thank you.

[Ken2k7]

Try:

SELECT id, content
FROM mapContent
HAVING LOCATE(' tea ', content) > 0
     OR LOCATE('tea ', content) > 0
     OR LOCATE(' tea', content) > 0;

 

The space on tea should separate it from words like instead. Though there could be a punctuation mark at the end of tea. Guess you'll have to think about that.

[fenway]

Yes, you'll need to match whole words.

[Tsukiyomi]

Just in case anyone else has a similar issue I got it to work with this:

 

$q = "SELECT `id`, `content` FROM `mapContent` WHERE LOWER(`content`) REGEXP \"[[:<:]]" . $searchText . "[[:>:]]\"";