Web Design Talk

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

Page rank	5

Page rank	5

Page rank	5

Page rank	5

What follows is a list of some very useful MySQL queries for use in projects, enjoy. I won’t go into detail how and why they work, I’ll leave that up to you

/*** select records from the previous day ***/
SELECT * FROM users WHERE TO_DAYS(last_login) = ( TO_DAYS(NOW()) - 1 )

/*** select records from last 90 minutes ***/
SELECT* FROM users DATE_SUB(NOW(),INTERVAL 90 MINUTE);

/*** select records from last 1hr 5 mins ***/
SELECT DATE_ADD('1970-01-01 12:00:00', INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);

/*** select records from last hour ***/
select DATE_SUB(NOW(), INTERVAL 1 HOUR);

/*** using the SIGN function to mark a number as positive, negative or null ***/
SELECT backlist, SIGN(backlist) AS user_to_backlist
FROM users
WHERE user_banned IS NOT NULL;

/*** select records from last week ***/
select DATE_SUB(NOW(), INTERVAL 1 WEEK);

/*** get the last day of next month ***/
SELECT LAST_DAY('2006-03-06' + INTERVAL 1 MONTH) AS last_day;

/*** select unique records only ***/
SELECT user_name FROM users GROUP BY users HAVING ( COUNT(user_name) = 1 );

/*** select records from one table that are in another table i.e. all the customers that have placed an order ***/
SELECT DISTINCT cust.customer_id, cust.customer_name
FROM cust INNER JOIN orders ON cust.customer_id = orders.customer_id;

/*** insert data from one table into another ***/
INSERT INTO customers(customer_id, customer_name)
SELECT cus_key, cus_name
FROM customers_2 WHERE customer_name LIKE 'W%';

/*** update information based upon a seperate table ***/
UPDATE cust SET status = '1'
FROM orders WHERE orderdate > '2009-01-01' and orders.customer_id = cus.customer_id;

/*** classic self join example - who is an emoployees manager ***/
SELECT emp.empID, emp.Name, emp.Salary, managers.Name AS manager_name
FROM emp
LEFT JOIN emp AS manager_name
ON emp.ManagerID = Manager.EmployeeID
WHERE (emp.empID= '123456');

/*** using UNION to combine results from multiple queries into a single table ***/
SELECT users.name
FROM users WHERE (users.name BETWEEN 'A%' AND 'M%')
UNION
SELECT banned_users.name FROM banned_users
WHERE (banned_users.name BETWEEN 'A%' AND 'M%');

/*** concatenate column data into a single column ***/
SELECT CONCAT(emp.firstname, '-', emp.lastname) AS emp_full_name FROM emp;

/*** select count of records for each hour ***/
SELECT HOUR(last_login) AS last_login_hour, COUNT(*) AS the_count FROM users GROUP BY HOUR(last_login);

/*** import a csv file ***/
LOAD DATA INFILE '/path/xxx.csv' INTO users_table csv_test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY "\n" (user_name, access_level , user_email);

/*** display current mysql user ***/
SELECT USER();

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" />' );
	});
});

» Read more…