joined:Mar 14, 2003
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.