Categories & tags in coma separated list from db

best way to do it?

[le_gber]

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.

[jinxed]

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.

[le_gber]

i did think of lookup tables, but then when adding new posts you have to run two queries, don't you?

[jinxed]

What are you trying to achieve?

[le_gber]

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

[jinxed]

I meant your query. :-)

It would be helpful to know what this query is attempting to bring back in it's results.

[LifeinAsia]

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...

[le_gber]

@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...