Timothy Fisher Timothy Fisher - 1 month ago 6
MySQL Question

How can I retrieve all of the tags for each database record (many-to-many relationship) and echo them as a list

I'm using CodeIgniter and MySQL. I'm using the following function in my model to retrieve all of the items in a table.

public function get_projects() {
$query = $this->db->select('*')
->from('projects')
->get();
return $query->result();
}


I then display the results in a view like this:

<?php foreach ($projects as $project) { ?>
<li><a href="#"><?php echo $project->title; ?></a></li>
<?php } ?>


Now I want to list all of the tags for each item from the conjunction table that I have between a
Projects
table and a
Tags
table.

CREATE TABLE project_tags (
'project_id' int(11) NOT NULL PRIMARY KEY,
'tag_id' varchar(20) NOT NULL PRIMARY KEY,
FOREIGN KEY (project_id) REFERENCES Projects(id),
FOREIGN KEY (tag_id) REFERENCES Tags(id)
);


Now how can I add to my
get_projects()
function so that I can retrieve an array of the tags for each project?

I know I could possibly do a join query, however when I do that it returns multiple project titles because there's multiple tags, so the foreach loop in my view prints out duplicate titles.

Answer

A similar question was asked and here is a great answer that does the same thing Answer