Take the 2-minute tour ×

I would like to tweak the following mysql query, if possible:

SELECT * FROM artists,tags WHERE tags.tag_title LIKE '$q' AND artists.art_id=tags.art_id
  • $q are the keywords entered by users
  • artists is the table with the artists basic information
  • tags is the table with keywords related to the artists (an artist can add multiple keywords -or phrases- and each keyword is stored in a different row)

With this query tags.tag_title have to match the exact search. For example "alternative rock" will display all artists with the keyword "alternative rock" in the table tags. It's ok.

So, the question is that for the keyword "alternative rock" I would like to display ALSO the artists that have the keywords "alternative" and "rock" in different rows on the table tags but not the artists with the keywords "alternative" and "metal" or artists with the keywords "indie" and "rock".

Does it makes sense?

share|improve this question
confusing me also :) good question –  user1646111 Jan 31 '13 at 18:26
This is a many to many relationship. You need a table for artists, a table for tags, and a table (artists_tags) that relates one to the other. –  Strawberry Jan 31 '13 at 18:37
@Strawberry this how a problem sometimes should be altered instead of solution... –  user1646111 Jan 31 '13 at 19:36
I will try to explain it better: We have the artist A with the keywords "alternative" and "rock" on the table tags, the structure of the table tags is (tag_id, art_id, tag_title), and the artist B with the tags "rock" and "metal" on the table tags. When searching for "alternative rock" the artists A should be listed and the artist B no. –  Ramon Jan 31 '13 at 20:40
add comment

2 Answers

Obviously this is only an example, but I think you're after something like this:

SELECT * FROM artisits artist LEFT JOIN tags tag ON tag.artist_id = artist.id WHERE artist.name LIKE '$q' OR tag.name = $q
share|improve this answer
add comment

you look for something like this,

 SELECT * FROM artists
 ON artists.art_id=tags.art_id
 WHERE tags.tag_title LIKE '% $q1 %' OR  tags.tag_title LIKE '% $q2 %'

q1 and q2 are keyword1 and keyword2

share|improve this answer
example: searching for "A B" must select rows with tag = 'A B' or 'A' or 'B' where (same artist having tags if in previous row tag = 'B' then in this ROW tag = 'A') –  user1646111 Jan 31 '13 at 18:32
how you will split the $q variable here? unless u have every keyword its own variable –  echo_Me Jan 31 '13 at 18:35
better show example in sqlfiddle and give the result you wish –  echo_Me Jan 31 '13 at 18:37
what is what asked for, not what you willing to provide... –  user1646111 Jan 31 '13 at 18:39
the only way I found that returns the artist results for both keywords is using a search in boolean mode, although in that case the results returns also the artists with just one of the keywords... I need the results for both keywords only, any ideas? –  Ramon Jan 31 '13 at 20:52
show 1 more comment

Your Answer


