[SOLVED] Blog change (Efficient Tags)

[SuperBlue]

I have been thinking about making a change to my blog, mainly i want to group my posts by "tag".

 

I was thinking about what would be the most efficient method to do this. The first method which i have in mind, is having two tables, one containing the Posts and Comments, and one for the Tags.

 

Each time i make a new post, i will add the relevant tags. Then a post is made in the "tags table" for each tag, with a number corresponding to the auto-increment value of the real post.

 

This however creates a "problem", where the Blog needs to query the second database, before it can show the posts tagged as "tagname".

 

 

I'm thinking that its fine to do it like this, but i would still like your ideas on this. Are there any methods that are more efficiant?

[fenway]

Say what?

[jackpf]

That sounds like a pretty stupid way of doing it. Why not just have an extra column in your blog table for tags? Then just get/insert data from/to that?

[GeoffOs]

That sounds like a pretty stupid way of doing it. Why not just have an extra column in your blog table for tags? Then just get/insert data from/to that?

 

but this would be inefficient if you have more than one tag per post. A one to many relationship with a Tags table would be much better and allow as many tags against a post as you wanted.

[jackpf]

Just seperate them with a comma and explode it when you draw it from the database?

[GeoffOs]

Just seperate them with a comma and explode it when you draw it from the database?

 

That would be fine for showing the post, but what about listing all (paged) posts with a specific tag?

[jackpf]

That's a point. However, you could gather all tags, put them into an array and then search that. Would probably be less efficient though.

[kickstart]

Hi

 

Having one field with all the tags sounds nasty, and would likely push you into matching up using php rather than just keeping it in the database.

 

Assuming 2 tables

 

TheBlogTable

BlogId

BlogPost

 

TheTagTable

Id

BlogId

TagText

 

then you could just do:-

 

SELECT BlogId, BlogPost FROM TheBlogTable WHERE BlogId IN (SELECT BlogId FROM TheTagTable WHERE TagText = "TagToCheckFor" )

 

All the best

 

Keith

[GeoffOs]

.....SNIP.......

SELECT BlogId, BlogPost FROM TheBlogTable WHERE BlogId IN (SELECT BlogId FROM TheTagTable WHERE TagText = "TagToCheckFor" )

 

 

Sounds good but I think:

SELECT TheBlogTable.BlogId, TheBlogTable.BlogPost 
FROM TheBlogTable
INNER JOIN TheTagTable on TheBlogTable.BlogId = TheTagTable.BlogId
WHERE TheTagTable.TagText = "TagToCheckFor"

 

I am not a big fan of sub queries, you cannot easily predict the performance......

 

[kickstart]

Hi

 

Tend to agree and thought of a JOIN, but brain fade struck thinking about multiple records being returned unncessarily (which is not even relevant).

 

Might be worth this slight change though

 

SELECT TheBlogTable.BlogId, TheBlogTable.BlogPost 
FROM TheBlogTable
INNER JOIN TheTagTable on TheBlogTable.BlogId = TheTagTable.BlogId
AND TheTagTable.TagText = "TagToCheckFor"

 

That might force the join to only go against records with a matching tag, rather than possibly do a massive join then only filter for matching tags.

 

All the best

 

Keith

[SuperBlue]

Its also possible with FULLTEXT index, and an extra column, from what i was able to find out. But there are some issues with special characters in tagnames, which i don't want to account for atm.

 

Besides, it would also limit the number of characters, unless i didn't mind the perfoamance lost by using TEXT fields.

 

 

I'm going to use a two-table solution to begin with. This is still very new for me, so I'm going to use the solution which is easiest for me, and then gradually try other solutions, it was also what i first came up with.

 

And who knows, maybe its time for me to try and work with a few Joins. :-)