Categories Subcategories In PHP MYSQL (Simple Examples)

Welcome to a tutorial on how to deal with categories and subcategories in PHP MYSQL. So you are working on a project that involves multiple categories, but how do we create a database to handle that? How do we get the categories and show them in HTML? Let us walk through some simple examples, read on!

 

 

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) CATEGORY TABLE

-- (A) CATEGORY TABLE
CREATE TABLE `category` (
  `category_id` bigint(20) NOT NULL,
  `parent_id` bigint(20) NOT NULL DEFAULT 0,
  `category_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `category`
  ADD PRIMARY KEY (`category_id`),
  ADD KEY `parent_id` (`parent_id`);
 
ALTER TABLE `category`
  MODIFY `category_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

Yep, the database is as simple as that. We only need a parent_id to “enable” subcategories.

 

 

1B) DUMMY CATEGORIES

-- (B) DUMMY DATA
INSERT INTO `category` (`category_id`, `parent_id`, `category_name`) VALUES
(1, 0, 'Electronics'),
(2, 1, 'Computers'),
(3, 1, 'Cameras'),
(4, 2, 'Desktop'),
(5, 2, 'Laptop');

• Categories with parent_id = 0 are “main categories”.
• Categories with parent_id > 0 are “subcategories”.

 

PART 2) CATEGORY LIBRARY

<?php
class Category {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  protected $pdo = null;
  protected $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 CONNECTION
  function __destruct() {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }
 
  // (C) HELPER - RUN SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
  // ...
}
 
// (J) 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", "");
 
// (K) NEW CATEGORY OBJECT
$_CAT = new Category();

With the database in place, we can now create a library to work with it. This may look intimidating at first, but keep calm and look carefully.

• (A, B, K) When $_CAT = new Category() is created, the constructor automatically connects to the database. The destructor closes the connection.
• (C) A helper function to run an SQL query.
• (J) Database settings – Change to your own.

 

 

PART 3) GET – CATEGORIES TO HTML  LIST

3A) RECURSIVE GET CATEGORIES

// (D) GET ALL CATEGORIES RECURSIVELY
function getAll ($id=0) {
  // (D1) GET CATEGORIES WITH GIVEN PARENT ID
  $this->query("SELECT * FROM `category` WHERE `parent_id`=?", [$id]);
  $cat = [];
  while ($r = $this->stmt->fetch()) {
    $cat[$r["category_id"]] = [
      "n" => $r["category_name"],
      "c" => null
    ];
  }
 
  // (D2) GET CHILDREN
  if (count($cat)>0) {
    foreach ($cat as $id => $c) { $cat[$id]["c"] = $this->getAll($id); }
    return $cat;
  } else { return null; }
}

So far so good? Here comes the slightly painful part, we need a recursive function to get and “format” the categories. For those who are lost:

• The first cycle will get – $cat[1] = ["n"=>"Electronics", "c"=>RECURSION].
• The second cycle will get $cat[2] = ["n"=>"Computers", "c"=>RECURSION] and $cat[3] = ["n"=>"Cameras", "c"=>RECURSION].
• The third cycle will get $cat[4] = ["n"=>"Desktops", "c"=>RECURSION] and $cat[5] = ["n"=>"Laptops", "c"=>RECURSION].

Yep, a recursive function. You may want to restrict the number of levels in your own project, or this will become very resource intensive.

 

3B) DRAW CATEGORIES

<?php
// (A) GET ALL CATEGORIES
require "2-lib-category.php";
$all = $_CAT->getAll();
 
// (B) RECURSIVE DRAW CATEGORIES
function draw ($cat) {
  echo "<ul>";
  foreach ($cat as $id => $c) {
    echo "<li>({$id}) {$c["n"]}";
    if (is_array($c["c"])) { draw($c["c"]); }
    echo "</li>";
  }
  echo "</ul>";
}
draw($all);
?>

Now that we have the category data, all we need is to draw the HTML… With recursion once again.

 

 

PART 4) ADD, UPDATE, DELETE

