Search multiple mysql columns

Question

I have a table like this:

+-------------------+---------------+
| description       | colour        |
+-------------------+---------------+
| Macrame Dress     | Washed Black  |
| Darcelle Gilet    | Dirty Shellac |
| Darcelle Cardigan | Washed Black  |
| Let It Rot Vest   | Optic White   |
| Let It Rot Crew   | Washed Black  |
| Let It Rot Crew   | Optic White   |
| Battalion Short   | Somme         |
| Seine Dress       | Washed Black  |
| Seine Dress       | Cocomotion    |
| Odette V-neck     | Linen Marl    |
+-------------------+---------------+

 I want to find it for a black dress, and it returns lines 1 and 8. If I type black, it will return lines 1, 3, 5 and 8. The dress should return lines 1, 8 and 9.

Can anyone think of a really elegant, beautiful sql bit that will accept any number of search words and return the most concise result. I can come up with some pretty ugly ways to do this, but they upset my karma.

Answer 1

(-), , FULLTEXT. :

SELECT * FROM mytable
WHERE MATCH (description,color) AGAINST ('black dress');

, FULLTEXT MyISAM, .

, , , . MyISAM :

CREATE TEMPORARY TABLE mytmptable ENGINE=MyIsam SELECT * FROM mytable;
ALTER TABLE mytmptable ADD FULLTEXT KEY (description,color); 
SELECT * FROM mytmptable WHERE MATCH (description,color) AGAINST ('black dress');

Answer 2

where colour+description like '%s1%'

Answer 3

, , : (s1) (s2).

:

select * from tbl where description like '%(s1)%' and colour like '%(s2)%'

, , s1 s2.

s2 %%, ( ).

, -

select * from tbl
    where description + colour like '%(s1)%'
      and description + colour like '%(s2)%'
      and description + colour like '%(s3)%'

" ", ( " " s1 s2, s1). " + " - ; SQL , , , .

Answer 4

? VARCHAR ,

select 
  * 
from 
  tbl 
where 
  description like '%(s1)%' 
  or colour like '%(s1)%'

( @Pax, , , - , , "" "".)

Answer 5

, PHP , :

SELECT * FROM tbl
WHERE (description LIKE '%black%' OR colour LIKE '%black%')
AND (description LIKE '%dress%' OR colour LIKE '%dress%')

... where you add one of these conditions in square brackets for each word in the search expression and combine them with an AND. This requires a match in at least one of the columns for each word in the search expression.

This is not pure SQL (the actual query will depend on the number of words in the search expression), but it feels quite elegant, and I think it should do the job.

Answer 6

$node=explode(" ",$keysearch);
$sql="SELECT * FROM tbl_dress WHERE ";
$x=0;
foreach ($node as $key => $ns) {
    $x++;
    if ($x > 1){$sql.=" AND ";}
    $sql.="(CONCAT(description, colour) LIKE '%$ns%')";
}