CODEWITHSUNDEEP
phpmysql
user3871
user3871

Reputation: 12718

PHP-MySQL Tagging

I have a comics website which currently allows users to choose which comics they view by category: Charts, Office, Life, Misc, etc.

I'd like to implement a tagging system, similar to what we have here on StackOverflow, which will describe more of the content of each comic rather than its category. Ex: In Charts category, I have several business related...

My simple solution would be to handle it just how I've handled my categorization-

  1. Create a "Tags" table with tagid, tagname, tagdescription
  2. Add a tagid_ForeignKey field in comics table, and add a tag to each post.
  3. When a user clicks on a tag, it will show only those posts with that tag... or if there is also a category specified, it will show that specific category with that specific tag.

This approach, however, seems to limit me to one tag per category. What if I have a comic that is business and relationships related... so It'd need both of those tags.

How would I be able to attach multiple tags per comic?

EDIT:

A few more questions:

1) What do I insert into my new relational table... anything?

2) Also, for while ($row = $tag->fetch_assoc()) {, how can I loop through a table if there is a join? Isn't that an associative array?

3) The issue is that I am echoing out the tag choices as such, so once a user clicks on a link, how would you be able to allow them to then click on another link to assign a 2nd tag?

enter image description here

function getTags() {
include 'dbconnect.php';
global $site;

$tag = $mysqli->query("SELECT tagid, tagname FROM tags");   
//$tag = $mysqli->query("SELECT * FROM comics c INNER JOIN comictags ct ON (c.comicID = ct.comicID) WHERE ct.tag_id IN (1, 2, 3) GROUP BY c.comic_id");

mysqli_close($mysqli);

while ($row = $tag->fetch_assoc()) {        
    echo "<a href='?action=homepage&site=" . $site . "&tag=" . $row['tagid'] . "&tagname=" . $row['tagname'] . "'/>" . $row['tagname'] . "</a><br />";
  }

}

Upvotes: 2

Views: 657

Answers (2)

Colin M
Colin M

Reputation: 13348

You can accomplish this with a many-to-many relationship. A many-to-many relationship uses a relational join table that would look like this:

+---------------+---------------+
|   comic_id    |     tag_id    |
+---------------+---------------+
|       1       |        2      |
+---------------+---------------+
|       1       |        3      |
+---------------+---------------+
|       1       |        4      |
+---------------+---------------+

Now, in your query:

SELECT * FROM comics c INNER JOIN comic_tags ct ON (c.comic_id = ct.comic_id) WHERE ct.tag_id IN (1, 2, 3) GROUP BY c.comic_id

Where 1, 2, 3 are the tags the user selected that they would like to see.

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191749

Just add another table. Then you have three: One for Tags, one for Comics, and one for the relationship. You have to have this indirection table to properly store a many-to-many relationship. This allows each comic to have zero or more tags (and vice versa).

Upvotes: 6

Related Questions