[SOLVED] Query that Searches 2 Columns

[limitphp]

How would you write a query that searches for two words in two columns (songName and artistName)  using LIKE.

 

ex) lets say the two words are "doors" and "stones".

but I want it to check to make sure both words must occur in songName and/or both words must occur in artistName.

 

to simplify, lets say both songName and artistName are in the songs table.

 

Select * from songs where artistName LIKE '%doors%' AND artistName LIKE '%stones%' OR songName LIKE '%doors%' AND songName LIKE '%stones%'

 

what I'm confused about is when to use or and when to use and.

[Boo-urns]

That query looks correct to me. I think visually it would help if you change it to:

 

SELECT * FROM songs WHERE (artistName LIKE '%doors%' AND artistName LIKE '%stones%') OR (songName LIKE '%doors%' AND songName LIKE '%stones%');

 

I didn't test it but that should help solve your confusion.

[limitphp]

That query looks correct to me. I think visually it would help if you change it to:

 

SELECT * FROM songs WHERE (artistName LIKE '%doors%' AND artistName LIKE '%stones%') OR (songName LIKE '%doors%' AND songName LIKE '%stones%');

 

I didn't test it but that should help solve your confusion.

 

ah...I see.

So, that last AND won't cause it to match only records where it matched artistName AND where songName is LIKE stones?

[Boo-urns]

From what you're saying make sure you change the second part of what you're matching to "songName LIKE" as currently it is artistName. That might be your problem.

 

I did test the code locally on a test table and it worked fine I just threw it variables for easier testing

 

i.e. LIKE '%$string1%'

 

Hopefully that helps.