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 :)

Multiple Categorisation for SEO

A very common technique in ecommerce, is for products to be assigned a single category – part of the filing cabinet approach to site development. This works for niche ecommerce stores, but not for the majority. For example, a tshirt might belong equally in the following categories:’ red tshirts’, ‘logo tshirts’, ‘mens tshirts’ etc. Additionally there are times when it makes sense to have a multiple categories for a product and can help with conversions(a totally different topic).

The special care of multiple categories and SEO is that category pages contain a huge amount keyword rich anchor text. Yes, the majority of ecommerce software and system will allow filtering of results, but the canonical url tag is often used and results in messy links that are not ignored by search engines.

The major issue here is duplicate content – frowned upon by Google and can cause real issues for your site. Yes, a small numbers of pages is within acceptable limits, but when you have store that has hundreds of products duplicate content really can become an issue.

Using our tshirt example from above, say we place a product called ‘super baggy tshirt’ into the ‘red tshirts’ and ‘logo tshirts’ categories – the following two urls would be produced by our ecommerce software – the below structure is very common):


http://www.shop.com/tshirts/red-tshirts/super-baggy-tshirt/


http://www.shop.com/tshirts/logo-tshirts/super-baggy-tshirt/

At first glance, this all looks well: SEO friendly URLs, well structured, organised and keyword rich. All this correct, apart from the fact that both URLs represent the same product – here is our duplicate content issue. The duplicate content issue will get worse if the product is placed into more categories.  The easiest solution is to rewrite our product URL to something much simpler:


http://www.shop.com/super-baggy-tshirt/

This will allow us to place the product into as many categories as we need without creating any duplicate content at all – there will always be a single version of the product URL.

Create a CSS Hover Effect Image Gallery

After browsing through a a few web portfolios lately I’ve noticed a rather noce efefct – whereby when the user hovers over a thumbnail an image appear – this may be a zoom icon (for images) or a play button (for videos). In thus article I’ll quickly run through the simple steps on who we create a a funky css hover effect image gallery. This is a very useful technique for any sort of site that display thumbnails links.

The HTML

<div class="thumb_wrap">
<a href="#link" class="thumb_link">
<span><img src="play.png" alt="play" class="play_video" /></span>
<img src="thumb.jpg" alt="thumbnail" />
</a>
</div>

I’ve simply nested a span tag containing the hidden play button. Additionally, to keep everything XHTML valid, the hyperlink doesn;t contain block level elemnts.

The CSS

a img {
border:none;
}

.thumb_wrap {
width:194px;
height:110px;
margin:0 25px 0 0;
float:left;
}

img.play_video {
position: absolute; 
margin:40px 0 0 80px;
display: none;}

The CSS is simply sets the image’s position absolutely (to ensure nothing gets pushed out of line).

The JQuery

The give the show effect a nice fade in style and in order the show our hidden image I’ll add a small piece of JQuery that finds the image within our span tag and fades it in. The latter is done when the user hovers over the image, when the mouse leaves, the image is hidden again. The folllwing would go within the head tag (I’ve also let Google CDN host my JQuery file):

<script type="text/javascript">
$(document).ready(function() {

$(".thumb_link").mouseover(function(){
 $(this).find('img.play_video').fadeIn('slow');
});

$(".thumb_link").mouseout(function(){
 $(this).find('img.play_video').hide();
});

});
</script>

…..and that’s it. You now have a cross browser compatible (IE 6 plus) image gallery effect, that is very easy to implement. See the image gallery in action.

PHP Caching with PEAR Cache Lite

Last week I was given the task my a client to speed up their website – they had paid for literally a single hours work, so not much time at all. The site in question was a company website that had various dyamic boxes of content – namely an area for latest events, latest news and latest clients – aswell as a custom content management solution running for the main page content. Additionally, the database is hosted by (for me), argueably the worse and slowest webhost around – streamline.net – so database lookups are slower than usual to begin with.

Each time a page was served, many unecessary requests were being made to the MySQL database. The site gets quite a lot of passing traffic from search engines, with all the content be created byn the site owner. As a result, databse lookups for the same content each time as not needed and some simple caching was to be used – nameley, PEAR Cache_Lite.

PEAR Cache_Lite is a small yet powerful PHP caching system that will cache (or save on disk) our dynamic PHP pages. Subsequent requests for complex and database intensive pages are not required due to our cache. This will speed up the site no end.

