ASSUMPTIONS

Most of you guys here should already have an existing system, or a tag system will not make any sense as an independent system. So instead of reinventing the wheel, we shall not touch on how to create posts, products, images, or whatever you want to tag; This guide will purely touch on tags only.

Also, I am going to assume that you guys are comfortable enough with the basics – PHP, SQL, HTML, CSS, Javascript, and more. We will not go into tiny boring little details such as “what is AJAX and JSON”.

OVERVIEW

There will be 3 parts to this guide and system:

• The Database – Where we store all the tags.
• Server-side Scripts – PHP libraries that will do all the process. Save tags to the database, get the tags, etc…
• Client-side Scripts – The user interface, HTML, CSS, and Javascripts.

TAGS TABLE

CREATE TABLE `tags` (
  `post_id` int(11) NOT NULL,
  `tag` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `tags`
  ADD PRIMARY KEY (`post_id`,`tag`);
COMMIT;

Yep, this simple table is actually all we need.

DEMO POSTS TABLE

For the sake of completeness for the beginners, this is an example posts table that we will be adding tags to – But please take note, we can actually tag anything with a proper unique ID.

CREATE TABLE `posts` (
  `post_id` int(11) NOT NULL,
  `post_title` text NOT NULL,
  `post_txt` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `posts` (`post_id`, `post_title`, `post_txt`) VALUES
(1, 'Stream of Rainbow', 'Can the predecessor hope the diesel? The unknown bombs whatever buried manpower. The crucial boy tenders a developed blurb. A top law clicks before a release. Why does our employee monitor the many lawyer? An ear fumes.'),
(2, 'Misty in the Wings', 'When will a competing helmet react in a noise? A paragraph acts above the agenda! A kept delight repairs a controlling crush. Can the procedure vanish? The documented rectangle inconveniences a hysterical luggage. The learned tobacco screams.'),
(3, 'Lord of the Minks', 'The undone complaint collapses past an east estate. The insulting nurse flames the era. A willed hierarchy surfaces. A tentative wife bites the consenting fence.'),
(4, 'Ice in the Scent', 'A futile pump bangs against the cider. A night stomachs a wizard. How does the mania originate? Can a reject wreck a taking battle?');

ALTER TABLE `posts`
  ADD PRIMARY KEY (`post_id`);

ALTER TABLE `posts`
  MODIFY `post_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

CONFIG FILE

The first of the server-side scripts that we have to create is a config file to store all our settings and stuff. Please remember to change the database settings to your own.

DATABASE LIBRARY

pdo = new PDO(
        $str, DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
          PDO::ATTR_EMULATE_PREPARES => false
        ]
      );
    }

    // ERROR - CRITICAL STOP - THROW ERROR MESSAGE
    catch (Exception $ex) {
      print_r($ex);
      die();
    }
  }

  function __destruct () {
  // __destruct() : close connection when done

    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  function exec ($sql, $data=null) {
  // exec() : run insert, replace, update, delete query
  // PARAM $sql : SQL query
  //       $data : array of data

    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      $this->lastID = $this->pdo->lastInsertId();
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    $this->stmt = null;
    return true;
  }

  function start () {
  // start() : auto-commit off

    $this->pdo->beginTransaction();
  }

  function end ($commit=1) {
  // end() : commit or roll back?

    if ($commit) { $this->pdo->commit(); }
    else { $this->pdo->rollBack(); }
  }

  function fetchAll ($sql, $cond=null, $key=null, $value=null) {
  // fetchAll() : perform select query (multiple rows expected)
  // PARAM $sql : SQL query
  //       $cond : array of conditions
  //       $key : sort in this $key=>data order, optional
  //       $value : $key must be provided. If string provided, sort in $key=>$value order. If function provided, will be a custom sort.

    $result = [];
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($cond);
      // Sort in given order
      if (isset($key)) {
        if (isset($value)) {
          if (is_callable($value)) {
            while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
              $result[$row[$key]] = $value($row);
            }
          } else {
            while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
              $result[$row[$key]] = $row[$value];
            }
          }
        } else {
          while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
            $result[$row[$key]] = $row;
          }
        }
      }
      // No key-value sort order
      else {
        $result = $this->stmt->fetchAll();
      }
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    // Return result
    $this->stmt = null;
    return count($result)==0 ? false : $result ;
  }

  function fetch ($sql, $cond=null, $sort=null) {
  // fetch() : perform select query (single row expected)
  //           returns an array of column => value
  // PARAM $sql : SQL query
  //       $cond : array of conditions
  //       $sort : custom sort function

    $result = [];
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($cond);
      if (is_callable($sort)) {
        while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
          $result = $sort($row);
        }
      } else {
        while ($row = $this->stmt->fetch(PDO::FETCH_NAMED)) {
          $result = $row;
        }
      }
    } catch (Exception $ex) {
      $this->error = $ex;
      return false;
    }
    // Return result
    $this->stmt = null;
    return count($result)==0 ? false : $result ;
  }

  function fetchCol ($sql, $cond=null) {
  // fetchCol() : yet another version of fetch that returns a flat array
  // I.E. Good for one column SELECT `col` FROM `table`

    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($cond);
    $result = $this->stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    return count($result)==0 ? false : $result;
  }
}

Next, we have a database library here, that will do all the SQL heavy lifting work.

TAG LIBRARY

fetchCol("SELECT `tag` FROM `tags` WHERE `post_id`=?", [$id]);
  }

  function reTag($id, $tags=null) {
  // reTag() : replace tags for the given post ID
  // PARAM $id : post ID
  //       $tags : array of tags
    // Auto-commit off
    $this->start();

    // Remove old tags first
    $pass = $this->exec(
      "DELETE FROM `tags` WHERE `post_id`=?", [$id]
    );

    // Add new tags - If any
    // Might be a good idea to limit the total number of tags...
    if ($pass && is_array($tags) && count($tags)>0) {
      $sql = "INSERT INTO `tags` (`post_id`, `tag`) VALUES ";
      $data = [];
      foreach ($tags as $t) {
        $sql .= "(?,?),";
        $data[] = $id;
        $data[] = $t;
      }
      $sql = substr($sql, 0, -1);
      $pass = $this->exec($sql, $data);
    }

    // End - commit or rollback
    $this->end($pass);
    return $pass;
  }
}

This is a simple library that will work with the tags.

AJAX HANDLER

 0,
      "message" => "Invalid request"
    ]);
    break;

  // GET TAGS FOR POST
  case "get":
    $tags = $tagDB->getAll($_POST['post_id']);
    echo json_encode([
      "status" => is_array($tags) ? 1 : 0,
      "message" => $tags
    ]);
    break;
  // SAVE TAGS
  case "save":
    $pass = $tagDB->reTag($_POST['post_id'], json_decode($_POST['tags']));
    echo json_encode([
      "status" => $pass ? 1 : 0,
      "message" => $pass ? "OK" : $tagDB->error
    ]);
    break;
}

Of course, the library themselves will not do anything on their own, and so, we have to create this AJAX handler that will process the user requests. How it works is very simple, we give it a $_POST['req'] to specify which request we want, followed by the required parameters.

THE HTML

getAll($postID);

// HTML ?>


  
    

      Lord of the Minks
    
    

      The undone complaint collapses past an east estate. The insulting nurse flames the era. A willed hierarchy surfaces. A tentative wife bites the consenting fence.
    

    
    

      
MANAGE TAGS

      
      
%s
", $t);
          }
        }
      ?>

      
      
      
        Enter tags below, separate each with a comma.

        Click on existing tags above to remove.

        Remember to hit save to commit changes.