CODEWITHSUNDEEP
mysqlsqlsearchsql-like
Mathieu
Mathieu

Reputation: 1175

Mysql AND search on multiple row with multiple keywords

Let's say I have this table :

id    |  field1  |  field2  |  field3  |  field4
 0    |  pear    |  peach   |  fruit   |  california
 1    |  apple   |  peas    |  xxxxxx  |  cupertino
 2    |  apple   |  peach   |  xxxxxx  |  san francisco

If the user search for "apple peach" I want to get the line

2    |  apple   |  peach   |  xxxxxx  |  san francisco

same result expected for the search "applepie san francisco" or "applepeach fran"

So what would be the mysql request to do that?

thanks

Upvotes: 1

Views: 242

Answers (4)

Yaroslav
Yaroslav

Reputation: 6544

You will be using some kind of parameters to fit your search. So:

SELECT `id`, `field1`, `field2`, `field3`, `field4`
  FROM Table1
 WHERE whatyouarelookingfor_1 IN (field1, field2, field3) 
   AND whatyouarelookingfor_2 IN (field1, field2, field3)

Edited to add the IN clause as previously it was only selecting from one field

That is an easy query, check this working SQL Fiddle code.

Upvotes: 1

Habibillah
Habibillah

Reputation: 28695

Try this query

SELECT * FROM TABLE_NAME WHERE CONCAT(field1, ' ', field2) = 'apple peach';

Upvotes: 1

xdazz
xdazz

Reputation: 160883

SELECT 
   * 
FROM 
   your_table
WHERE 
   'apple' IN (field1, field2, field3) 
   AND 'peach' IN (field1, field2, field3)

Upvotes: 4

slugonamission
slugonamission

Reputation: 9642

Simply enough, you want to check if field1 is "apple" and field2 is "peach", so WHERE field1='apple' AND field2='peach' will do it.

I get the feeling that what you want is a little more complex though. One solution might be to replace the spaces in the search terms with commas, then use the IN SQL operator to search within that.

Something like the following may therefore work:

SELECT ... FROM ... 
WHERE 
field1 IN ('apple','peach') OR 
field2 IN ('apple','peach') OR 
field3 IN ('apple','peach') OR  
field4 IN ('apple','peach')

Upvotes: 1

Related Questions