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*******@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.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*******@gmail.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.'%interior%' 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($searchTerms as $term)
$query .= "key_word like '%" . $term . "%' and ";

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

[Carl]

kiqyou_vf wrote:

I'm sorry, I guess I don't understand.'%interior%' 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?

I'm not sure I understand what you are asking here, but ill give it
another try based on my understaning of your first post.

If you search using a clause like "%home interior%", you will find
matches that have the phrase "home interior" in the field. If you search
for "'%home%' AND '%interior%' you will get matches that have the
_words_ 'home' and 'interior' in the field, though not necessarily in
that order.

for example:
Select * from table where field like '%home interior%'
matches "new home interior blah" but not "interior ideas for your home".

Select * from table where field like '%home%' and field like '%interior%'
will match both.

I hope that helps.
Carl.

[kiqyou_vf]

Thats it Zeldor, I'm going to try that out.

[Sergej Andrejev]

Take a chance and try my code
<?
// GET search query
$where = trim($_GET['where']);

// some vars
$search_col_name = "col";
$select_collumns = "*";
$select_tables = "table1";

// remove all special chars
$where = ereg_replace("[^0-9a-zA-Z ]", "", $where);

// procced first var
$where = preg_replace("/^[ ]*(\w)+/", "%$1%", $where);
// replace all AND into SQL-like expressions
$where = preg_replace("/ and (\w)+/", "' AND $search_col_name LIKE
'%$1%", $where);
// replace all OR into SQL-like expressions
$where = preg_replace("/ or (\w)+/", "' OR $search_col_name LIKE
'%$1%", $where);
$where = "WHERE $search_col_name LIKE '".$where."';";
$sql_query = "SELECT $select_collumns FROM $select_tables ".$where;
echo $sql_query;
?>

[kiqyou_vf]

what does "%$1%" do?

[Sergej Andrejev]

Try to google "perl regular expressions". I'm sure it'll be easy to find