searching MySQL

pablodelapena

Hi all,

I have 3 tables:

File (FileID,name)
Tags (TagId,Tag)
files_tag(FileID,TagID)

I'd like to be able to search File based on keyword tags. files_tag stores a relationship between the two tables. I'd like to return files if all keywords are matched.

So.. if I have two files in File: (1,'a masterpiece')(2,'photograph')

and two tags in Tags (1,'photography')(2,'illustration')

And files_tag relationship are as follows: (1,1)(1,2)(2,1)
(so 'masterpiece' has two tags and 'photograph' has just one)

What would the query be to search for a file which contains both 'photo' and 'illustration', ie: return 'a masterpiece' only. ( but if i had searched for just 'photo' both files would have been returned)

This is my SQL so far which isn't working properly (I'm sure the logic is off, but I'm not sure of the fix)

SELECT
	f.name,
	t.Tag
FROM files_tag_r AS ftr
LEFT JOIN files AS f
	ON ftr.FileID = f.FileID
LEFT JOIN tags AS t
	ON ftr.TagID = t.TagID

WHERE

t.Tag LIKE '%illustration%'
AND t.Tag LIKE '%photo%'


ORDER BY f.name

Obviously if there is a different way to store the data then I'm open to suggestions!

Thanks in advance.

bretticus

t.Tag can't be both values simultaneously. Try using an OR instead of an AND. Also, is there a good reason you are using LEFT JOIN that I'm not catching?

pablodelapena

If I use OR then it returns results with any of the keywords, not all of the keywords.

As for the joins, I just need to get the data (such as name and tag) from the other tables in order to perform the search, if that makes any sense.

bretticus

If I use OR then it returns results with any of the keywords, not all of the keywords.

Quite a conundrum since t.Tag cannot be simultaneously both. On one hand, you get all matches with OR (as you should.) On the other hand, you get nothing (as you should.) Maybe your database design is not suited for the drill down since you have a join table that allows many-to-many relationships. If I had more time to wrap my head around this, I'd help you out. Nothing comes to mind immediately. BUMP!

bretticus

Ya know...

I was getting ready for the day (yeah I know, I work from home) and I thought about this and realized you could do it with multiple sub queries. Here is an example.

$sql = "SELECT name
FROM files
WHERE fileid
IN (

SELECT fileid
FROM filetags
INNER JOIN tags
USING ( tagid )
WHERE tag = 'photography'
)
AND fileid
IN (

SELECT fileid
FROM filetags
INNER JOIN tags
USING ( tagid )
WHERE tag = 'illustration'
)";

Your schema should be very similar