Simple Tag System With PHP MySQL – Free Code Download

Welcome to a tutorial on how to build a tag system with PHP and MySQL. Tags are a common feature these days, and it can pretty much be used to describe anything – Posts, products, pictures, videos, audio, and more. Building one yourself is not that difficult actually.

A simple tag system with PHP and MySQL only consists of a few core components:

  1. A tags database table to store the tag information.
  2. A tags PHP library to manage the tags (add, update, delete).
  3. Lastly, implementation into the HTML pages itself to display and/or manage the tags.

So just how do we build a tag system? Let us walk through an example in this guide – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes Simple Tags Useful Bits & Links
The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

  • Create a database and import the 1-database.sql file.
  • Change the database settings in 2-tags-lib.php to your own.
  • Run 3-dummy-data.php for a demo on how to use the tags library to save tags.
  • Lastly, 4-books-demo.php on how to fetch and display the tags.

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

SIMPLE TAGS SYSTEM

All right, let us now get into the development of a simple tags system with PHP and MySQL.

 

1) TAGS DATABASE TABLE

1-database.sql
-- (A) TAGS
CREATE TABLE `tags` (
  `id` int(11) NOT NULL,
  `tag` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

Yep, 2 fields are all we need to store the tags.

  • id This is the primary and foreign key – Tie this to whatever you want to tag. A post ID, product ID, video ID, or whatever else.
  • tag The tag itself. Also a primary key to prevent duplicates, improve search performance.

 

 

2) PHP TAGS LIBRARY

2-tags-lib.php
<?php
class Tags {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {
    try {
      $this->pdo = new PDO(
        "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET, 
        DB_USER, DB_PASSWORD, [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
      );
    } catch (Exception $ex) { die($ex->getMessage()); }
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }

  // (C) HELPER FUNCTION - EXECUTE SQL QUERY
  function query ($sql, $data=null) {
    try {
      $this->stmt = $this->pdo->prepare($sql);
      $this->stmt->execute($data);
      return true;
    } catch (Exception $ex) {
      $this->error = $ex->getMessage();
      return false;
    }
  }

  // (D) SAVE TAGS
  function save ($id, $tags=null) {
    // (D1) AUTO-COMMIT OFF
    $this->pdo->beginTransaction();

    // (D2) DELETE OLD ENTRIES
    $pass = $this->query("DELETE FROM `tags` WHERE `id`=?", [$id]);

    // (D3) ADD NEW ENTRIES (IF ANY)
    if ($pass && is_array($tags)) {
      $sql = "INSERT INTO `tags` (`id`, `tag`) VALUES ";
      $data = [];
      foreach ($tags as $t) {
        $sql .= "(?,?),";
        $data[] = $id; $data[] = $t;
      }
      $sql = substr($sql, 0, -1);
      $pass = $this->query($sql, $data);
    }

    // (D4) RESULTS
    if ($pass) { $this->pdo->commit(); }
    else { $this->pdo->rollBack(); }
    return $pass;
  }

  // (E) GET TAGS FOR ID
  function get ($id) {
    $this->query("SELECT * FROM `tags` WHERE `id`=? ORDER BY `tag`", [$id]);
    $tags = [];
    while ($row = $this->stmt->fetch()) { $tags[] = $row['tag']; }
    return $tags;
  }

  // (F) GET ALL TAGS & USAGE COUNT
  function getCount ($limit=5) {
    $this->query("SELECT `tag`, COUNT(`tag`) `c` FROM `tags` GROUP BY `tag` ORDER BY `c` DESC LIMIT $limit");
    $tags = [];
    while ($row = $this->stmt->fetch()) { $tags[$row['tag']] = $row['c']; }
    return $tags;
  }
}

// (G) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');

// (H) NEW TAGS OBJECT
$TAGS = new Tags();

Yikes! This library looks complicated, but keep calm and look carefully.

  • A & B: When the $TAGS = new Tags() object is created, the constructor will connect to the database automatically; The destructor will close the connection when done.
  • C: function query() is only a simple helper function for running SQL queries.
  • D To F: There are only 3 essential tag functions.
    • function save() to update the tags. Just provide $id and an array into $tags – If  $tags = null, that will remove all the tags from $id.
    • function get() Self-explanatory, get tags.
    • function getCount() Get “tag usage stats”, which is the most common tag.
  • G & H: Self-explanatory again. A reminder to change the database settings to your own.

 

 

3) SAVING & ADDING TAGS

3-dummy-data.php
// FOR THIS DEMO, WE HAVE 2 BOOKS
// ID 88 = Lord Of The Minks
// ID 99 = Ice In The Scent

// (A) LOAD LIBRARY
require "2-tags-lib.php";

// (B) ATTACH TAGS
echo $TAGS->save(88, ["action", "fantasy", "horror", "popular", "thriller"])
  ? "OK" : $TAGS->error;

echo $TAGS->save(99, ["action", "popular", "romance", "sci-fi"])
  ? "OK" : $TAGS->error;

// (C) YOU CAN ALSO USE GETCOUNT() TO FETCH THE COMMON TAGS
$tags = $TAGS->getCount();
print_r($tags);

Now that the foundations are done – Here is a real quick demo on using the library to add/update the tags.

 

4) FETCH & DISPLAY TAGS

4-book-demo.html
<!-- (A) TAGS LIBRARY -->
<?php require "2-tags-lib.php"; ?>
 
<!-- (B) BOOK ID 88 -->
<div>
  <h1>Lord Of The Minks</h1>
  <?php
  $tags = $TAGS->get(88);
  foreach ($tags as $t) { echo "<div class='tag'>$t</div>"; }
  ?>
</div>
 
<!-- (C) BOOK ID 99 -->
<div>
  <h1>Ice In The Scent</h1>
  <?php
  $tags = $TAGS->get(99);
  foreach ($tags as $t) { echo "<div class='tag'>$t</div>"; }
  ?>
</div>

Lastly, all that’s left is to fetch the tags and show them in the HTML page.

 

 

USEFUL BITS & LINKS

That’s all for the code, and here are a few small links and extras that may be useful.

 

NO ADMIN PANEL?

Well, I figured that some will already have an existing admin panel, and not reinventing the wheel is the best course of action to keep this guide simple as possible – Thus the raw “no interface, just use the library to save tags”. If you don’t already have a user login and admin panel, follow up with the links below.

P.S. The lazy alternate way is to use PHPMyAdmin or MySQL Workbench to directly manage the tags in the database. But of course, this option will be “for developers only”. You are still stuck with “must build an admin panel” if the users are not sophisticated enough.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *