Category: PHPMyAdmin

Associate Products with Multiple Categories Using MySQL

Having a database structure that allows a product to be associated with more than one category is a very common scenario in any eCommerce website. However, after working on a couple of truely awful bespoke solutions from other developers recently, whose methods to store and retreive such data were so convoluted, have inspired me to write this article.

Story such data need to not be overly complicated. The following, simple, table structure is required (in a real ecommerce system you would definately have additional fields – these have been omitted here for thae sake of the example):

Product
product_id (PK)
name

Category
category_id (PK)
name

Products_Category
product_id (PK)
category_id (PK)

The products_category table is a simple linking table that allows a many-to-many relationship between the product and category table. It contains to two primary keys to ensure every combination of product and category is unique. for example, this table will contain many unique number pairs and a row may be 1,4 or product_id 1 and category_id 4. The files to create and populate this table structure with sample data can be found here.

Now it is simplay a case of running a series of MySQL statements (I’d advise converting them to stored procedures for more security and better application seperation) to retreieve the appropriate data. For example:

Products Within a Certain Category (E.g. category_id 1):

SELECT p.product_id, p.name FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
WHERE pc.category_id = '1';

Count Products Within a Certain Category (E.g. category_id 1):

SELECT COUNT(p.product_id) As myCount FROM product p
INNER JOIN product_category pc
ON p.product_id = pc.product_id
WHERE pc.category_id = '1';

…and that’s it. Extremely simple, can be expanded to any eCommerce system and not convoluted at all 🙂

Displaying a Breadcrumb Navigation for Multiple Sub Categories via PHP

While making an ecommerce store I ran into the issue of displaying a category breadcrumb. Usually this is easy as I always recommend keeping the numbers of categories and sub categories to a maximum of 1 level deep. E.g. Tshirts > Red tshirts. For this store, due to the sheer number of products the owner wanted to add up to seven category levels. While there are many tutorials on this floating about, they all seem tocus on displaying the whole tree – very useful for a sitemap page, but not a breadcrumb navigation. While it would have been possible to hard code several if statements into the category listing page, this seemed a bit messey and would cause problems if an eighth level was added.

For the breadcrumb naviagtion I needed a function to displaying the path to a given category ID, sometimes refered to as a single branch or node. I was using a simple parent child database table structure:

While the latter may seem fairly trival I really couldn’t get my head around the problem. After a bit of Googling, I found a function that was a good starting point so adapted it to fit my problem (the function is part of a categoru class):

function getCategoryTreeIDs($catID) {
		$row = DB::getInstance()->query("SELECT parent FROM categories WHERE ID = '$catID'")->fetch();
		$path = array();
		if (!$row['parent'] == '') {
			$path[] = $row['parent'];
			$path = array_merge($this->getCategoryTreeIDs($row['parent']), $path);
		}
		return $path;
	}

The function simply returns an array of category IDs. E.g 20, 28. So from the array I’d know that the tree would go as Home > Cat ID 20 > Cat ID 28.

Displaying the Breadcrumb Navigation

To display the actual breadcrumb I simply added the following method, that loops through the array of ID we just generated. The getNameLink method simply generates an SEO feindly website URL for the category, inside the <a> tag.

function showCatBreadCrumb($catID) {

		$array = $this->getCategoryTreeIDs($catID);

		$numItems = count($array);
		for ($i = 0; $i<=$numItems-1; $i++) {
			echo $this->getNameLink($array[$i]) . ' &raquo; ';
		}
	}

The result is a nicely formatted breadcrumb (to use our tshiorts example again):

Home &rquao; Clothes &rquao; tshirts &rquao; Mens &rquao; Red tshirts &rquao; Offensive &rquao;

A recursive function inside a loop, are you insane?

Some of you may have noticed that the function used to generate the category IDs is called recursively. This generally considered bad practice, for large data sets due to performance issues. However, for the current use this isn’t an issue. I know for a fact that client won’t be adding categories more than several levels deep, so performance really isn’t an issue in my eyes here. Maybe if we had hundreds of categories, but for several it’s really a non issue in my opinion.

getCategoryTreeIDs