ticket #53 is for tracking any work done on this.
Both Gallery and Coppermine have a notion of keywords, it is possible to add those to each picture and then later perform searches on those, yet the implementation is far from perfect.
The idea behind tagging is that if you have hundreds and thousands of pictures then you want to be able to find pictures about a particular subject fast later on, having tags adds an additional dimension to the collection besides the traditional albums approach.
I have been playing with Flickr and it has a rather nice implementation, basically you have an additional textbox for each image where you can enter words or phrases (surrounded with quotes) and they have a lot of features which make tagging a lot easier. For example, if you upload multiple pictures, you can give them all the same tags by filling a single textbox. Additionally, when editing tags you can see all the tags you have used previously, clicking on one adds it to the textbox, clicking it again removes it again. Go ahead, play with Flickr and see whether you like it.
As far as I can see, they do not simply store the tags in a text field for each image, there must be a separate table for tags, because they have the feature where you can edit a tag and all the pictures that have it will reflect the change.
So this is what I have i mind. We need 2 more tabels: plogger_tags and plogger_tag2picture (which is a relation table).
mysql> describe plogger_tags; +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | id | bigint(20) unsigned | | PRI | NULL | auto_increment | | tag | char(50) | YES | MUL | NULL | | | tagdate | datetime | YES | | NULL | | | formatted | char(50) | YES | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
mysql> describe plogger_tag2picture; +------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+-------+ | tag_id | bigint(20) unsigned | | MUL | 0 | | | picture_id | bigint(20) unsigned | | MUL | 0 | | | tagdate | datetime | YES | | NULL | | +------------+---------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
tagdate column is used to keep track of when a tag or a relation between tag and picture was created. formatted tag (for using in URLs) is stored in it's own field.
tags.tagdate - used to order tags in order they were created or get a list of new tags tag2picture.tagdate - used to create a timeline of used tags
Let's assume that we have a new fresh Plogger installation with no pictures uploaded. We are going to upload one, let's say that it's a picture of a girl sitting behind a computer. The filename is IMG3359.jpg. There is a textbox for entering tags and you'll probably want to enter the following in it: "girl computer"
If the form is submitted (picture gets uploaded), then for each tag that didn't exist before a new row will be inserted into plogger_tags. Additionaly the relation between the picture and tag gets stored.
Something like this:
$tags = explode(' ',$_POST['tags']);
foreach($tags as $tag) {
$sql = 'SELECT id FROM plogger_tags WHERE tag = '$tag';
$stmt = mysql_query($sql);
$tag_id = mysql_fetch_assoc($stmt);
// no such tag, create it
if (empty($tag_id)) {
$sql = 'INSERT INTO plogger_tags (tag) VALUES ('$tag')";
$stmt = mysql_query($sql);
// get the id for the last insert
$tag_id = mysql_insert_id();
}
$sql = "INSERT INTO plogger_tag2picture (tag_id,picture_id) VALUES ($tag_id,$_POST['picture_id'])";
mysql_query($sql);
};
Here's the result:
mysql> select * from plogger_tags; +----+----------+ | id | tag | +----+----------+ | 1 | girl | | 2 | computer | +----+----------+ 2 rows in set (0.00 sec) mysql> select * from plogger_tag2picture; +--------+------------+ | tag_id | picture_id | +--------+------------+ | 1 | 1 | | 2 | 1 | +--------+------------+ 2 rows in set (0.00 sec) mysql> select id,path from plogger_pictures where id = 1; +----+-------------------------------------+ | id | path | +----+-------------------------------------+ | 1 | IMG3359.jpg | +----+-------------------------------------+ 1 row in set (0.00 sec)
Once this is in place we can implement the following:
which will run the query
mysql> SELECT p.id,path,tag FROM plogger_pictures p, plogger_tags tag, plogger_tag2picture t2p WHERE (p.id = t2p.picture_id AND t2p.tag_id = tag.id AND tag = 'girl'); +----+-------------------------------------+------+ | id | path | tag | +----+-------------------------------------+------+ | 1 | IMG3359.jpg | girl | +----+-------------------------------------+------+ 1 row in set (0.00 sec)
As you can see - a single query will give us a list of pictures which match the tag. This approach allows for easy renaming of tags. We can also count them to get something like http://www.flickr.com/photos/tags/
Possible features
- RSS feed for a tag
- slideshow for a tag
- Some kind of tag manager, where tags can be renamed and deleted.
- tag categories? Possible categories would include "Places", "People", "Events", "Hobbies", and whatever else you can think of. Adding tags to pictures would still work as it does now, tag categories gives yet another dimension (new browsing possibilities) to tags
- This does potentially have a few pitfalls that need to be worked out before we implement this:
- Can you tag a picture with a category of tags? Does that then apply all the tags in that category to the picture? Or are tag categories just for management and searching purpose?
- Instead of categories we could possibly provide the option to "link" tags. That is, tag A could have two other tags, B and C, linked to it, making tag A a "category-tag". This might however cause more problems if you are able to tag pictures with these "category-tags".
- This does potentially have a few pitfalls that need to be worked out before we implement this:
DDL
CREATE TABLE `plogger_tag2picture` ( `tag_id` bigint(20) unsigned NOT NULL default '0', `picture_id` bigint(20) unsigned NOT NULL default '0', `tagdate` datetime default NULL, KEY `tag_id` (`tag_id`), KEY `picture_id` (`picture_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `plogger_tags` ( `id` bigint(20) unsigned NOT NULL auto_increment, `tag` char(50) NOT NULL default '', `tagdate` datetime NOT NULL default '0000-00-00 00:00:00', `formatted` char(50) NOT NULL default '', PRIMARY KEY (`id`), KEY `tag` (`tag`), KEY `formatted` (`formatted`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
