Simple Hashtag System With PHP MySQL (Free Download)

Welcome to a tutorial on how to create a simple hashtag system with PHP and MySQL. Comments, images, videos, audio, or whatever else – One can never get away from hashtags in this age. But just how do we “build hashtags” into a system? Read on for an example!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

PART 1) THE DATABASE

1A) POSTS TABLE

-- (A) POSTS
CREATE TABLE `posts` (
  `post_id` bigint(20) NOT NULL,
  `post_txt` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `posts`
  ADD PRIMARY KEY (`post_id`),
  ADD FULLTEXT KEY `post_txt` (`post_txt`);
 
ALTER TABLE `posts`
  MODIFY `post_id` bigint(20) NOT NULL AUTO_INCREMENT;

Everyone has different types of content to add hashtags to – Audio, video, images, comments, posts, articles, etc… So to keep things simple, we will just attach hashtags to dummy posts.

• post_id Primary key and auto-increment.
• post_txt The content itself.

 

 

1B) HASHTAGS TABLE

-- (B) TAGS
CREATE TABLE `tags` (
  `post_id` bigint(20) NOT NULL,
  `tag` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `tags`
  ADD PRIMARY KEY (`post_id`,`tag`);

Hashtags that are attached to each post.

• post_id Partial primary key and foreign key.
• tag Name of the tag.

 

 

PART 2) PHP HASHTAGS LIBRARY

<?php
class Tag {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {{
    $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
    ]);
  }

  // (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) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) SAVE POST + TAGS
  function save ($txt, $tags=null, $id=null) {
    // (D1) ADD/UPDATE POST
    if ($id==null) {
      $this->query("INSERT INTO `posts` (`post_txt`) VALUES (?)", [$txt]);
    } else {
      $this->query("UPDATE `posts` SET `post_txt`=? WHERE `post_id`=?", [$txt, $id]);
    }

    // (D2) DELETE OLD TAGS
    if ($id==null) { $id = $this->pdo->lastInsertId(); }
    else { $this->query("DELETE FROM `tags` WHERE `post_id`=?", [$id]); }

    // (D3) INSERT NEW TAGS
    if ($tags != null) {
      $tags = json_decode($tags, true);
      $sql = "INSERT INTO `tags` (`post_id`, `tag`) VALUES ";
      $data = [];
      foreach ($tags as $t) {
        $sql .= "(?,?),";
        $data[] = $id; $data[] = $t;
      }
      $this->query(substr($sql, 0, -1).";", $data);
    }

    // (D4) DONE
    return true;
  }

  // (E) GET POSTS
  function get ($tag=null) {
    // (E1) GET ALL
    if ($tag==null) { $this->query("SELECT * FROM `posts`"); }

    // (E2) SEARCH BY TAG
    else {
      $sql = "SELECT p.* FROM `tags` `t`
              JOIN `posts` `p` USING (`post_id`)
              WHERE `tag`=?";
      $this->query($sql, [$tag]);
    }

    // (E3) FETCH!
    return $this->stmt->fetchAll();
  }
}

// (F) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (G) NEW HASH TAG OBJECT
$_TAG = new Tag();

Next, we have a seemingly massive PHP library to work with the database. But keep calm and look carefully.

• (A, B, G) When $_TAG = new Tag() is created, the constructor connects to the database automatically. The destructor closes the connection.
• (C) A helper function to run an SQL statement.
• (D & E) The “actual” hashtag functions.
  • get() Get all posts, or search for posts with the specified tag.
  • save() Add a new post, or update an existing post.
• (F) Self-explanatory. Change the database settings to your own.

 

 

PART 3) AJAX HANDLER

<?php
if (isset($_POST["req"])) {
  require "2-lib-tag.php";
  switch ($_POST["req"]) {
  // (A) GET POSTS
  case "get":
    echo json_encode($_TAG->get(
      isset($_POST["tag"]) ? $_POST["tag"] : null
    ));
    break;

  // (B) SAVE POST
  case "save":
    echo $_TAG->save(
      $_POST["txt"],
      isset($_POST["tags"]) ? $_POST["tags"] : null,
      isset($_POST["id"]) ? $_POST["id"] : null
    ) ? "OK" : $_TAG->error;
    break;
}}

Of course, the library will not do anything by itself. This is a simple endpoint to accept AJAX requests and how it works is very simple – Just send $_POST["req"] to specify the request, followed by the required parameters.

