PHP MySQL Blog tags

2
votes

I hope someone here can help me..

I am currently having a small issue when dealing with tags in PHP and MySQL. So far, I am able to added tags when a new post is created. The only problem that I am facing if actually repopulating the checkbox list with tags from the particular post.

This is my table structure

Posts:

id | title | content | date_created | date_modified

Tags:

id | tag_name

Post_Tags:

post_id | tag_id

I have a single interface for managing the posts and tags at the same time. I could have used 2 interfaces but that wouldn't be efficient. I can add the post and the relevant tags easily to the corresponding tables quite easily when the form is submitted. I just want to be able to have the relevant checkboxes selected when a particular post is about to be edited.

using this SQL code to retrieve the post and all its tags but all it has returned is just one tag yet there are 4 tags associated with this post:

   SELECT *
   FROM (`posts`)
   LEFT JOIN `post_tags` ON `post_tags`.`post_id` = `posts`.`id`
   LEFT JOIN `tags` ON `post_tags`.`tag_id` = `blog_tags`.`id`
   WHERE  `id`  LIKE '%104%'
   GROUP BY `blog_posts`.`id_art`

With that not working, i decided to create 2 queries. One that will list all the available tags and the other that will list the tags belonging to a post and basically do a nested foreach loop. I have has some kind of success, the only problem here is that the checkbox is being repeated 4 times. I.E. lets same, the post has tags such as books, movies, videos and games, the result is as follows:

books books books books movies movies movies movies etc....

That keeps on going for all the 10 tags that are listed. 

<div style="overflow-y:auto; width: 350px; height: 200px;">
     <?php foreach ($blog_tags as $tag) : ?>
     <?php foreach($post_tags as $t) : ?>
          <?php echo form_checkbox('tags[]',$tag->id_tag,(!empty($blog_article) && $t->tag_id == $tag->id_tag) ? TRUE : FALSE); ?> <?php echo $tag->name_tag; ?><br />
      <?php endforeach; ?>
      <?php endforeach; ?>
</div>

Can anyone point out why the code is behaving the way it is? Thanks

Sam

PS. Sorry if this is so long....

2
phpmysqltags
why do you have multiple <?php ?> when it can be done with only one <?php ?>? The code would be much clearer. - Izzy

2 Answers

1
votes

Should:

LEFT JOIN `tags` ON `post_tags`.`tag_id` = `blog_tags`.`id`

... maybe be changed to ...

LEFT JOIN `tags` ON `post_tags`.`tag_id` = `tags`.`id`

I wasn't sure what blog_tags was.

0
votes

Sounds like a SQL problem to me. I think your second approach will be easiest to use. You can just SELECT * FROM tags to get all of your tag information. Then SELECT * FROM posts LEFT JOIN post_tags ON posts_id = post_tags.post_id. which would get all of the tag IDs associated with this post. You can then match those IDs up to the list of tags you already retrieved.