Hi. I'd like to write the following as a MySQL SELECT statement to cut down on the number of queries required to get the information, but I'm not sure how to write it.
I have two tables - tags and books_tags (a many-to-many relationship junction table). The final output I want would print as follows:
<label for="formFiltertag1"><input type="checkbox" name="tag[]" value="1" id="formFiltertag1" class="rank90" /> PHP (15)<br /></label>
Where the text is the name of the tag (tags.name) and the number in parens is the count of how often the tag's ID appears in the junction table (COUNT(books_tags.tag_id)). The input ID and value will be dynamic based on the tags.id field.
I originally thought I'd just run a query that gets all of the info from the tag table and then use a foreach loop to run a separate count query for each one, but as they number of tags grows that could get unwieldy quickly.
Here's an example as I have it written now (using CodeIgniter's ActiveRecord pattern)...
The Model:
function get_form_tags() {
$query = $this->db->get('tags');
$result = $query->result_array();
$tags = array();
foreach ($result as $row) {
$this->db->select('tag_id')->from('books_tags')->where('tag_id', $row['id']);
$subResult = $this->db->count_all_results();
$tags[] = array('id' => $row['id'], 'tag' => $row['tag'], 'count' => $subResult);
}
return $tags;
}
The controller:
function index() {
$this->load->model('browse_model', 'browse');
$tags = $this->browse->get_form_tags();
$data['content'] = 'browse/browse';
$data['tags'] = $tags;
$this->load->view('global/template', $data);
}
The view (condensed):
<?php foreach ($tags as $tag) : ?>
<label for="formFiltertag<?php echo $tag['id'] ?>"><input type="checkbox" name="tag[]" value="<?php echo $tag['id'] ?>" id="formFiltertag<?php echo $tag['id'] ?>" class="rank<?php echo $tag['count'] ?>" /> <?php echo $tag['tag'] . ' (' . $tag['count'] . ')' ?><br /></label>
<?php endforeach; ?>
This works, but like I've said it's going to create way more queries than needed to get the job done. Surely there's a better way. Penny for your thoughts?
Thanks much, Marcus