Complicated query, joins maybe?

many_pets

Can someone help with a query, please?
I have a table of projects. And a table of images that point to their parent project, and then a table of image_tag (search words associated with the images) that each point to the parent image. Each image can have multiple tags associated with it. Each image can have only one owning project, and projects have multiple images.

Here are the tables:
Project ( id, visible )
Image ( id, project (owning project id), [other stuff] )
Image_tag ( id, image (owning image id), search_term )

I want to query the db for images that have more than one tag (for instance, images that are tagged both as 'animals' and 'landscape'). So, there would be two image_tag recs for such an image, both pointing to the same image, one with search_term= 'animals' and one with search_term= 'landscape'. Ultimately, I'd like to also be able to get only images that belong to projects that have 'visible'='1'.

I can get it all to work for matching just one image_tag, but can't see how to do this with multiple image_tag recs. Maybe I need to look for each tag than join the results?

So, if someone could help with either the query, or just how to go about thinking about this, I'd so appreciate it.

Drakla

SELECT id, count(image) as tagcount
FROM image_tag
GROUP BY image
HAVING tagcount > 1

It's something like that, untested...

many_pets

Thanks... I need to clarify here. This is to respond to a user's search question, and so they'd be asking for images that have certain tags or search terms associated with them. So they might ask for images labelled 'landscape' and 'animals'. I need to find all images with those two image_tag records. Or maybe even more search terms...

Serially, it would be:
1. find all image_tag recs for 'landscape' (points to an image)
2. find all image_tag recs for 'animals' (points to an image)
3. find all images in common in both tables 1. and 2.

Does that make sense?? Maybe I'm making it more complicated than it really is...

Maybe an intersection between these two (derived) tables on image?

Drakla

You could construct your queries along these lines - note that you'd have to do whatever splitting of search terms you've received, and there's no join to make sure the images actually exist in the image table.

	// these represent the terms that have been submitted
$terms = array('cats', 'kittens', 'cannibals');

$q = 'SELECT image, search_term FROM image_tags WHERE ';

$conditions = array();
foreach ($terms as $term)
{
		// escape the term - use whatever db func you have
	$term = mysql_real_escape_string($term);
		// this could be LIKE "%$term%" if you wanted partial matching
	$contitions[]= 'image_tag = "$term"';
}

$q .= implode(' OR ', $conditions);
	// execute

// if you wanted a summary of matches

$q = 'SELECT search_term, count(*) AS hits FROM image_tags WHERE ';
$q.= implode(' OR ', $conditions);
$q.= ' GROUP BY search_term';
	// execute

again none of that is tested