Firstly, you’ll need to download the latest Cache_Lite package and include the file Lite.php on your page. Also, create a folder called ‘cache’ within your site structure. The general idea of Cache_Lite is very simple, giving each page a unique ID – there is no set rule for this and the creation of this ID is left to the developer.

The actual code to cache a complex page is very simple:

Continue reading

W3C Validation and SEO Benefits – My Opinion

The link between full W3C Validation and it’s important upon SEO is commonly discussed topic and a huge taboo. This is the notion that  having a valid site according to the W3C Standards is either critical (or not) to your website’s SEO.The first thing to note that a site passing W3C Validation will have met the following criteria: will not use depreciated tags and will not have syntax errors – essentially a syntax check.

I physically cringe when I hear quotes such as ‘valid xhtml will help your users’. Valid xhtml will not help your users, to help your users a site needs to adhere to web coding standards – this is an entirely different beast. The main difference here is the practice of seperating content from presentation, thus giving the content increased meaning. For example, a page using tables to layout the whole web page would not adhere to web coding standards because using tables for layout is semantically incorrect and requires a lot more code. Tables should be used for tabular data, simple. Another example is the use of paragraph and header tags. Visually they are very similar but have a very very different meaning sementically. However, yet again, semantically incorrect pages will pass validation. The main Google webpage doesn’t even validate (interestingly, Google does’t even quote html attributes in order to save on page size). In my opinion, as long this is the case W3C validation will be a none issue, SEO wise.

Understanding which semantic elements add value to the document will affect the onsite of a website and is an SEO ranking factor.I have read several artuicles that describe W3C validation and SEO as a match made in heaven, this simply isn’t the case, although web semantics and SEO are.

There are many websites (40% is a figure thrown around a lot) that do not validate, yet perform quite well in search engines as they have a range of high quality content. Take a quick example. I searched for a very competitive term “houses”. The number one result was rightmove.co.uk. Rightmove even has an authorative listing for that term too – SEO wise there can’t be too many issues here. Running that site through the validator throws up 33 errors and 22 warnings. – see the result. These are mainly smaller syntax errors that quite rightly, the developers of that site have ignored. There are endless examples where sites a lot worse appear at the top of the SERPs, even though they fail to validate and sometimes, don’t follow web standards at all.

Continue reading

PHP Paging with Caching

The majority of PHP paging script with contain a request to the database for a total row count. This is perfectly acceptable if you script needs an exact and accurate results count. However, most sites don’t need a dynamic count and the total results count represents an uneeded database request. On busier sites this effect can become quite limiting and will slow down you scripts.

As an example take a website that gets say 10 hit per second, withouit caching this represents 20 (10 hits on your database and 10 hits on your database to fecth the result set) hits per second to your database – on larger sites and larger result sets this effect is amplified. However, by simply caching the total number of rows returned you can cut the requests to your database in half.

Google uses a caching system on their results (albeit more more complicated) and they never retuirn the exact amount of results (the perforamnce hit on a site as big and busy as Google would be absolutely huge!). Instead they present results as: “results 20-40 of about 248,000″ – with figure of 248,000 coming from an estimated cache value.

