Magento MySQL Shorts

I use quite a lot of MySQL whilst developing to provide the client stock reports and product reports etc. I thought I’d create a post here to collate them, as they are endlessly useful to me.

I usually enter the query in the Sequel Pro, then use that software to export them as CSV for the client to open as a spreadsheet.

Simple Stock Report

This simple stock report prints out every product, with it’s ID, SKU, Name and Stock Quantity.

The 71  in the WHERE  statement denotes the attribute whereby the name of the product is stored – this may not be the same for all systems. You can find a list of all your attributes and their associated attribute ids by viewing the bs_eav_attribute  table.

All Tier Prices

To get a list of all the tier prices in your site, listing each product ID, SKU, Quantity Required for the Tier Price and the Resultant New Price:


See more Magento Posts

Cron Jobs in Magento Modules

Cron jobs are scheduled tasks that happen at predefined intervals within a server. Usually, these are reserved for mundane, easily automated tasks like clearing log data and reindexing the database. They can be quite easy to set up and get working, but a lot of online guides had me stumped on certain details, so I’ll do my best to explain it fully here.

Setting Up Cron for your Magento Installation

Crontab is a small application on your server that manages cron jobs and assigns resources as they are required. If you’re working from a Virtual Server or a newly provisioned server, then chances are, crontab is installed and working, but not yet set up to work with Magento.

Magento comes with a cron shell file that you’ll need to hook up to crontab.

Begin by accessing your server via ssh/cli. Then run the following line:

This will allow you to modify your crontab configuration. You’ll need to add the following line:

What this does is tell crontab to look at the within your Magento directory at every minute, every hour.

For more information on Cron Formats check out Wikipedia

From this point forward, your Magento installation should be included in your servers crontab configuration, allowing you to make modules that run cron jobs.

A considerable number of core modules utilise cron so it may be a good idea to set this up even if you have no intention of developing cron-enabled modules.

Creating a Cron Job

Cron jobs work with observers in a similar way to events, albeit with a slightly different syntax.

In your module’s config.xml:

You can replace the <cron_expr> value with one that is more relevant to your project. Again, see the Cron Formats on Wikipedia for a reference on the exact syntax here.

Within the <run> tags is a path to your observer and resultant function. When the cron fires, it should run a specific function on an observer. Using the above path, we can assume the following file – Model/Observer.php

From this point, your function should be running at your chosen schedule.

Cron Scheduling with AOE_Scheduler

In the past, I’ve used AOE_Scheduler to manage the scheduling of these cron jobs and allow me more control within the Magento backend. It reports on any cron-based issues and allows you to see the status of jobs in real time.

Cron Scheduling with AOE_Scheduler

Cron Scheduling with AOE_Scheduler

I’d highly recommend use of this module as it seems to just make the whole process of cron management streamlined and simple. Thank Fabrizio Branca for his efforts here.

See more Magento Posts

Observing Order Status Changes in Magento

Magento is such that it allows extraordinary customisation, and its capabilities are only limited by the capabilities of the developer. Often, you may wish to listen for a specific event within the Magento core, and create a custom action upon it. With respect to this, there are a number of ways with which you can listen for an order status change.

  1. Rewrite the Mage_Sales_Model_Order class, adding a custom event dispatch command to the _setState method
  2. Listen to the Order Save event and ascertain whether or not the order status was adjusted

My recommendation would be to listen to the Order Save event. Rewriting classes, especially one at such a crucial part of the core, can often cause incompatibilities with other modules. Event Observers work aside from the usual code processing and as such do not clash.

Creating an Event Observer

Within your module, you can add the following to your config.xml, under the global > events tag:

The sales_order_save_after event is the important marker here. It tells Magento to run the following methods when that event occurs. It has a sister event in sales_order_save_before.

The next point is to declare your module as the recipient of this event, and you can do this by defining a class and a specific method to run. In this case, we are running the orderSaved method on our Model/Observer.php

The Observer Method

