SQL Joining 3 tables tag system codeigniter

I've been trying to come up with a tag system for my posts and I am having troubles getting the values I want. Rather, I can't seem to wrap my head around the logic of the joining of tables. I've tried to find information to help me along but I guess I need someone to really lay down the basics.

Anyway, these are my tables (shortening the posts table);

POSTS
post_id
post_title
post_freetext

POST_TAGS
post_id
tag_id

TAGS
tag_id
tag_text

What I am trying to do is fetching all posts connected to a single tag.

My CodeIgniter code looks like this;

   $this->db->select('*');
   $this->db->from('posts');
   $this->db->join('post_tags', 'post_tags.post_id = posts.post_id' ,'inner');
   $this->db->join('tags', 'tags.tag_id = posts.post_id', 'inner');
   $this->db->where('tag_text =', $tagid);
   $this->db->order_by('posts.post_id', 'desc');
   $q = $this->db->get();

$tagid in this case is the string (read tag) that I am looking for.

I was successful in joining two tables and fetching the posts but then I realized I would not be able to allow the user to look at all the tags (and thus I needed a separate "tag" table). Can't seem to get this right though.

Any help at all would be very much appreciated and I do realize that this question has probably been answered multiple times - still, can't wrap my head around the logic.

Answers


About logic of joining this page could help

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

in you code you have a mistake in second join

$this->db->join('tags', 'tags.tag_id = posts.post_id', 'inner');

Those two tables are not connected(where is foreign key and where it points).

You have to use table post_tags not posts so use something like this: 'post_tags.tag_id = tags.tag_id'


$this->db->select('*');
   $this->db->from('posts');
   $this->db->join('post_tags', 'post_tags.post_id = posts.post_id' ,'inner');
   $this->db->join('tags', 'tags.tag_id = post_tags.tag_id', 'inner');
   $this->db->where('tag_text', $tagid);
   $this->db->order_by('posts.post_id', 'desc');
   $q = $this->db->get();

Need Your Help

Android - Unable to change text in TextView

android textview findviewbyid

I just started to learning Android and I have a problem. With new Android Studio i have two XML files for each activity, so not to get NullPointerException I had to change my findViewById a little ...

Alternatives to php for in-line web programming?

php python ruby web-applications

I first learned web programming with php a while back. It has some features that I find very helpful, but the overall language is not something I enjoy, just as a matter of personal preference. I am