To cache the number of results the script would simply store a count of the total number of records in a SESSION variable (assume the cached session value is called “totalResults”. You could also store the result as a COOKIE that expires in the future, to force an update the cached number of rows. Using sessions, the code is very simply:

if( !isset($_SESSION['totalResults']) ) {
  //Code to query your database for a row count
} else {
  //use the total record count directly from the session variable
  $myPagerObj->pageSomeData($_SESSION['total_records'], $perPage, $curPage);
}

It would also be wirth doing some validation on the cached page value, for security. However, the latter would usually be done directly in you’re paging class.

JQuery Hover Effect Image Gallery For eCommerce

web design talk jquery galleryAfter searching  for a simple jquery image gallery for an online store I decided to make my own. There were several solutions that were close to what I wanted, but quite there. The project required an simple large vierw of an image with multiple thumbnails below, when hovered upon switched the large image. The requirements were as follows: cross browser compatible (IE6 plus, Chrome and Firefox – all the major browsers), have a nice fading effect between image changes and be totally degradable if JavaScript was turned off.

First of all, take a peek and the finished JQuery image gallery – I’ve put the gallery in context on a product detail page for an ecommerce store.

JavaScript and JQuery to the Rescue!

JQuery was the JavaScript framework of choice and made it easy to get things up and running. Techniqually the same effect can be achieved without JQuery, but for simplicity I’m using JQuery. The code is quite easy to follow and I’ll just pickup  up on the main parts.

Firstly we’ll start off with the HTML – simply two dividers, with an unordered lists for the thumbnail images:

<div id="bigpic" class="b"><img src="images/big/iphone-3-big.jpg" alt="iPod Shuffle 16GB Zoom View" />
<p id="desc">iPod Shuffle 16GB Zoom View</p>

</div>

<div id="thumbs">
<ul>
	<li><a rel="images/small/iphone-1-small.jpg" href="images/big/iphone-1-big.jpg">
<img src="images/small/iphone-1-small.jpg" alt="iPod Shuffle Front View In Blue!" />
</a></li>
	<li> <a rel="images/small/iphone-2-small.jpg" href="images/big/iphone-2-big.jpg">
<img src="images/small/iphone-2-small.jpg" alt="iPod Shuffle Dual View Grey!" />
</a></li>
	<li> <a rel="images/small/iphone-3-small.jpg" href="images/big/iphone-3-big.jpg">
<img src="images/small/iphone-3-small.jpg" alt="iPod Shuffle 16GB Zoom View" />
</a></li>
</ul>
</div>

The main image is housed in the divider with the id of ‘bigpic’. I’ve also added a description below the big image – this will change when the image is hovered over to the thumbnail alternate text. Each thumbnail link goes directly to the enlarged version of the image – this way, the gallery will still work when JavaScript is turned off.

The JQuery Magic ….

Right after including the JQuery library (I recommend you make use of Google CDN for this – there are many reasons to let Google host your JQuery files) I have included an int,js file – this contains all the gallery JavaScript. The int file is a small file that catches a hover on each thumbnail and switches the main gallery image (Our ‘bigpic’ divider). All the code is contained within the mahic document.ready listener. The first half of the file catches the hover on a thumbnail:

$('#thumbs ul li a').hover(
		function() {
			var currentBigImage = $('#bigpic img').attr('src');
			var newBigImage = $(this).attr('href');
			var currentThumbSrc = $(this).attr('rel');
			switchImage(newBigImage, currentBigImage, currentThumbSrc);
		},
		function() {}
	);

Here we are getting the currentBig image href, the new big image (from our thumbs href) and current thumbnail src. The second empty fucntion is included to say ‘don’t do anything when hovering away’. Exclude this empty function and the hover event will continue to fire when you leave the image. Now we have these three variables we pass them to our switchImage function, code below:

function switchImage(imageHref, currentBigImage, currentThumbSrc) {

		var theBigImage = $('#bigpic img');

		if (imageHref != currentBigImage) {

			theBigImage.fadeOut(250, function(){
				theBigImage.attr('src', imageHref).fadeIn(250);

				var newImageDesc = $("#thumbs ul li a img[src='"+currentThumbSrc+"']").attr('alt');
				$('p#desc').empty().html(newImageDesc);
			});

		}

	}

This is quite self explanatory. The first check made is that the current big image is not the same as the target big image – if this was true we would fade in and out the same image, which leads to a rather weird looking effect. If both paths match, the user has hovered over the thumbnail of the current big image (in this case nothing would happen). Hovering over any other image will result in the current big image fading out, setting the big image src to the src from the thumbnail (the new image) and populating our description paragraph.

Using the Gallery in a Production Website

In the example 3 static large and small images have been manually added for simplicity. In a real website, maybe an online store, you would retrieve this information from a database. Another thing to note is image sizes. I always find it useful to let users upload whatever file resolution they want, giving them the exact pixels as a recomendation. To ensure that a particularly large file wouldn’t break the layout I tend to sue image resizing scripts, such as the excellent smart image resizer from shifting pixel – uploaded images can be automatically scaled to dimensions of your choosing.

Also, at some point I’ll learn about making your owns plugins for JQuery and turn this into a self contained plugin :)

There’s not a lot more to this to be honest. tested in IE6, IE7, IE8, Chrome and Firefox – all working nicely.

Take a look at the final result or download the source files. Enjoy!

