CODEWITHSUNDEEP
phpdatabasecodeigniter-2
Ivanka Todorova
Ivanka Todorova

Reputation: 10219

Getting info from many tables

I have 4 tables: question_tags, tags, questions and users.

Question structure is: q_id, q_title, q_content, q_date, q_author (id from users table).

Tags structure is: tag_id, tag_name, tag_description.

Question_tags structure is: id, tag_id, q_id.

I want ot list all questions and next to every question title to show and its tags. I've come up with this so far:

$this->db->join('users', 'q_author = users.id', 'left');
$this->db->order_by('q_id', 'desc');
$this->db->limit($per_page, $offset);
$query = $this->db->get('questions');

But have no ideas about the tags. (also I'm using CodeIgniter)

P.S. Every question can has more than one tag.

database table tags codeigniter-2

Upvotes: 1

Views: 79

Answers (1)

davidethell
davidethell

Reputation: 12018

You'll need to have different queries if you want to avoid returning duplicate data in your main query. Because you have many tags related to each question you should loop through your question results and within each loop iteration query the tags for that question.

Upvotes: 1

Related Questions