This part of the tutorial will deal with the “categories admin”, which I see that “most other online tutorials” miss out on. Well, if you don’t have to deal with “admin”, feel free to skip these and you will be a happier person.

 

4A) ADD CATEGORY

// (F) ADD NEW CATEGORY
function add ($name, $parent=0) {
  $this->query(
    "INSERT INTO `category` (`category_name`, `parent_id`) VALUES (?, ?)",
    [$name, $parent]
  );
  return true;
}

Adding a new category is easy. Just specify the category name and parent.

 

4B) UPDATE CATEGORY

// (E) GET ALL CHILDREN CATEGORY ID
function getChildren ($id) {
  $this->query("SELECT `category_id` FROM `category` WHERE `parent_id`=?", [$id]);
  $cat = $this->stmt->fetchAll(PDO::FETCH_COLUMN);
  foreach ($cat as $cid) {
    $cat = array_merge($cat, $this->getChildren($cid));
  }
  return $cat;
}
 
// (G) UPDATE CATEGORY
function update ($name, $id, $parent) {
  // (G1) CHECK PARENT ID
  // PARENT ID CANNOT BE SET TO SELF + CANNOT MOVE UNDER CHILDREN
  $cannot = $this->getChildren($id);
  $cannot[] = $id;
  if (in_array($parent, $cannot)) {
    $this->error = "Invalid parent ID";
    return false;
  }
 
  // (G2) UPDATE ENTRY
  $this->query(
    "UPDATE `category` SET `category_name`=?, `parent_id`=? WHERE `category_id`=?",
    [$name, $parent, $id]
  );
  return true;
}

The painful part happens when we update and/or change the parent category. Now, consider these:

• We change “computers” into a “main category” – No problem.
• We change “computers” to a child of “cameras” – Does not sound right, but no problem.
• Place “computers” under “desktop” – Both desktop and laptop become “isolated orphans”.

So yes, there must be rules when changing parents:

• The parent_id cannot be “self”.
• The parent_id cannot be moved to a child category within the same branch.
• That is, the parent_id can only be “moved upwards” or “moved sidewards”.

That is exactly what we are doing in (E) and (G1).

 

 

4C) DELETE CATEGORY

// (H) "SAFE DELETE" - CHILDREN WILL REVERT TO PARENT ID 0
function safeDel ($id) {
  // (H1) GET ALL CHILDREN
  $children = $this->getChildren($id);
 
  // (H2) AUTO-COMMIT OFF
  $this->pdo->beginTransaction();
 
  // (H3) REVERT CHILDREN TO PARENT ID 0
  if (count($children) > 0) {
    $in = implode(",", $children);
    $this->query("UPDATE `category` SET `parent_id`=0 WHERE `category_id` IN ($in)");
  }
 
  // (H4) DELETE CATEGORY
  $this->query("DELETE FROM `category` WHERE `category_id`=?", [$id]);
 
  // (H5) COMMIT;
  $this->pdo->commit();
  return true;
}
 
// (I) "CASCADE DELETE" - ALL CHILDREN WILL ALSO BE DELETED
function casDel ($id) {
  // (H1) GET ALL CHILDREN + SET CURRENT ID
  $in = $this->getChildren($id);
  $in[] = $id;
  $in = implode(",", $in);
 
  // (H2) DELETE
  $this->query("DELETE FROM `category` WHERE `category_id` IN ($in)");
  return true;
}

Lastly, dealing with deletion is another pain – What do we do with the child categories? There are 2 possibilities:

• Change the child categories back to “main categories”.
• Cascade delete all child categories.

It’s your project, you decide.

 

 

 

SINGLE PARENT ONLY

As you can see, the parent_id design only allows one parent per category. If you want “multiple parents”, create a category_to_category table with 2 fields – category_id and parent_id. But be warned, it becomes very complex. You will have to step up your “recursive games”, and also make sure that endless loops don’t happen.

 

LINKS & REFERENCES

• Collapsible Tree Menu – Code Boxx
• Dropdown Menu – Code Boxx