Detect AJAX Requests using the x-requested-with header and xmlhttprequest

This is a small snippet of code I came across today, it allows a script to display different content based on how it was requested. This method allows your scripts to remain in a single file, handing both AJAX and normal requests – it avoids ending up with lots of small PHP files in your AJAX folder, that deals with ajax requests.Another use would be a page that has 2 web forms, one AJAX and one normal. You could keep the code for this page in a single file. This method is also useful for security purposes, as it would ensure that requests to your AJAX scripts are via AJAX only. It also has uses for writing unobtrusive JavaScript – maybe ensuring that an AJAX enabled web form would work when javascript is disbaled.

For example, the below code would display different code depending on if the request for the page was made via AJAX or directly via a browser.

if(isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest') {
    //This is an AJAX request, do AJAX specific stuff
}
else {
    //This is not an AJAX request E.g. display normal page content.
}

In some code I was working on today, I saw a neater way of achieving the above, this would be included in your common config file:

define('IS_AJAX_REQUEST', isset($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest');

if(IS_AJAX_REQUEST) {
    //This is an AJAX request, do AJAX specific stuff
}
else {
    //This is not an AJAX request E.g. display normal page content.
}

There’s an HTTP variable set called HTTP_X_REQUESTED_WITH, which will is set to ‘XMLHttpRequest‘ if the request method is via AJAX. This is method is untested with JavaScript frameworks other than JQuery, so may not work (but I don’t see any reason at all why it wouldn’t!).

It’s also worth noting that not all web servers include this variable and sometimes omit this specific $_SERVER paramter. Use vardump($_SERVER) to check that the HTTP_X_REQUESTED_WITH is present.

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:

php multiple=

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

Enhanced Visitor Event Tracking With Google Analytics and JQuery

Google Analytics has fast become the industry standard to track a plethora of web based information about your website. Whilst being totally free and easy to setup, you are limited to tracking elements that physically render in the browser – so items such as PDF, ZIP and RSS feeds links are not tracked, this because Google Analytics has a great reliance upon JavaScript. However, tracking such links can be achieved with a small amount of extra work.

Personally, I wasn’t aware you could track specific links with Analytics and only ever considored this when a client asked ‘why doesn’t Google show me the numbers of times my marketing report (read: a PDF file) has been clicked?’ – a totally valid request that I wanted to investigate.

Use JQuery to improve Google Analytics and track downloads, RSS, Email & external links

First things first, make sure you have a google Analytics account, the latest version of JQuery and the latest version of the analytics code running on your website :)

As with the majority of the JQuery magic, everything happens within the doc ready event listener – this will used to capture various clicks to select elements.

Tracking Download Link Clicks (PDF, ZIPs etc.)

$(document).ready(function() {
	
	$("a[rel=download]").click( function() {
		var fileName = $(this).attr("href");
		pageTracker._trackPageview(fileName);
		return true;
	});

});

Then on every link you wish to track, simply add the rel attribute to your non HTML files as follows:

<a href="myData.zip" rel="download">Download My ZIP Data File</a>

Tracking Downloads of Specifc File Types (E.g. PDF files)

Using the dollar sign to match against links that end in .pdf (or any extension you wish to track).

$(document).ready(function() {

	$("a[href$=pdf]").click( function() {
		var myPDF = "/pdfDownloads/" . $(this).attr("href");
		pageTracker._trackPageview(myPDF);
		return true;
	});

});

The /pdfDownloads/ is used to identify and seperate report data within Google Analytics.

Tracking the click of a specific link such as an RSS feed

Simply add an identifier to your RSS feed link (in this example the link was given an id of ‘rssFeed’):

$(document).ready(function() {
	
	$("a#rssFeed").click( function() {
		pageTracker._trackEvent("RSS", "RSS Subscriber Link Clicked");
		return true;
	});

});

Tracking mailto: Link Clicks

$(document).ready(function() {
	
	$("a[href^=mailto:]").click( function() {
		pageTracker._trackEvent("Mail", "User clicked on mailto link");
		return true;
	});

});

Tidying up….

You should also disable the clicked element to prevent multiple event recording and provide feedback. To do this, simple add the following at the start of each piece of code – disbabling the element and changing the cursor to an egg timer (although you could display a small graphic to make things look prettier):

$(this).css("cursor", "wait");
$(this).attr("disabled", true);