Welcome to WebmasterWorld Guest from 3.80.60.248

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Categories & tags in coma separated list from db

best way to do it?

     
11:17 am on Jun 24, 2015 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Mar 14, 2003
posts: 1550
votes: 0


Hi all,

I am working on a simple blog system and am wondering what is the best way to do the following:

- I have 3 tables in my database: 1 for blog-posts, 1 for categories and 1 for tags.
- the tables for categories and tags have 2 columns: id and name;
- the id(s) of the categories and the id(s) of the tags are stored alongside each blog_post in a coma separated list, in 2 columns

To display a list of all the blog_posts with categories and tags (ala WP), I query the db three times to get all the blog_posts, all the categories and all the tags and:
1. loop through each post;
2. explode() my coma separated list of categories into an array;
3. Loops through the categories
4. if there is a match, str_replace the id by the name;
5. do step 2, 3 and 4 again for the tags
6. return the modified $blog_posts array

Surely there must be a better way to do this, even if it involves rethinking the db structure or the query.

Ta.
2:09 pm on June 24, 2015 (gmt 0)

Full Member

10+ Year Member

joined:Jan 2, 2005
posts:333
votes: 0


5 tables required;

1. Blog_Posts
2. Tags
3. Blog_Posts_Tags
4. Categories
5. Blog_Posts_Categories

For the joining tables, i.e 3&5 - store the id's of the blog posts and the tags/categories.

Then just query create one query with the joins.
3:01 pm on June 24, 2015 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Mar 14, 2003
posts: 1550
votes: 0


i did think of lookup tables, but then when adding new posts you have to run two queries, don't you?
3:22 pm on June 24, 2015 (gmt 0)

Full Member

10+ Year Member

joined:Jan 2, 2005
posts:333
votes: 0


What are you trying to achieve?
3:26 pm on June 24, 2015 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Mar 14, 2003
posts: 1550
votes: 0


leaner, cleaner, faster code... and learning the best way to do this along the way
4:44 pm on June 24, 2015 (gmt 0)

Full Member

10+ Year Member

joined:Jan 2, 2005
posts:333
votes: 0


I meant your query. :-)

It would be helpful to know what this query is attempting to bring back in it's results.
4:56 pm on June 24, 2015 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5804
votes: 155


leaner, cleaner, faster code... and learning the best way to do this along the way

Thinking about the future, if you wanted to do a search by category or keyword, using lookup tables will be more efficient than storing the ids in a comma-separated list. Go with the normalized form suggested by jinxed.

To display a list of all the blog_posts ..., I query the db three times
when adding new posts you have to run two queries

Most likely your site will be doing far more reads than writes, so you should optimize the read into 1 query, joining the tables you need (which you can do with the tables jinxed suggested).

And if your site is doing more writes (posts) than reads (people reading the posts), then you have bigger issues than database normalization...
5:08 pm on June 24, 2015 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Mar 14, 2003
posts: 1550
votes: 0


@jinxed the post_title and the categories names and tags names (not ids) which is what is stored with the blog post

@LifeinAsia, yes indeed more read than writes... will take a look at lookup tables...
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members