How can I implement an "interesting tags" feature like that on Stack Overflow?
As mentioned in the other answers, there's most likely a many-to-many relationship with between users and tags, represented as an own table. I made a SQL demo of a simplified case. The InterestingTags table is the table connecting what user is interested in what tags.
/* Create tables */CREATE TABLE User (id INT NOT NULL AUTO_INCREMENT, name varchar(50), PRIMARY KEY(id));CREATE TABLE Tag (id INT NOT NULL AUTO_INCREMENT, name varchar(50), PRIMARY KEY(id));CREATE TABLE InterestingTags (user_id INT NOT NULL REFERENCES User(id), tag_id INT NOT NULL REFERENCES Tag(id), PRIMARY KEY(user_id,tag_id));/* Insert some data *//* 3 users, 5 tags and some connections between users and tags */INSERT INTO User (name) VALUES ('jQueryFreak'), ('noFavoriteMan'), ('generalist'); INSERT INTO Tag (name) VALUES ('jQuery'), ('php'), ('asp.net'), ('c#'), ('ruby');INSERT INTO InterestingTags (user_id, tag_id) VALUES (1,1), (3,1), (3,2), (3,3), (3,4);/* Select all the users and what tags they are interested in */SELECT u.name, t.name FROM User u LEFT JOIN InterestingTags it ON it.user_id = u.id LEFT JOIN Tag t ON t.id = it.tag_id;/* Select all tag ids that are interesting to user 3 ("generalist") */SELECT tag_id FROM InterestingTags WHERE user_id = 3;/* Now let's introduce a questions table. For simplicity, let's say a question can only have one tag. There's really a many-to-many relationship here, too, as with user and tag*/CREATE TABLE Question (id INT NOT NULL AUTO_INCREMENT, title VARCHAR(50) NOT NULL, tag_id INT NOT NULL REFERENCES Tag(id), PRIMARY KEY(id));/* Insert some questions */INSERT INTO Question (title, tag_id) VALUES ('generating random numbers in php', 2), /*php question*/ ('hiding divs in jQuery', 1), /*jQuery question*/ ('how do i add numbers with jQuery', 1), /*jQuery question 2*/ ('asp.net help', 3), /*asp.net question */ ('c# question', 4), /*c# question */ ('ruby question', 5); /*ruby question *//* select all questions and what users are interested in them */SELECT q.title, u.name FROM Question qLEFT JOIN InterestingTags it ON it.tag_id = q.tag_id LEFT JOIN User u ON u.id = it.user_id;/* select all questions a user will be interested in. Here the user is jQueryFreak with id = 1 */SELECT q.id, q.title FROM Question qLEFT JOIN InterestingTags it ON it.tag_id = q.tag_idLEFT JOIN User u ON u.id = it.user_idWHERE u.id = 1;/* Select all questions and indicate whether or not jQueryFreak (with id = 1) is interested in each one *//* TODO: make SO question about how to do this as efficient as possible :) */SELECT q.id, q.title, (SELECT COUNT(*) FROM InterestingTags it WHERE it.tag_id = q.tag_id AND it.user_id = 1) AS is_interested FROM Question q;/* Let's add a many-to-many relationship between questions and tags. Questions can now have many tags */ALTER TABLE Question DROP COLUMN tag_id;CREATE TABLE Question_Tag ( question_id INT NOT NULL REFERENCES Question (id), tag_id INT NOT NULL REFERENCES Tag (id), PRIMARY KEY (question_id, tag_id));/* Insert relationships between questions and tags */INSERT INTO Question_Tag VALUES /* First the tags as in the above examples */ (1,2), (2,1), (3,1),(4,3),(5,4),(6,5), /* And some more. ASP.NET question is also tagged C# and php question is tagged jQuery */ (1,1), (4,4);/* select all questions and what users are interested in them(Some combinations will show up multiple times. This duplication is removed in the two following queries but I didn't find a solution for it here)*/SELECT q.title, u.name FROM Question qLEFT JOIN Question_Tag qt ON qt.question_id = q.id /* <-- new join */LEFT JOIN InterestingTags it ON it.tag_id = qt.tag_id LEFT JOIN User u ON u.id = it.user_id;/* select all questions a user will be interested in. Here the user is jQueryFreak with id = 1 */SELECT q.id, q.title FROM Question qLEFT JOIN Question_Tag qt ON qt.question_id = q.id /* <-- new join */LEFT JOIN InterestingTags it ON it.tag_id = qt.tag_idLEFT JOIN User u ON u.id = it.user_idWHERE u.id = 1GROUP BY q.id; /* prevent duplication of a question in the result list *//* Select all questions and indicate whether or not jQueryFreak (with id = 1) is interested in each one *//* STILL TODO: make SO question about how to do this as efficient as possible :) */SELECT q.id, q.title, (SELECT COUNT(*) FROM InterestingTags it WHERE it.tag_id = qt.tag_id AND it.user_id = 1) AS is_interested FROM Question qLEFT JOIN Question_Tag qt ON qt.question_id = q.id /* <-- new join */GROUP BY q.id;
Update: Added php demo.
Remember to change your mysql constants before running the demo
What this does is running two queries to the DB:
- One asking for all questions and their tags
- One asking for what tags the user is interested in.
To "mark" a question with its tags, it adds a class for each tag it belongs to -- e.g. a question tagged with jQuery (where jQuery has the ID 1) and php (with ID 2) will have the classes tagged-1 and tagged-2.
Now combining this with the other query, fetching the interesting tags, you just have to select the questions having classes corresponding to the interesting tags and style them. For example, if you're interested in the tags with ID 1 and 3, it would be the following jQuery code $('.tagged-1, .tagged-3').addClass('interesting-tag');
<?phpconst mysql_host = "localhost";const mysql_username = "";const mysql_password = "";const mysql_database = "INTERESTINGTEST";const user_id = 1; //what user is viewing the page?class Question { public $id; public $title; public $tags; function __construct($id,$title) { $this->id = $id; $this->title = $title; $this->tags = array(); }}class Tag { public $id; public $name; function __construct($id,$name) { $this->id = $id; $this->name = $name; }}/**************************Getting info from database****************************/mysql_connect(mysql_host,mysql_username,mysql_password);mysql_select_db(mysql_database);//Fetch interesting tags$result = mysql_query("SELECT tag_id FROM InterestingTags WHERE user_id = " . user_id);$interesting_tags = array();while($row = mysql_fetch_array($result)){ $interesting_tags[] = $row['tag_id'];}//Fetch all questions and their tags$query_select_questions ='SELECT q.id AS q_id, q.title AS q_title, t.id AS t_id, t.name AS t_name FROM Question qLEFT JOIN Question_Tag qt ON qt.question_id = q.idLEFT JOIN Tag t ON t.id = qt.tag_id';$result = mysql_query($query_select_questions);$questions = array();while($row = mysql_fetch_array($result)){ $q_id = $row['q_id']; $q_title = $row['q_title']; $t_id = $row['t_id']; $t_name = $row['t_name']; if (!array_key_exists($q_id, $questions)) $questions[$q_id] = new Question($q_id, $q_title); $questions[$q_id]->tags[] = new Tag($t_id, $t_name);}mysql_close();/**************************Write document****************************/?><style> .question { padding:0px 5px 5px 5px; border:1px solid gray; margin-bottom: 10px; width:400px } .interesting-tag { background-color: #FFEFC6 }</style><script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.js"></script><script> var interesting_tags = [ <?php echo implode($interesting_tags,',') ?> ]; var tagclass_prefix = ".tagged-"; var tags_selector = tagclass_prefix + interesting_tags.join(", " + tagclass_prefix); $(function() { $(tags_selector).addClass("interesting-tag"); });</script><?php foreach ($questions as $q) { $tagsIDs = array(); $tagNames = array(); foreach ($q->tags as $tag) { $tagsIDs[] = $tag->id; $tagNames[] = $tag->name; } $classValue = "tagged-" . implode($tagsIDs," tagged-"); $tagNames = implode($tagNames, ", ");?><div id="question-<?php echo $q->id ?>" class="question <?php echo $classValue ?>"> <h3><?php echo $q->title ?></h3> Tagged with <strong><?php echo $tagNames ?></strong></div><?php }?>
There is a row in mysql for every member, let's call it "interested_tags".
More likely, there is an additional table that represents a many-to-many relationship between users and tags. With another table that associates tags with questions.
Then you'd just need a query (or more likely a stored procedure) that compares a user's tags to a question's tags and returns a boolean true or false.