CODEWITHSUNDEEP
phpmysql
stuart
stuart

Reputation:

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.

Upvotes: 5

Views: 1495

Answers (6)

zatanabee
zatanabee

Reputation: 1

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

Upvotes: 0

Nick F
Nick F

Reputation: 10102

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.

Upvotes: 0

cg.
cg.

Reputation: 3678

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');

Upvotes: 3

paxdiablo
paxdiablo

Reputation: 881103

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.

Upvotes: 0

Learning
Learning

Reputation: 8175

where colour+description like '%s1%'

Upvotes: 1

chryss
chryss

Reputation: 7519

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'.)

Upvotes: 0

Related Questions