Provided the event is declared successfully, in the correct place, then it should fire; however, you need to add the correct method to your Model/Observer.php before it will actually do anything:

Once the orderSaved method is included correctly, it’s a simple matter of changing the status of an order so that you can check the system.log for the word “Test”.

Checking for Order Status Change

We don’t want this process to enact every single time the order is saved, as this will repeat the process each time. We only want our process to work when the status is different/has been changed.

When you receive an event, you can not only check an objects data, but you can also check it’s original data. The difference between the original data and the data is that the original data will be an array of the exact state of the object prior to the save procedure, and the data will contain the new, updated data. This means that potentially, the original data will contain the old order status, and the data will contain the new order status.

Here you can see that we’re grabbing the original data and the new data, and we’re running a comparison of the status key for each to ascertain whether or not the status has indeed changed. The only way the process will continue is if there status is different to how it was.

See more Magento Posts

Magento Breadcrumbs – Links Missing

Magento renders breadcrumbs as part of the Catalog module within the Magento Core. For some reason, this functionality was broken and I set about trying to track down why.

How Breadcrumbs Work

Within the breadcrumbs template file – locate at <em>app/design/frontend/base/default/page/html/breadcrumbs.phtml, you will see that Magento loops through a variable called $crumbs. Here’s  a preview of it’s output:

As you can see, the second item within the breadcrumb doesn’t hold a link; as it is the last in the list, then this is intended functionality. Consider this:

Here we have an additional level to our breadcrumbs, now, for all intents and purposes, category90 should have a link. This is because if you are currently viewing category91 you can use the breadcrumb to navigate to it’s parent; in this case category90. This is something I’ve noticed in Magento 1.8, and I cannot verify whether or not this has changed in the newer 1.9.

Tracking Down the Source of $crumbs

The $crumbs variable is passed to the phtml file in it’s generation, so I needed to track down where this array was generated in order to rectify the process. I ended up doing a find and replace on the Mage > Core > Catalog folder, and I came across the following class: Mage_Catalog_Block_Breadcrumbs

Within this class there was a method called _prepareLayout. This is a method you will find fairly commonplace within Magento’s block classes and essentially, it prepares any variables that are required in the view thats about to be shown. I knew that this was my way in. Around half way down the _prepareLayout method was the following line:

The return from this method call generated something eerily similar to our array from the view file, so it warranted further investigation.

I looked within Mage > Catalog > Helper > Data.php for this method and came across the following, around line 105:

Clearly, this prints out the URL, but only if _isCategoryLink returns a ‘true’. I chose to place some var_dump commands within the _isCategoryLink method, and I found that it was performing exactly as I’d expected, and returning true where it needed to. This suggested that there’s something bad going on with the $categories[$categoryId]-&gt;getUrl() method.

I tried independently echoing the result of $categories[$categoryId]-&gt;getUrl() to see what it outputted, and it indeed printed null where there should have been the link. This is the source of my breadcrumbs issue. A faulty method that doesn’t produce an accurate URL.

Fixing the Breadcrumbs

The correct course of action to repair this issue would be to create a module, and use this module to override the Helper method with repaired code. You can find out how to do overrides in this brilliant tutorial from Inchoo.

I knew of another way of generating a URL for a category, and this was via the getUrlPath() method. The difference between getUrl and getUrlPath is that getUrl will generate a fully formed URL, but getUrlPath will strip only return from the first / onwards. For example:

getUrl() –
getUrlPath() – category

This does have a knock-on effect for categories that are more than one level deep, so this needed some modification to get it to work as expected.

To this end, I replaced the faulty code (above) with the following:

I concatenated each subsequent category to the previous path so that the first level categories appear correctly within the breadcrumbs, but also so do deeper levels.

This solution will only work for those that have conventional URL paths in their breadcrumbs to work with.

See more Magento Posts

Magento Index: There was a problem with reindexing process

“There was a problem with re-indexing process” – a typically ambiguous error message from Magento has plagued one of my biggest projects for some time. The full Magento index process was failing on a specific index and I needed to figure out why.

For my issue, the index that was failing was the Product Attributes Index.

Magento Index Error

I first checked the Magento Exception Log (var/log/exception.log) and I found the below error:

The difficulty with this issue is tracking down which table it’s trying to apply this to. After much investigation, I realised that this number 1144-303-1-4802 was a generated value. 1144 was the product entity_id; 303 was the attribute_id, 1 was the scope, and 4802 was the attribute value. It seemed logical to look for a table where the attribute values are stored and look for a field where 4802 is a duplicate within the values.

The ‘catalog_product_entity_varchar’ was the culprit. It showed the following within its table:

value_id – 81406
entity_type_id – 4
attribute_id – 303
store_id – 0
entity_id – 1144
value – 4799,4802,4802,4804

The entity_id and the attribute_id matched with the error message I was experiencing. With the value, I can see there is a duplicate for the attribute value 4802.

I would assume, by this, that the error message refers to a process that for each row in the catalog_product_entity_varchar table it assembles an indexed ID using the entity_id, the attribute_id and each of the values. Using the above table, I can only assume that the following is going to be assembled at run-time:

  • 1144-303-1-4799
  • 1144-303-1-4802
  • 1144-303-1-4802
  • 1144-303-1-4804

Obviously, if this is added as a primary key to a particular table then it will fail because of the duplicate.

Index Repair Options

From here, I can see two obvious repair options:

  1. Find the process that assembles the IDs and add duplicate checking/ignoring logic, or
  2. Find the process where attributes are saved to the catalog_product_entity_varchar table, then add duplicate checking/ignoring logic.

I opted for option 2, as this is a more sustainable, and much more efficient process. It dynamically overcomes this issue without messing with the core.

Index Solution

I first created a new module. If you’ve no experience doing this, I’d suggest using the Mgt Commerce Kickstarter Module.

Inside this module, I added the following to the config, under the global tag.

This is an event, and events run under certain circumstances. The circumstance we’ve selected is the catalog_product_save_after event which Magento fires automatically as a product is saved in the backend. The method it will run when it fires is fixAttributes which is within the Company_Module_Model_Observer class.

Now over to the actual class and method:

So essentially, what’s happening here is I’ve identified two problem attributes, and stored their IDs within a public array – purely for ease of updating if this problem reoccurs for a new attribute in future.

The product is passed to this event as part of Magento’s own internal functionality. So, for this particular product, I first read the catalog_product_entity_varchar table, passing my problem attribute IDs and my product entity ID. This immediately gives me a condensed list of problem records.

From here, I get all the rows, and run explode to bust that value field (4799,4802,4802,4804) up into an array; then I run array_unique on it which automatically removed any duplicates, and run implode to return it to a string format. All in a single line:

After this, I rewrite the value to the field by appending it to a standard MySQL Update query and right at the end, I run this query.

That’s it. Create an event, then on the event’s process, get all problem fields, fix the problem, re-save it to the database.

Why Did this Happen?

Truth is, I have no idea. The issue seems to be related to attributes that have lots of options, and is available as a multi-select in the product screen. If this is the case, then Magento frequently saves with duplicates. It’s strange because although a Multi-Select box doesn’t allow multiple selections of a single item.

I’m not yet ruling out a store specific issue. I don’t wish to blame Magento itself for everything!

See more Magento Posts

Magento: Mass Assign Product Super Attributes

Magento sometimes has a habit of not working quite the way we’d like, and this was none-so-apparent when messing with our attributes. We had used a service called cart2cart to port everything to Magento from another E-commerce provider; all in all, I’d say that it works fairly well, however, it does seem to mess up the attributes and attribute sets.

Once we’d cleared the majority of our old attributes and reset our attribute sets, the next step was to remove a sticky attribute. We couldn’t remove this originally because Configurable Products depended on it; giving us to a bit of a catch-22. We couldn’t remove this attribute because the Configurable Products depended on it, and we couldn’t disconnect this attribute from the Configurable Product.

We took the radical step to jump into the Magento database and actually just clear the catalog_product_super_attribute table, along with the catalog_product_super_attribute_label and catalog_product_super_attribute_pricing tables. I know… crazy, right. We backed the database up before we did this, naturally.

Now we were in a situation whereby if we visited any Configurable Products in the administrative backend, it would force us to choose a new attribute to assign to the product. The attribute we selected needed to be of a Global scope and be marked as able to be used to create configurable products. We didn’t want to do this for 1000+ products.

Magento Script

This script is designed to take every configurable product and assign a new super attribute to it.

It goes without saying that if your Magento setup is more complex that a simple list of configurable products all connected to the same attribute then this script will need to be adapted in order to work efficiently.

Save this script as super.php and store it in your Magento root. You can then run it by visiting

Keep in mind that this script can take quite a lot of time to run depending on your Magento product setup and the sheer amount of products.

Magento Wipes Tier/Group Pricing Data on Product Save

Sometimes, when Magento runs a product update procedure, particularly from an external script, it doesn’t re-save all the correct product data. This led to a client noticing that the tier pricing for all of their products had been reset. Catastrophic.

At this stage, I can’t stress enough the importance of making backups. In this situation, a backup saved my client’s site

I discovered the reason, and thereby the solutions, via many different StackExchange posts and independent blogs.

Reason for this occurring

I found the below in a quote from Marius on Stack Overflow:

When saving a product the call stack reaches Mage_Eav_Model_Entity_Abstract::_collectSaveData(). In this method you loop through the _origData member of the model and get the attributes that are different and save only those ones.

The _origData member is populated when loading a model by calling setOrigData().
So far so good. The difference is that for the product model the method setOrigData() looks like this (for performance reasons I assume):

This means that if you are not in the admin environment _origData will be null.

In this case _collectSaveData() tries to loop through null, thus the error you are getting.

So, to summarise; if you’re going to be accessing product data from external, or front-end scripts, this issue will always occur. I’m tempted to file a bug report to Magento, as I believe that there shouldn’t be a circumstance where this should return null. To me, it seems like a very strange thought process.

However, there is a solution to this.

Solution to the problem

Magento provides a way to decide upon the environment you would like to run a script within on a script-by-script basis.

To solve this particular issue, you can add the following to the top of your script:

Essentially what this does is tell Magento that this script is to run in an Admin context, and as a result, the code that Marius pointed out above will always set the original data correctly.

See more Magento Posts

Getting over the iOS7 Status Bar

One of the great challenges of the iOS7 upgrade is the introduction of an awkward, translucent status bar.

In reality, the iOS7 status bar looks pretty good. For us developers, it can be an absolute nightmare.

Prior to iOS7, all views sat underneath the status bar, leaving it mono coloured. In iOS7, all views sit behind the status bar.
This is fine, I’m not averse to change in methodology, but production apps are expected to support multiple versions of iOS, not just the latest.
This means I’m forced to ‘straddle the fence’ between change and not change. I this case, the fence is sharp, and it hurts my man-parts.

Status Bar Workaround

I’ve developed a way in which I can manually adjust elements of my view to account for this status bar, through code, without affecting previous versions of iOS.

I call it  deltaY

In my projects, I always use a Utility class, where I can stash methods that can be pulled into any class. The  deltaY  method is the perfect example of a class that I can use multiple times without any instance variables.

Here it is:

There’s two methods, first to identify the leading integer representing the major version number, secondly, to increment any passed values by 20 (the height of a status bar) depending on whether or not the version number is 7 or above.

Simple really.

From here, I can keep my views the same size, so they sit behind the status bar, but I can manually move some elements so that my custom menu buttons aren’t affected by the 20 point increase in screen real estate.

Here’s how it’s used with a normal class:

This will now place the view at 0 for iOS6 and below, and 20 for iOS7.

It’s not just used for the Y value either. I have some custom header views, which were 44 high similar to the UIToolbar or UINavigationBar, and now, I use my deltaY method and it can extend the height of the header so it sits behind the status bar, but also gives enough space for displaying content below it.

The dreaded status bar is no longer an issue.

InlineEditCell, UITableViewCell Subclass

Please Note. This is no longer being maintained, and recent developments have rendered this project broken – if you wish to access the source, please let me know and I’ll provide it.

The InlineEditCell allows you to quickly and easily add a new level of efficiency to your UITableView implementation.

The InlineEditCell  is capable of handling text based input for Standard Text, URLs, Emails and Numbers, along with Select Based input for UIDatePicker and a UIPickerView.

A delegate is also provided with the class, for returning the value of the inputted data.

The InlineEditCell  comes packaged with the below files:

  • CellBackground – for customising the look of the cell
  • SelectedCellBackground – for customising the look of the cell’s selected state
  • DrawingAids – providing some utility methods for the Background classes
  • TableCellTextField – which is the base cell look/workings for subclassing

InlineEditCell Set-Up & Configuration

Step 1: Include in your Project

Add the included files to your Objective-C Project, then include them in your UITableView source like so:

InlineEditCell also comes with a delegate which lets you know when editing has finished. To utilise this, ensure your UITableView class implements the delegate in the header:

Step 2: Configure your UITableView to carry the Cells

Where the normal UITableViewCell is initialised into the table, just swap it for the InlineEditCell class with this simple initialiser:

The first two arguments, Style and ReuseIdentifier are standard practice for a UITableViewCell, but the other two arguments are specific to InlineEditCell – more on this below.

The next thing to do is ensure the delegate is hooked up correctly, and customise the placeholder/default text of the cell:

InlineEditCell Customisation & Options

Type String

An appropriate editing control is assigned to each ‘type’.
See below for the ‘type’ strings compatible with InlineEditCell:

  • text – normal text keyboard
  • url  – url keyboard
  • number  – number keyboard
  • email – email keyboard
  • date  – date picker view
  • selection – selection picker view

Data Dictionary

The data dictionary presents the schema for how you want each cell to work. This is an optional parameter!
The only field type that requires use of the Data Dictionary is the Selection Type.

When using a selection field, the data should be in the below format:

The data dictionary will be expanded upon with each new release.


Example 1: Text Field

Example 2: URL Field

Example 3: Date Field

Example 4: Selection Field


You can download and use the InlineEditCell on the below website:

Inline Editing Cell, UITableViewCell Subclass on CodeCanyon

Including Custom Fonts in iOS

In the past, there was only the facility to include fonts already bundled with your device. Now however, Apple have responded to feedback and given access to developers to add new custom fonts to their application. Custom fonts can obviously provide a much higher level of customisation in your app!

Custom fonts are something that, as a designer, we cherish. Often, we’ll have to dig deeply to find the best font for our designs.

Acquiring the Custom Font

The best format for the custom font is TTF

If you can’t get a TTF variation of your custom font, there’s a number of font converters that’ll do the job for you. I’ve used Free Online Font Converter in the past with some success.

So you need to make sure you’ve added your TTF font file to your project by clicking File >> Add Files…

Custom Fonts in Project Directory

The font added to my project. I’ve created a Fonts group to keep them organised

Adding the Custom Fonts Array to Your PLIST

You need to ‘activate’ the custom fonts within your Info.plist file.

The key you need to add is called ‘Fonts provided by application‘, or ‘UIAppFonts‘ if you’re viewing via Source Code.

Add the full name of the custom fonts file including the extension to this plist.

Adding the Custom Fonts in the Info.plist - PLIST view

Adding the Custom Fonts in the Info.plist – PLIST view


Adding the Custom Fonts in the Info.plist - Source Code view

Adding the Custom Fonts in the Info.plist – Source Code view

Including the Custom Font via UIFont

You’re now able to use the custom font as a UIFont in the same way that you’d use any of the system fonts.

It’s great that Apple are finally allowing developers to add additional customisations to their apps, it’s about time!