multi-word query

[kiqyou_vf]

Hello all, I'm trying to figure out how to search a "key_words" column,
that contain key words (obviously) for that row. An example of what one
of the key_words contains is: "home interior decorating". I can search
for "home interior" or "interior decorating" and it will return the
correct row, but if I search for "interior home" or "home decorating" I
get nothing. I need it to return any row with ALL of those words in any
order. I am using WHERE LIKE '%search%'. I have done some searching,
but was never quite sure what to really search for. Is there an easy
way around this or can someone point me in the right direction? THANKS!

[Shelly]

"kiqyou_vf" <so*******@gmai l.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

Hello all, I'm trying to figure out how to search a "key_words" column,
that contain key words (obviously) for that row. An example of what one
of the key_words contains is: "home interior decorating". I can search
for "home interior" or "interior decorating" and it will return the
correct row, but if I search for "interior home" or "home decorating" I
get nothing. I need it to return any row with ALL of those words in any
order. I am using WHERE LIKE '%search%'. I have done some searching,
but was never quite sure what to really search for. Is there an easy
way around this or can someone point me in the right direction? THANKS!

WHERE key_words like %home% AND key_words like %interior% AND key_words like
%decorating%

[ZeldorBlat]

One option would be something like this:

If you're searching for (foo) and (bar)

select *
from my_table
where key_words like '%foo%'
and key_words like '%bar%'

[kiqyou_vf]

I'm sorry, I forgot to mention that the '%search%' is coming from a
form- $_GET['search']

[Carl]

kiqyou_vf wrote:

I'm sorry, I forgot to mention that the '%search%' is coming from a
form- $_GET['search']

You may want to use the explode() function to split the $_GET text into
and array of 'words' first. Then loop through this array of words adding
the '%' chars to the beginning and end of each word while building the
sql statement.

Carl.

[Tim Van Wassenhove]

On 2005-08-01, kiqyou_vf <so*******@gmai l.com> wrote:

Hello all, I'm trying to figure out how to search a "key_words" column,
that contain key words (obviously) for that row. An example of what one
of the key_words contains is: "home interior decorating". I can search
for "home interior" or "interior decorating" and it will return the
correct row, but if I search for "interior home" or "home decorating" I
get nothing. I need it to return any row with ALL of those words in any
order. I am using WHERE LIKE '%search%'. I have done some searching,
but was never quite sure what to really search for. Is there an easy
way around this or can someone point me in the right direction? THANKS!

Have a look at http://dev.mysql.com/doc/mysql/en/fulltext-search.html
--
Met vriendelijke groeten,
Tim Van Wassenhove <http://timvw.madoka.be >

[kiqyou_vf]

Damn, I was hoping that it wouldn't be the only way. I looked at that
earlier and couldnt understand anything. Are there any tutorials that
use fulltext search that you know of? I mean, if thats the way I have
to do it, then I'll do it but I was really hoping for a quicker
solution- or a work around. Anyway, thanks for pointing me in the
proper direction.

[Carl]

kiqyou_vf wrote:

Damn, I was hoping that it wouldn't be the only way. I looked at that
earlier and couldnt understand anything. Are there any tutorials that
use fulltext search that you know of? I mean, if thats the way I have
to do it, then I'll do it but I was really hoping for a quicker
solution- or a work around. Anyway, thanks for pointing me in the
proper direction.

Why wouldn't you just use multiple 'anded' tests when pattern matching?

"SELECT * FROM `table` WHERE `description` LIKE '%interior%' AND
`description` LIKE '%home%'"
Carl.

[kiqyou_vf]

I'm sorry, I guess I don't understand.'%in terior%' and '%home%' need to
come from a query entered into a text box, not a pre-defined link.

Is there a way I could put the multi-word search into an array,
seperated by spaces and make each item in the array print out " LIKE
'%item1+1%' " with a loop? Does that make sense?

[ZeldorBlat]

$searchTerms = explode(' ', $_GET['search']);
$query = 'select * from my_table where ';

foreach($search Terms as $term)
$query .= "key_word like '%" . $term . "%' and ";

$query = substr($query, 0, -4); //get rid of the last 'and'