Tag: MySQL

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

MySQL Cheatsheet – Useful MySQL Queries

Adding Unlimited Form Fields With JQuery and Saving to a Database

In this article I’ll discuss how to add an unlimited number of additional form elements to a form and then save to a database. The latter part is the key here as a variety of tutorials exist on adding form elements, but I have yet to see anywhere that actually explains how to manipulate these added form fields. For example, how to get values to store them in a MySQL datbase. In the example we’ll have a simple user signup form where the user can add multiple fields to describe their favourite websites.  The basic Form HTML is as follows (nothing amazing, just a simple html form):

<script src="js/jquery.js" type="text/javascript"></script>
<h1>New User Signup</h1>
<form action="index.php" method="post">

  <label for="name">Username:</label>
  <input id="name" name="name" type="text" />
  <label for="name">Password:</label>
  <input id="password" name="password" type="text" />

   <div id="container">
      <a href="#"><span>» Add your favourite links.....</span></a>
   </div>

   <input id="go" class="btn" name="btnSubmit" type="submit" value="Signup" />
</form>

The only part that isn’t standard is highlighted above. This is simply the link users click to add additional form fields on the fly. To make that happen we’ll need some JQuery:

var count = 0;
$(function(){
	$('p#add_field').click(function(){
		count += 1;
		$('#container').append('<strong>Link #' + count + '</strong>'+ '<input id="field_' + count + '" name="fields[]' + '" type="text" />' );
	});
});

Continue reading Adding Unlimited Form Fields With JQuery and Saving to a Database