NHANWEB

Tagging với PHP và MySQL

Nguyễn Duy Nhân

Bài viết này mình sưu tầm trên PHPPro để dành đọc dần. Phiên bản tiếng Anh của nó hơi bị nhiều thông tin.

Tagging

Bài viết của tác giả bạn đọc tại đây.

Phần nội dung bên dưới là mình backup để đọc dần.

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: 

CREATE TABLE phpro_tags (
  tag_id INT(11) NOT NULL auto_increment,
  tag_name varchar(30) NOT NULL,
  PRIMARY KEY  (tag_id),
  UNIQUE KEY tag_name (tag_name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE phpro_tag_types (
  tag_type_id INT(1) NOT NULL auto_increment,
  tag_type_name varchar(30) NOT NULL,
  PRIMARY KEY (tag_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE phpro_tag_targets (
  tag_target_id INT(11) NOT NULL auto_increment,
  tag_id INT(11) NOT NULL ,
  tag_target_name varchar(30) NOT NULL,
  tag_target_url varchar(255) NOT NULL,
  tag_type_id INT(1) NOT NULL,
  PRIMARY KEY  (tag_target_id),
  FOREIGN KEY (tag_id) REFERENCES phpro_tags(tag_id) ON DELETE CASCADE,
  FOREIGN KEY (tag_type_id) REFERENCES phpro_tag_types(tag_type_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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. 

 setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
return self::$instance;
}

/**
*
* Like the constructor, we make __clone private
* so nobody can clone the instance
*
*/
private function __clone(){
}

} /*** end of class ***/

?>

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

Tag Types

30 ) { /*** if tag is too long ***/ $msg = 'Maximum length of tag type is 30 characters'; } else { /*** if we are here, a tag type was posted ***/ try { /*** include the db class ***/ include 'db.class.php'; /*** assign the string ***/ $tag_type_name = filter_var($_POST['tag_type_name'], FILTER_SANITIZE_STRING); $sql = "INSERT INTO phpro_tag_types ( tag_type_name ) VALUES (:tag_type_name)"; $stmt = db::getInstance()->prepare($sql); $stmt->bindParam('tag_type_name', $tag_type_name); $stmt->execute(); $msg = 'Tag Type Added!'; } catch(Exception $e) { $msg = 'Unable to process tag type'; } } ?>

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.

prepare($sql);
    $stmt->execute();
    $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $types = array();
    $i = 0;
    foreach( $res as $type )
    {
        $types[$type['tag_type_id']] = $type['tag_type_name'];
        $i++;
    }
?>
Tag Type
Tag Name
Tag URL
Tags
1, "max_range"=>100)) == false) { /*** if tag is too short ***/ $msg = 'Invalid Tag Type'; } elseif( strlen($_POST['tag_target_url']) == 0 ) { /*** if tag is too long ***/ $msg = 'Tag target is required'; } elseif( strlen($_POST['tags']) == 0 ) { $msg = 'Tag Required'; } elseif( strlen($_POST['tag_target_name']) == 0 ) { $msg = 'Tag Name is too short'; } elseif( strlen($_POST['tag_target_name']) > 30 ) { $msg = 'Tag Name is too long!'; } else { /*** if we are here, all is well ***/ $tag_type_id = filter_var($_POST['tag_type_id'], FILTER_SANITIZE_NUMBER_INT); $tag_target_url = filter_var($_POST['tag_target_url'], FILTER_SANITIZE_STRING); $tag_target_name = filter_var($_POST['tag_target_name'], FILTER_SANITIZE_STRING); $tags = filter_var($_POST['tags'], FILTER_SANITIZE_STRING); try { /*** explode the tag string ***/ $tag_array = explode(',', $tags); /*** begin the db transaction ***/ db::getInstance()->beginTransaction(); /*** loop of the tags array ***/ foreach( $tag_array as $tag_name ) { /*** insert tag into tags table ***/ $tag = strtolower(trim($tag)); $sql = "INSERT IGNORE INTO phpro_tags (tag_name ) VALUES (:tag_name)"; $stmt = db::getInstance()->prepare($sql); $stmt->bindParam(':tag_name', $tag_name); $stmt->execute(); /*** get the tag ID from the db ***/ $sql = "SELECT tag_id FROM phpro_tags WHERE tag_name=:tag_name"; $stmt = db::getInstance()->prepare($sql); $stmt->bindParam(':tag_name', $tag_name); $stmt->execute(); $tag_id = $stmt->fetchColumn(); /*** now insert the target ***/ $sql = "INSERT INTO phpro_tag_targets (tag_id, tag_target_name, tag_target_url, tag_type_id) VALUES (:tag_id, :tag_target_name, :tag_target_url, :tag_type_id)"; $stmt = db::getInstance()->prepare($sql); $stmt->bindParam(':tag_id', $tag_id); $stmt->bindParam(':tag_target_name', $tag_target_name); $stmt->bindParam(':tag_target_url', $tag_target_url); $stmt->bindParam(':tag_type_id', $tag_type_id); $stmt->execute(); } /*** commit the transaction ***/ db::getInstance()->commit(); $msg = 'Tag Type Added!'; } catch(Exception $e) { $msg = 'Unable to process tag type'; echo $e->getMessage(); } } ?>

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://phpro.org/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. 

prepare($sql);
        $stmt->bindParam(':tag_target_url', $tag_target_url);
        $stmt->execute();
        $res = $stmt->fetchAll(PDO::FETCH_ASSOC);
        /*** loop over the array and create the listing ***/
        $msg = '
'; } catch(Exception $e) { $msg = 'Unable to process tag type'; } ?>

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.

Exit mobile version