MySQL and similar tags

[ShoeLace1291]

What's the best way to select data that has tags similiar to the current record?  For example:  I have a photo gallery and I want the user's to be able to add tags to their photos(tags as in keywords) so I can create a "Similar Photos" area on the view photo page.  I want to insert the tags in the photo table in a column like this "ocean, dock, boat, water, waves" and then get rows that have tags similar to those.  Anyone have any ideas?

[Adam]

Use a second - "relational" - table to store the tags. A comma delimited string is not good for querying, as you'd need to perform LIKE queries on the column, and that will severely limit the accuracy of your results -- at least without a hit to performance.

 

The table just needs to contain the - "foreign key" - ID of the photo, and a simple varchar column for the tag. Add a primary key across the two. Obviously guessing certain column names here, but you could then perform a JOIN query like this to get the results:

 

select t2.photo_id,
       count(t2.photo_id) as matches
from photo_tags t1
join photo_tags t2 on (
    t1.tag = t2.tag
    and t1.photo_id != t2.photo_id
)
join photos p on (t2.photo_id = p.photo_id)
where t1.photo_id = << ID of photo >>
group by t2.photo_id
order by matches desc;

 

That will return a list of photo IDs that have matching tags for the given photo ID, in descending order of number of matches. If you add a LIMIT clause to the end, you can retrieve the the top x matches. Obviously you can also extend the query to select more columns from the photos table..

[ShoeLace1291]

What is photos p in the following code:

join photos p on (t2.photo_id = p.photo_id)

[Adam]

"photos" is the name of the table containing the actual photo details (ID, caption, location, etc) and "p" is an alias assigned to it. An alias is used to prevent any ambiguity between the tables. If no column names are the same between them though, you don't necessarily need it.

[ShoeLace1291]

Is "t2" the tag table?

[ShoeLace1291]

I'm getting a syntax error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(tag1.tutorial_id) as matches FROM tutorial_tags tag1 JOIN tutorial' at line 2"  This is the query

 

return $CI->db->query("
		SELECT tag.tutorial_id
			count(tag1.tutorial_id) as matches
		FROM tutorial_tags tag1
		JOIN tutorial_tags tag2 on (
			tag1.keyword = tag2.keyword
		)
		WHERE tag1.tutorial_id = << ".$tutorial_id." >>
		GROUP BY tag2.tutorial_id
		ORDER BY matches DESC
		LIMIT 6");

[Adam]

You're missing a comma between the columns to select:

 

SELECT tag.tutorial_id, [...]

 

You also need to remove "<<" and ">>". They were only put in to help you see where to use the ID variable...