mysql multi-column search

mysql multi-column search

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 search it for "black dress" and it only return rows 1 & 8. If I type in "black", it will return rows 1, 3, 5 & 8. Dress should return rows 1, 8 & 9.

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

Answer by paxdiablo for mysql multi-column search

Ideally, I think you would have two separate search terms, one for description (s1) and one for color (s2).

Then your query becomes:

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

Substituting in the values for s1 and s2 of course.

A blank s2 would result in %% which should match everything (I think).

To be able to search on multiple terms in either field, you need something like

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

This will have to be constructed on the fly, based on the number of words in your search pattern (so "black dress" will have s1 and s2, black will just have s1). The "description + colour" bit is concatenated fields; my SQL is a bit rusty so I don't know how you'd do that exactly but the concept is sound.

Answer by cg. for mysql multi-column search

The only nice solution (karma-wise) I can think of would involve using a FULLTEXT index. This would allow you to make use of queries like this:

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

Unfortunately, FULLTEXT indexes require the MyISAM table type, if I recall correctly.

EDIT

I doubt that this is what you are looking for but let me add it for the sake of discussion. You could duplicate your data into a temporary MyISAM table and do you full text search on that:

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 by chryss for mysql multi-column search

What data type are your columns? If they are VARCHAR or similar, you'd simply do

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

(This is adapted from @Pax, who I believe misunderstood the question -- I take it to mean it should match whenever the search term appears in 'description' or 'colour'.)

Answer by Learning for mysql multi-column search

where colour+description like '%s1%'  

Answer by Nick F for mysql multi-column search

Well, as long as you can use PHP to assemble the query, this should work:

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

...where you add one of those bracketed conditions for each word in the search term, and connect them with "AND". This requires a match in at least one of the columns for each of the words in the search term.

It's not pure SQL (the actual query will depend on how many words are in the search term), but it feels reasonably elegant and I think it should do the job.

Answer by zatanabee for mysql multi-column search

$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%')";  }  

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72