基于PHP MYSQL的Tags/Tagging 标签系统设计实例

基于PHP MYSQL的Tags/Tagging 标签系统设计实例 / Tagging With PHP And MySQL

Contents

Abstract
The Database
The Database Connection
Adding A Type
Adding A Tag
Fetching Related Tags

Abstract

With the proliferation of web sites now driven by relational databases, it is not surprising that new and innovative ways continue to emerge for data relationships. Traditionally, groups of data have been stored in categories, but the demand for better and more prolific relationships has seen the evolution of tags. Tags are not replacing categories, but are further extending the relationship between data and objects within databases.

This tutorial focuses on a simple and effective tagging solution based on a bookmarking concept, where each opject in the database referred to by its URL. Each URL, in turn, has one or more tags associated with it. Any other object that shares one of more of these tags can be retrieved to produce an array of related data.
The Database

Before moving onto the schema, a quick expanation is required of our needs. The database needs to be able to store each target url, a name for the target, and of course the tags. The database needs to adhere to basic normalization rules so that each item within the database can be referenced by its ID. The schema will look like this:

The first table in the schema contains the tags themselves. Each tag name (tag_name) is UNIQUE within the system. This allows for using the tag ID’s as a primary parent that other ID’s can relate to with a FOREIGN KEY. The use of foreign keys means the database can handle the referential integrity of the data. For example, the third table, phpro_tag_targets, uses a foreign key to reference the parent tag_id in the phpro_tags table. Should this tag be removed, then all tag targets that have that key, will be removed from the phpro_tag_targets table also. Likewise with the phpro_tag_types table.

The phpro_tag_targets also references this table for the type of object being stored. this could be video, image, articles etc. Should a tag type be deleted from the phpro_tag_type table, then all records in the phpro_tag_targets table that reference it, will be deleted also. This will save us a lot of code in our application and gain us a significant speed increase.
The Database Connection

For the purpose of this tutorial, a singleton class is provided to connect to the database. This class definition is included in all files that access the database.

By including the above class definition in the scripts, the database instance can be accessed simply.

Adding A Type

Here is a simple form and script to add a tag type. The types may be videos or tutorials or anything that allows to refine a search. eg: searching for all video’s tagged with “PHP”.

The script above provides a simple interface to add tag types. It has some loose validation which could be supplanted with an exception class or custom error handling, but for demonstration purposes, will work fine.
Adding A Tag

With the Types in place, a form and script are needed to tag some target URL’s. The database design uses a foreign key relationship from the target URL and the tag itself.

When considering the interface to the to the tags, it is important to consider how the end user will enter their tags. The simplest way for the end user is via a simple form text field where the end user enters a comma delimtted list of tags.

The comma delimitted list of tags is put into an array with the PHP explode() function and the array traversed to enter the tags.

The tag engine also needs to check if a tag already exists in the tags table, as the tag names are UNIQUE and may not occur twice within the table. A simple SELECT query could be used to check if a tag is in the database,m and if it is, then continue to the next tag. But to save this added query, an INSERT IGNORE query can be used to ignore the error generate when a duplicate tag is found. The tag will not be INSERT’ed and the error will be IGNORE’ed.

Of course, when entering, or tagging a target URL, the tag is entered if it does not exist, and the tag is given an tag_id. It is this tag_id that is used to by the tag target to relate to it. When entering the tag target url, the tag_id has to be SELECT’ed from the tags table. Of course, this requires more than a single SQL query to achieve, and to minimise the impact on the database, a single transaction can be used to wrap it all up. The PDO instance from the database provides easy asscess to transactions.

Here is a short list of some tag target URL’s that can be added to the tag system. Just give them a simple name and enter the URL and the tags in a comma delimitted list in the provided form.

Note that the URL’s are all links to all but the last item, are articles here on PHPRO.ORG, the last item is an image of yours truly giving a presentation on Managing Hierarchical Data with PHP and MySQL or MPTT as it has become known. Note that this last item is tagged with “mysql” and “mptt”.
Fetching Related Tags

When retrieving tags related to the Managing Hierarchical Data with PHP and MySQL tag target, the system needs to return, all items tagged with any tag associated with it. That is, any item tagged with “mysql” or “mptt”. There are four such items in the list above.

The URL in the string http://blog.cnlabs.net/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html is provided as an example only, the URL could have come from the query string via $_SERVER variables.
The task of retrieving the related tag targets is made simple by the database design and will require a single query with SELF JOIN, thus providing maximum speed when queried.

The above code will produce a list of tag names which link to thier respective URLs such as this:

Login Auth
dropdown select
mptt mysql
kev pic

This tutorial provides a simple yet effective tagging system. Feel free to adapt it as needed as there is wide range of customizations and additions that could be made.