PHP-MySQL Tagging

Question

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-

Create a "Tags" table with tagid, tagname, tagdescription
Add a tagid_ForeignKey field in comics table, and add a tag to each post.
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 />";
  }

}

Explosion Pills · Accepted Answer · 2013-02-13 14:44:41Z

6

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).

answered Feb 13, 2013 at 14:44

Explosion Pills

192k56 gold badges341 silver badges417 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3871 Over a year ago

Can you elaborate on "indirection table"? Is that just a regular table? How do I set that up?

Explosion Pills Over a year ago

@Growler yeah just a regular table. No special meaning for the word "indirection," it just means that Tags and Comics to not map directly to each other, but map indirectly through this table. Something like

CREATE TABLE ComicTags (ctID int unsigned not null auto_increment primary key, comID int unsigned not null, tagID int unsigned not null)

user3871 Over a year ago

Okay, thank you for this... do you have an example of how my query would look? and, can you see my above edit? I have another question

Explosion Pills Over a year ago

@Growler why don't you ask SO another question?

user3871 Over a year ago

I figured appending it to this question would be easier since you already have some background into my question.

Colin M · Accepted Answer · 2013-02-13 14:46:02Z

2

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.

answered Feb 13, 2013 at 14:46

Colin M

13.4k3 gold badges42 silver badges61 bronze badges

1 Comment

user3871 Over a year ago

Okay, thank you for this... but can you see my above edit? I have a question

Collectives™ on Stack Overflow

PHP-MySQL Tagging

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related