Find related database entries by tag

[georgerobbo]

Hi! I'm having some trouble finding related articles by their assigned tags. The database structure is as follows:

 

Primary Key = underlined

 

Table: Blog

BlogID

BlogTitle (etc)

 

Table: Tag

TagID

The Tag

 

Table: BlogTags

BlogID

TagID

 

Once I have the script fully working I shall tidy everything up and create a class, so I apologise for such a bad practise for the time being.

 

$BlogQuery = mysql_query("SELECT BlogID FROM blog WHERE BlogSlug = '$QueryString'");				
$BlogResult = mysql_fetch_row($BlogQuery);
$BlogID = $BlogResult[0];

$BlogTagQuery = mysql_query("SELECT blog.BlogID, blogtags.TagID FROM blog JOIN blogtags ON blogtags.BlogID = blog.BlogID WHERE blog.BlogID = $BlogID");

while($BlogTagResult = mysql_fetch_array($BlogTagQuery)):

$TagID = $BlogTagResult['TagID'];							
$RelatedQuery = mysql_query("SELECT blogtags.BlogID FROM blogtags WHERE blogtags.BlogID != $BlogID AND blogtags.TagID = $TagID");

while($RelatedResult = mysql_fetch_array($RelatedQuery)):

	$RelatedID = $RelatedResult['BlogID'];

	$FindBlogQuery = mysql_query("SELECT BlogTitle, BlogSlug FROM blog WHERE BlogID = $RelatedID");

	while($FindBlogResult = mysql_fetch_array($FindBlogQuery)):
		echo '<li><a href="'.$RootUrl.$FindBlogResult['BlogSlug'].'">'.$FindBlogResult['BlogTitle'].'</a></li>';
	endwhile;

endwhile;

endwhile;

 

To explain all the junk above. I retrieve the ID of the currently displayed article which I then query to find all TagID linked to the article in the BlogTags table. I then take each tag and try to find another blog with the same TagID. There are a few problems with this which I hope to address.

 

1. Efficiency - the above is a pretty pathetic script - way too many MySQL queries.

2. If a related post has more than one identical tag then it will display the link to the post for each related tag found. Of course we don't want this so I want to create some method to rank the related blogs by the number of identical tag and then display the link once in ranked order.

 

Any help would be very very appreciated!