• $_POST["req"] = "get" Get all posts. If $_POST["tag"] is provided, will search for all posts that contain the specified tag.
• $_POST["req"] = "save" Self-explanatory. Save a given post, along with the given hashtags.

 

PART 4) DEMO HASHTAG PAGE

4A) THE HTML

<!-- (A) ADD POST -->
<form onsubmit="return htag.save()">
  <input type="text" required id="add"
         placeholder="Post some dummy messages with #hashtags">
  <input type="submit" value="Post">
</form>
 
<!-- (B) SEARCH BY TAG -->
<form onsubmit="return htag.get(true)">
  <input type="text" id="search"
         placeholder="Then search by hashtag">
  <input type="submit" value="Search">
</form>
 
<!-- (C) POSTS -->
<div id="posts"></div>

Well, this is just a dummy page to demonstrate how we can attach hashtags to posts.

1. An “add post” HTML form.
2. Search for posts with the given hashtag.
3. An empty <div> to show the posts.

 

 

4B) JAVASCRIPT INITIALIZE

var htag = {
  // (A) INITIALIZE
  hAdd : null, // html add post field
  hSearch : null, // html search field
  hPosts : null, // html posts <div>
  init : () => {
    // (A1) GET HTML ELEMENTS
    htag.hAdd = document.getElementById("add");
    htag.hSearch = document.getElementById("search");
    htag.hPosts = document.getElementById("posts");
 
    // (A2) LOAD POSTS
    htag.get();
  },
  // ...
};
window.onload = htag.init;

On window load, htag.init() will run. Nothing much going on here, just getting some of the related HTML elements.

 

4C) JAVASCRIPT SHOW POSTS

// (B) GET/SEARCH POSTS
get : search => {
  // (B1) FORM DATA
  let data = new FormData();
  data.append("req", "get");
  if (search == true) {
    data.append("tag", htag.hSearch.value);
  }
 
  // (B2) FETCH & DRAW HTML
  fetch("3-ajax-tag.php", { method : "post", body : data })
  .then(res => res.json())
  .then(data => {
    htag.hPosts.innerHTML = "";
    data.forEach(post => {
      let row = document.createElement("div");
      row.className = "row";
      row.innerHTML = post["post_txt"];
      htag.hPosts.append(row);
    });
  });
  return false;
},

Remember the AJAX endpoint 3-ajax-tag.php? We are just making a $_POST["req"] = "get" fetch request to get all the posts, and showing them in HTML.

 

4D) JAVASCRIPT SAVE POST

// (C) SAVE POST
save : () => {
  // (C1) GET TAGS
  let matches = htag.hAdd.value.match(/(^|\s)(#[a-z\d-]+)/g),
  tags = matches === null ? null : [...new Set(matches)];
  if (tags!=null) {
    tags.forEach((t, i) => { tags[i] = t.substring(2); });
  }
 
  // (C2) FORM DATA
  let data = new FormData();
  data.append("req", "save");
  data.append("txt", htag.hAdd.value);
  if (tags!=null) { data.append("tags", JSON.stringify(tags)); }
 
  // (C3) POST + UPDATE HTML LIST
  fetch("3-ajax-tag.php", { method : "post", body : data })
  .then(res => res.text())
  .then(txt => {
    if (txt == "OK") {
      htag.hAdd.value = "";
      htag.get();
    } else { alert(txt); }
  });
 
  // (C4) DONE
  return false;
}

Finally, this one is just long-winded. We are sending $_POST["req"] = "save" to the AJAX handler once again. With the text and tags, of course.

 

 

 

DUMB. HASHTAGS SHOULD BE PROCESSED WITH PHP.

My developer senses are tingling. I can hear the trolls screaming “it’s so dumb, just use PHP to separate the hashtags from the text”. The reason why I use Javascript to separate the hashtags from the text is simple:

• Not all people are working with short posts and comments.
• Some people may prefer to have 2 separate fields.
• For example, one field for “image caption”, another for “add hashtags to image”.

So yep, it makes sense for the PHP library to process the “content” and “hashtags” separately. If it is better for you to separate the hashtags directly on the server side – By all means, please do so. I am just catering to the “general public” here.

 

COMPATIBILITY CHECKS

• Arrow Functions – CanIUse
• Fetch – CanIUse

This example will work on most modern “grade A” browsers.