0

So I am working on a custom blog system where on each post I show the list of tags associated with that post. Next to each one of the the tags I want to show a count of how many other posts use each tag, and that is what I am having issue figuring out.

Here the the 3 tables I have one for the posts, tags, and then post_tags for assigning which posts have what tags.

mysql> DESC post; 
+----------+--------------+------+-----+---------+----------------+ 
| Field    | Type         | Null | Key | Default | Extra          | 
+----------+--------------+------+-----+---------+----------------+ 
| id       | mediumint(9) | NO   | PRI | NULL    | auto_increment | 
| contents | longtext     | NO   |     | NULL    |                | 
+----------+--------------+------+-----+---------+----------------+

mysql> DESC tags; 
+-------+--------------+------+-----+---------+----------------+ 
| Field | Type         | Null | Key | Default | Extra          | 
+-------+--------------+------+-----+---------+----------------+ 
| id    | mediumint(9) | NO   | PRI | NULL    | auto_increment | 
| tag   | varchar(255) | NO   |     | NULL    |                | 
+-------+--------------+------+-----+---------+----------------+ 

mysql> DESC post_tags; 
+---------+--------------+------+-----+---------+----------------+ 
| Field   | Type         | Null | Key | Default | Extra          | 
+---------+--------------+------+-----+---------+----------------+ 
| id      | mediumint(9) | NO   | PRI | NULL    | auto_increment | 
| post_id | mediumint(9) | NO   |     | NULL    |                | 
| tag_id  | mediumint(9) | NO   |     | NULL    |                | 
+---------+--------------+------+-----+---------+----------------+

Here is the code I use to pull the list of tags for the post

$stmt = $con->prepare('SELECT * FROM tags LEFT JOIN post_tags ON post_tags.tag_id = tags.id JOIN post ON post.id = post_tags.post_id WHERE post.id = ?');
$stmt->bind_param('i', $_GET['id']);
$stmt->execute();
$tags = $stmt->get_result();

and how I display them

<?php while ($tag = $tags->fetch_assoc()) : ?>
<span class="tags"><a href="tags.php?<?= $tag['tag'] ?>" class="tag tag-35762 "><span class="name"><?= $tag['tag'] ?></span><span class="count">1</span></a>
<? endwhile ?>
Ravi Hirani
  • 6,511
  • 1
  • 27
  • 42
josiec09
  • 19
  • 4
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman Jul 03 '22 at 21:54

1 Answers1

1

Something like this?

SELECT t.*, IF(post_count is null,0,post_count) as post_count
FROM tags AS t 
LEFT JOIN(SELECT COUNT(*) as post_count,tag_id FROM post_tags GROUP BY tag_id) as pcount 
     ON pcount.tag_id = t.id

OR

SELECT t.*, count(pt.id) as post_count
FROM tags AS t 
LEFT JOIN post_tags pt ON pt.tag_id = t.id
GROUP BY t.id
Ravi Hirani
  • 6,511
  • 1
  • 27
  • 42