Porto

Tagging With PHP And MySQL

Tagging With PHP And MySQL

by Kevin Waterson

Contents

  1. Abstract
  2. The Database
  3. The Database Connection
  4. Adding A Type
  5. Adding A Tag
  6. 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:

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.


<?php

class db{

/*** Declare instance ***/
private static $instance NULL;

/**
*
* the constructor is set to private so
* so nobody can create a new instance using new
*
*/
private function __construct() {
  
/*** maybe set the db name here later ***/
}

/**
*
* Return DB instance or create intitial connection
*
* @return object (PDO)
*
* @access public
*
*/
public static function getInstance() {

if (!
self::$instance)
    {
    
self::$instance = new PDO("mysql:host=localhost;dbname=pro_tags"'username''password');;
    
self::$instance-> setAttribute(PDO::ATTR_ERRMODEPDO::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.


<?php
    
include "db.class.php";

    
$db db::getInstance();
?>

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


<form action="" method="post">
<dt><h3>Tag Types<h3></dt>
<dd><input type="text" name="tag_type_name" maxlength="30"/></dd>
<dd><input type="submit" /></dd>
</dt>
</form>

<?php
    
/*** begin with some validation ***/
    
if(!isset($_POST['tag_type_name']))
    {
        
/*** if no POST is submited ***/
        
$msg 'Please Submit a tag type';
    }
    elseif(
strlen($_POST['tag_type_name']) == 0)
    {
        
/*** if tag is too short ***/
        
$msg 'Tag Type must have a value';
    }
    elseif( 
strlen($_POST['tag_type_name']) > 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';
        }
    }
        

?>

<h4><?php echo $msg?></h4>

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.


<?php
    
/*** include the db class ***/
    
include 'db.class.php';

    
$sql "SELECT
        tag_type_id,
        tag_type_name
        FROM
        phpro_tag_types
        ORDER BY
        tag_type_name"
;
    
$stmt db::getInstance()->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++;
    }
?>
<form action="" method="post">
<dt>Tag Type</dt>
<select name="tag_type_id">
<?php
    
foreach( $types as $id=>$type )
    {
        echo 
'<option value="'.$id.'">'.$type.'</option>';
    }
?>
</select>
<dt>Tag Name</dt>
<dd><input type="text" name="tag_target_name" maxlength="30" /></dd>
<dt>Tag URL</dt>
<dd><input type="text" name="tag_target_url" /></dd>

<dt>Tags</dt>
<dd><input type="text" name="tags" maxlength="100"/></dd>
<dd><input type="submit" /></dd>
</dt>
</form>

<?php
    
/*** begin with some validation ***/
    
if(!isset($_POST['tag_type_id'], $_POST['tag_target_url'], $_POST['tags']))
    {
        
/*** if no POST is submited ***/
        
$msg 'Please Submit a tag';
    }
    elseif(
filter_var($_POST['tag_type_id'], FILTER_VALIDATE_INT, array("min_range"=>1"max_range"=>100)) == false)
    {
        
/*** if tag is too short ***/
        
$msg 'Invalid Tag Type';
    }
    elseif( 
strlen($_POST['tag_target_url']) == )
    {
        
/*** if tag is too long ***/
        
$msg 'Tag target is required';
    }
    elseif( 
strlen($_POST['tags']) == )
    {
        
$msg 'Tag Required';
    }
    elseif(  
strlen($_POST['tag_target_name']) == )
    {
        
$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();
        }
    }
?>

<h4><?php echo $msg?></h4>

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.

  • http://phpro.org/tutorials/Creating-Dropdowns-with-PHP-and-Xajax.html has tags "xajax" and "dropdown"
  • http://phpro.org/tutorials/Design-Patterns.html has tag "design patterns"
  • http://phpro.org/tutorials/Basic-Login-Authentication-with-PHP-and-MySQL.html has tags "mysql" "authentication"
  • http://phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html has tags "mysql" "mptt"
  • http://phpro.org/tutorials/Dropdown-Select-With-PHP-and-MySQL.html has tags "mysql" "dropdown"
  • http://www.flickr.com/photos/yogomozilla/1492877513/in/pool-sydphp/ has tags "kevin" "mptt" and is an image

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.


<?php

    $tag_target_url 
'http://phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html';

    try
    {
        include 
'db.class.php';

        
$sql "SELECT
            U.*
            FROM
            phpro_tag_targets U
            JOIN
            phpro_tag_targets T
            WHERE
            U.tag_id = T.tag_id
            AND
            T.tag_target_url = :tag_target_url
            GROUP BY
            tag_target_url"
;
        
$stmt db::getInstance()->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 '<ul>';
        foreach(
$res as $val)
        {

            
$msg .= '<li><a href="'.$val['tag_target_url'].'">'.$val['tag_target_name'].'</a></li>'."\n";
        }
        
$msg .= '</ul>';
    }
    catch(
Exception $e)
    {
        
$msg 'Unable to process tag type';
    }
?>

<?php echo $msg?>

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

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.