Ok so basically, I'm trying to create a "tagging" system for my database entries, so that I can find related media. So far I have a database table setup like this:

CREATE TABLE media (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
`group` int(2) NOT NULL,
`type` int(2) NOT NULL,
`source` varchar(255) NOT NULL,
`user` int(11) NOT NULL,
`timestamp` varchar(255) NOT NULL,
thumbnail varchar(255) NOT NULL,
tags varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

I've been inserting tags in with my media entries, in the format of
"tag1, tag2, tag3, tag4"

So the first thing I did was get the variable with the tags and explode it into an array. Then "foreach" tag, I ran a mysql query to get other entires containing the tag set as $current.


<?php
$related = explode(", ",$media["tags"]);

foreach($related as $current) {
$get_related = mysql_query("SELECT * FROM `media` WHERE id != '$_GET[id]' AND tags LIKE '%" . $current . "%'");
while($found = mysql_fetch_array($get_related)) {
echo $found["title"] . "<br/>";
} }

?>


The problem I have is here. When I use this structure, it finds every video with the first tag in it, and then repeats for each tag. So if my first entry has 3 tags similar to entry 2, entry 2 would show 3 times in my list.

I don't know how to go about this, (This is why I'm asking) but I want to have it output each video found, only once, and order them by amount of tags similar.

This should theoretically achieve a "Related media" list. I had the idea to use a multi-dimensional array, but I couldn't get it right.

Does anyone have any idea on how I can finish this script, I googled a couple of similar scripts, but haven't found one using a "related entries" module on their pages.

Thanks in advance,
GSimpson.

Normalize your design; store only one tag on a row, in a new separate table, that relates back to your first table. You'll then be able to work with the data as you describe.

Ahh, that makes a bit more sense to do it that way. Thanks.

One newb question about Mysql, say we were doing this on a project the size of YouTube, Metacafe, Photobucket, Last.fm - would there be any problem with the table handling that many tags?

Once again, Thank You.