Opencart 1.5.x Multistore – Associating Existing Data

The Opencart Multistore feature is a great addition for retailers requiring multiple stores, managed via a single administration area. Setting up multistore in Opencart is quite easy and can be accomplished in a few minutes.

However, after visiting the new store you’ll immediately see that existing products, categories, customers, page layouts etc. have not transferred over. Ouch! The new store is completely empty.
Continue reading Opencart 1.5.x Multistore – Associating Existing Data

Laravel 4 Database Seeding with Faker

Database seeding can be a pain to perform and end up very clumsy. Seeding is a process required in the majority of web applications – either for stress testing or just to generate a reasonable sample of test data during testing. Laravel 4 already has database seeding and migrations built in, which of course is great. However, the functionality to generate the actual sample data is lacking. Enter Faker – a package, available via composer. The author describes this better than I can:

Faker is a PHP library that generates fake data for you. Whether you need to bootstrap your database, create good-looking XML documents, fill-in your persistence to stress test it, or anonymize data taken from a production service, Faker is for you.

Continue reading Laravel 4 Database Seeding with Faker

Counter Cache

Counter Cache (or Counter Cache Columns) is the term coined when adding a column to data tables in order to keeps track of aggregate data in an application. The term “cache” is used because the application has no need to perform costly count queries. It can simply read the value of the “count column”. This practice is less costly when compared to performing separate database queries. The worst case scenario, where the application has database queries within a loop is avoided too.

Continue reading Counter Cache

Why Codeigniter Base Models Rock

codeigniter base crud modelsThere are a plethora of reasons to use a good base model (also called CRUD Model, MY-Model) for all your CRUD operations in a CodeIgniter (or any) application. Amongst many, a base model will boostrap all models it extends, keep your application as “DRY” as possible and speed up general development. Codeigniter has a pretty neat active record implementation, but you do tend to repeat a lot of the boring database stuff when writing models. In my opinion, you’d be insane not to use a good base model. There are many out there, but I use the amazing base model from Jamie Rumblelow (it deserves and SEO Link before you ask!).

Continue reading Why Codeigniter Base Models Rock

Enchaning an Invoicing System for Reporting

At the start of the year I wrote a custom invoicing system for a client, using PHP and Smarty (hope to convert to use Twig at some point). The client has since come back to me to built in extra functionality – namely a reporting section. I thought I’d share my solution to the issue I was faced with.

The client required some fairly involved reporting facilities, here is a sample of some of the requirements from the client:

  • Aggregated report, split between two dates of our choosing, with totals split by either month, day or week (we want to choose)
  • A list of invoices for a day of our choosing with aggregated totals of for the day selected
  • Annual invoicing report – we want to show a summary of the total for the items sold, the total vat charged and summaries for average invoice values
  • Invoices totals grouped by customer

There were several more, but they were all similar to the above.

Firstly, I’ll explain the database setup, for the invoicing, of the current system I built. It was basically two tables – one for the invoivce header and one table for the invoices items – nothing un typical there at all.

Continue reading Enchaning an Invoicing System for Reporting

Storing Ecommerce Order Information Correctly

Yesterday I was doing a tiny bit of web realted freelancing for a friend – simply involving doing a bulk, conditional update of his entire stock for his ecommerce website. The site itself was simple in design and used PayPal with a sprinkle of IPN. So, after a bit of MySQL magic, I updated all of his prices, for all ~700 products in one fell swoop – all good so far, easy money.

However, after an hour or so of emailing the store owner I got a very worried phone call. In short, the bulk price update to had caused all archived order invoices to become incorrect. Why – due to bad database design by the original developer, let me explain.

Continue reading Storing Ecommerce Order Information Correctly

Best Practices With PEAR Cache Lite

Caching a website using PEAR Cache Lite has many benefits – it will speed up your site and is very simple to implement. However, there are several common pitfalls they could cause potential issues on a website. Basically, you can’t approach caching from a single angle, as the type of caching used is always project/problem specific.

Full File Level Caching

Full file caching is the simplist method and essentially takes an image of the page at a particular time. This method is very fast to implement but does have it’s downsides. As you are storing a static file of the page you’ll instantly notice the following issues (I’ve included some typical examples you’ll run into):

  • Any elements of your page that require session data will fail to work (you’ve essentially cached the session id too!)
  • If the page needs to be served using a particular header you’ll be out of luck, as a statiuc file will always be served using a plain text header (E.g. a cached rss feed won’t validate as the header has changed)
  • Clearing the cache becomes very clumsey as the whole cache requires cleaning when even a small change is made (E.g. say a new article is added by the admin)
  • User logins that make use of session data
  • E-Commerce sites and a shopping basket summary
  • Search results pages

With full file caching dynamic content will always be a issue. However, full file caching CAN help high traffic sites, even when a short cache lifetime is set – use with caution!
Continue reading Best Practices With PEAR Cache Lite

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

MySQL Cheatsheet – Useful MySQL Queries