Help with 1 Post with multiple categories

Psmeg · #1 (**permalink**) 08-20-10, 08:41

Hi all,

I'm working on the development of a simple blog as part of a CMS and have stumbled upon an issue that I'm hoping someone here can help me with.

Here's what I'm trying to achieve and how the DB is currently setup.

I want to display records (blog posts) and also include the list of categories each post is in.

For example: New Web Site Released (post name) would display under the following categories: business information, web site releases, company news.

This is fairly easy to achieve for an individual blog post, as I have the post ID to filter things by. But, in the listing page I have multiple posts and as such am struggling on how to group the information for post categories below each blog post.

The database is setup with a one-to-many relationship as follows:

tbl_BlogPosts

bl_id, bl_date, bl_synopsis, bl_post

tbl_BlogCategoryJoin (one-to-many table)

bc_id, bcName

tbl_BlogCategory

bc_id, bl_id

Is there a simple way of grouping this information via a MySQL statement?

Any help would be much appreciated.

Regards

Paul

r937 · #2 (**permalink**) 08-20-10, 12:34

Code:

SELECT bp_table.bl_id
     , bp_table.bl_date
     , bp_table.bl_synopsis
     , bp_table.bl_post
     , x_table.categories
  FROM tbl_BlogPosts AS bp_table
LEFT OUTER
  JOIN ( SELECT bc_table.bl_id
              , GROUP_CONCAT(bcj_table.bcName) AS categories
           FROM tbl_BlogCategory AS bc_table
         INNER
           JOIN tbl_BlogCategoryJoin AS bcj_table
             ON bcj_table.bc_id = bc_table.bc_id
         GROUP
             BY bc_table.bl_id ) AS x_table
    ON x_table.bl_id = bp_table.bl_id

Internet Services

Web Development

Digital Marketing

Consumer Tech