View Full Version : Resolved Related database entries, selecting entry only once?

08-21-2009, 11:49 PM
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:

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,

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.

$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,

08-22-2009, 12:14 AM
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.

08-22-2009, 12:26 AM
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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum