PHP (21)

Merchandise magento category by product age / availability

Just a quick snippet of code today, it grabs all the products in a certain category (given in category_id), sorts them by age and stock availability (you can adjust the relative weighting of these using $stock_weight) and then updates the category. The end result is a nice mix of newer products, and products with loads of stock towards the top of the category, older / lower stocked products further down.

This uses the external database script access script from: here

I should probably bundle this up into an extension, with a cron job and so on but…. I’ll leave that as an exercise for the reader!

$category_id = 8;
$stock_weight = 10;

$sql = "select ccp.*, DATEDIFF(Now(),cpe.created_at) as age,
(select sum(qty) from cataloginventory_stock_item as csi join catalog_product_super_link as cpsl on csi.product_id = cpsl.product_id where cpsl.parent_id = cpe.entity_id) as stock_qty,
((select DATEDIFF(Now(),MIN(created_at)) from catalog_product_entity) - (select age)) as age_factor,
(select stock_qty) * $stock_weight as stock_factor,
(select age_factor) + (select stock_factor) as sortby
from catalog_category_product as ccp
	join catalog_product_entity as cpe
		on cpe.entity_id = ccp.product_id
where ccp.category_id = $category_id
and cpe.type_id = 'configurable'
ORDER BY sortby DESC";

function build_query($results){
  $update_sql = "Insert into catalog_category_product (`category_id`,`product_id`,`position`) VALUES ";
  $pos = 0;
  foreach($results as $result){
    $update_sql .= "('".$result['category_id']."','".$result['product_id']."','".$pos."'),";
  $update_sql = rtrim($update_sql,',');
  return $update_sql;

require_once "include/db.php";

$db = new db();

$results = $db->query($sql);
$db->query("delete from catalog_category_product where category_id = $category_id");

Access magento database outside magento

Just a quick note, sometimes you need to access the magento database from a script, but don’t want the overhead of loading the whole magento framework….. Here’s a little snippet of code that  grabs the database details from the magento xml config, so you don’t have to store them in your script:

 class db {

 private $data = null;

 public function __construct(){

     $host = (string) $xml->global->resources->default_setup->connection->host;
     $db = (string) $xml->global->resources->default_setup->connection->dbname;
     $user =(string) $xml->global->resources->default_setup->connection->username;
     $pass = (string) $xml->global->resources->default_setup->connection->password;
     $charset = 'utf8mb4';

     $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
     $opt = [
         PDO::ATTR_EMULATE_PREPARES => false,
     $this->data = new PDO($dsn, $user, $pass, $opt);

 public function query($sql){
    return $this->data->query($sql)->fetchAll(PDO::FETCH_ASSOC);



Save it somewhere like /magento_root/scripts/include/db.php and use it like this:


require "include/db.php"
$db = new db();

$results = $db->query('select * from cataloginventory_stock_item');





Mage Tools

Quick plug for some magento scripts I wrote for development / deployments of Magento 1.x

There’s some info in the readme, but I might write a couple of quick tutorials if anyone’s interested:

For the last couple of months I’ve been working on a platform to solve one of the problems I’ve seen repeated across numerous businesses in the ‘fast fashion’ ecommerce space.  The problem is how to update your homepage and site with the numerous promotions you’re running in a timely fashion without impacting site performance.  For the majority of businesses in this space, Magento is the platform of choice for the web facing side of the business.  Unfortunately Magento’s tools for this particular problem are lacking.  Smaller businesses tend to use CMS blocks with javascript timers to make sure their promotions show at the right time.  Moving up the scale, we have plugins for magento that allow cms blocks to be shown on a schedule.  And at the top end of the cost spectrum we have ‘personalization’ platforms that, as well as offering variant testing and personalization also offer the ability to schedule blocks of content.

Create products in magento from excel file

As per this thread on reddit, here’s my script for importing products into magento via an excel file. Some of it, specifically the attributes and attribute sets are hardcoded in places, but other than that it should be generally applicable to most stores. It makes use of the PHPExcel library for loading the data from an xls or xlsx file.


Magento: Image upload script

Hey all, been a long time since I posted anything since my job is keeping me pretty busy…. here’s something I implemented for that might come in useful for you.

It’s a little script that basically scans a directory for images and, if it finds any, adds them to the relevant products.

Images have to be named as follows:




Category based shipping in magento

One of the things that’s always annoyed me about Magento is the dearth of decent, free shipping modules available for the system. Out of the box you can do a reasonable amount with regards shipping, setting a flat rate or using one of the built in shipping methods…but if you want to do anything fancy (and in my experience, people always do) then you need to pay out for one of the many customisable shipping modules available.

It’s been nearly a year since I last looked at Magento, but a recent freelance project has meant me getting back into it and as the client has some very specific shipping requirements, I thought I’d write my own shipping module. I spent a few hours fiddling with config files, before rememebering why Magento always annoyed me so much (hint, it’s the config files) and used the rather brilliant magento module generator to get me started.

Here’s what I wanted from a shipping module:

  • Different shipping rates for individual products
  • Different shipping rates to different countries
  • Easy assigning of shipping rates to products

So, to tackle these problems I came up with a module that uses Shipping rules defined through categories. This means you can setup your rules, and then use the standard product / category concept in Magento to assign your shipping rates.

Once you install the module (see below for a download) each category gets a new attribute under its ‘general’ tab, named ‘Shipping’. That’s where you enter your rules. Rules are extremely simple, and consist of a series of lines, each of which is made up of ‘country code’ = ‘price’

here’s an example of one ruleset:


This translates to ‘If the delivery address is in the UK, free shipping. If it’s in the European union, shipping is 3.00, for the rest of the world it’s 5.00’.

You can have as many lines as you want (they’re processed top to bottom) and if you have multiple items in your basket that produce different shipping rates, the most expensive rate is picked (there’s no option to sum the rates together yet, I might add that in at some point).

You can add these rules to your actual, published product categories or do what I’ve done for this client and set up a seperate tree of ‘Shipping categories’ like so:


So there you go. Bit basic, but does what I needed it to do.

Here’s the download link (upload the ‘app’ folder from the archive into the root of your magento installation):

jNag server rewrite underway

Just a quick not to mention that I’ve restarted work on jNag.  First order of business is to rewrite the server side code which, to be frank, is a quick and dirty job I hacked together just to get soemthing working at the time.  The rewrite will incorporate everything I’ve learned about development in the last couple of years and should be much easier to maintain and offer something that’s caused a few people grief with jNag in the past: version support.  On a basic level this means that jNag will be able to support all the different flavours of nagios that are out there by changing a configuration variable (there’s some detail about how this is done below, in case anyone’s interested)

This new version of the server will be backwards compatible with the current versions of the app, but once I’ve rewritten the server I’ll be moving onto the app.  First I’ll be updating the libraries that jNag uses (especially jQuery mobile, which has had a full production release and a point release since the version that shipped with current versions of jNag) and then hopefully adding features.

As a special bonus, here’s some bits of code that deal with loading in different vetrsions of Nagios.

First up, in the core framework class I have this bit of code:

$this->main = new main();

This ‘includes’ a file based on what’s set in the ‘server_type’ config key, and then instantiates the ‘main’ class from that file.

Our main class looks like this:


 * main class for the 'classic' server type

class main extends main_base{
    function __construct(){
    public function status(){
        $hosts = json_decode($this->run_query("GET columns\nOutputFormat: json\n\n"));
        echo "<table>";
        $last_table = "table";
        foreach($hosts as $host){
            if ($host[2] != $last_table)
            echo "<tr><td COLSPAN='4' ALIGN='middle' style='background-color:red;'>".$host[2]."</td></tr>";
            $last_table = $host[2];
            echo "<tr><td>".$host[0]."</td><td>".$host[1]."</td><td>".$host[2]."</td><td>".$host[3]."</td></tr>";
        echo "</table>";        

Obviously that’s just the first bit of it, the full class is a bit bigger… buut you can see how it implements the ‘Status’ method.  The full class implements all the methods that our old ‘returndata.php’ file in previous versions of jNag provided and, because the framework can be accessed from any index file we can access it through ‘returndata’ and maintain compatibility.

You’ll notice our ‘main’ class extends a ‘main_base’ class.  That looks something like this:

abstract class main_base{
    //show current system status
    abstract protected function status();
    //get items that are 'pinned' to the homescreen
    abstract protected function get_pinned();
    private function format_time($timestamp){
        if ($timestamp > 0){
            return date("d/m/Y-H:i",$timestamp);
        } else {
            return "Never";

And contains abstract functions for all the methods that our real main class has to implement.  So, once all this is in place all you have to do is create a ‘main’ class for your specific flavour of nagios, set the server_type correctly and jNag will work.

Refactoring this code has really shown how much I’ve learned since I wrote the original.  I’m particularly pleased with how the config class turned out.  It looks like this:

class config {
     * load all config information from $filename.
     * for each key = value in the file you end up with config->key = value;
    public function __construct($filename){        
        $settings = file($filename,FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
        foreach($settings as $setting){
            $var_array = split("=",$setting);
            if (substr($var_array[0],0,2) != "//"){
                $var_array[0] = trim($var_array[0]);
                $var_array[1] = trim($var_array[1]);
                if (strtolower($var_array[1]) == "true")
                $var_array[1] = true;
                if (strtolower($var_array[1]) == "false")
                $var_array[1] = false;
                $variable = $var_array[0];                
                $this->$variable = $var_array[1];
         public function getValue($key){
            return $this->$key;


And, as the comments say, you pass it a file with key / value pairs in the constructor like this:

$config = new config($myfile);

and then you can do something like this:

$foo = $config->getValue("foo");

Anyway, that’s it really… stay tuned for more news as the rewrite progresses.


Magento: solving ‘invalid API path’ errors

I’ve noticed a bug with Magento that affects the code I wrote for the ‘Extending the API’ tutorial so I thought I’d post a fix for it here.  Well, I assume it’s a bug since I don’t see how it could be anything else…

Here’s the article in question

Here’s the api.xml file from that tutorial:

            <modulename_category translate="title" module="modulename">
                <title>New Category API</title>
                	<getID translate="title" module="modulename">
                		<title>Retrieve Category ID from its name</title>

You’ll notice that I declare a ‘resources_function_prefix’ of ‘catalogCategory’ to fit in with the existing Magento API.  this means that my new method called ‘getID’ can be accessed via a soap call to ‘catalogCategoryGetID’.  This all seems straightforward… or so I thought.

It appears that during execution of soap calls, that resource prefix is mapped directly to a magento module.  Hence, if you have a prefix of ‘catalogCategory’ defined in your module any methods with that prefix will be mapped to your module..including the methods defined in the Magento class we’re overriding with our module.  So for all the standard Magento API calls you’ll receive a nice ‘invalid APi path’ error.

The workaround for this is pretty obvious, we need to declare our new methods with a different function prefix and then update the wsi.xml (the wsdl file) accordingly.

This of course means you’ll be accessing your methods with a different prefix than the standard magento one (so something like myModuleCatalogCategoryGetID rather than catalogCategoryGetID) but that’s a small price to pay for it actually, you know, working.


Job Hunting!

Thought I might as well post something here, you never know who reads these things…

I am currently on the hunt for a job.  Ideally something in PHP / Web development that pays around £30k (or slightly less for the right role) and is based somewhere near Manchester (UK).  Working from home would be nice though if you’re based on the other side of the world.

email me for a CV if you have any opportunities that might fit the bill.