Perform Natural Sort Order in MySQL on Non-numeric Fields

MySQLI was working on a project recently where the e-commerce client organized their inventory using product SKU values formatted numerically. The MySQL field type was a varchar yet the values entered where formatted like: 10123, 65432, 98765, etc.

The client’s most recent product update presented a sort order problem. Their new set of products had SKU values that surpassed 5-digits, formatted like 100123. The default sort order is to show the highest numerical SKUs at the top. Because MySQL expects the SKU to be a string value, the actual numerical values sorted incorrectly — all the newest products were at the bottom.

Luckily there is a quick and easy fix for this type of problem. You can force a MySQL SELECT statement to sort naturally. A natural sort is really just a term for how humans might commonly order a set of information. It is easy to achieve in MySQL:

SELECT `product_title`, `product_sku`
FROM `products`
ORDER BY `product_sku` + 0 DESC

By adding a “+ 0″ to the order by column, this forces the data to be sorted naturally without altering the results. There you have it, a quick and easy fix for sorting string values in a usable way.

Posted in Web Development | Tagged , , | Leave a comment

BWCA Canoe Trip 2010

This summer I was lucky enough to spend a week with some great guys from First Evangelical Free Church in theĀ  Boundary Waters Canoe Area Wilderness. It was perfect weather, few bugs, completely exhausting, but most importantly — a blast!

We had two different groups of guys; one team of 8 and another of 9. I was on the eight man “Alpha” team. I put the below video together before we shared photos so “Bravo” team’s experience isn’t included.

Courtesy of our fearless leader Jeff, here are some of the trip details.

Alpha Team

  • Trip distance: 33 miles
  • Number of lakes: 20
  • Number of portages: approx. 20
  • Number of beaver dams: seemed like hundreds
  • Low point: 1650 feet (Fente Lake, right before 320 rod portage)
  • High point: 1990 feet (early on 480 rod portage between Zenith & Lujenida lakes)

Bravo Team

  • Trip distance: 18 miles
  • Number of lakes: 5
  • Number of portages: 7
  • Low point: 1788 feet (both Sawbill & Cherokee lakes)
  • High point: 1853 feet (on portage from Skoop Lake to Cherokee Creek)

Trip Photos

Trip Map

Posted in Outdoors | Tagged , , , , | Leave a comment

Portrait of Summer

We have a family friend who happens to be a talented photographer. He was up visiting my parents during the Minnesota State Fair and we asked for a quick photo shoot. After Dave worked his magic, we now have our first family portrait with all six of us!

Left to Right: Ryan, Jessica, Claire, Leah, Pete, Molly

Check out more of David Burke’s Photography skills when you get a chance.

Posted in Photography | Tagged , , | Leave a comment

Patching Magento 1.3.x to run on PHP 5.3

MagentoI officially changed to using PHP 5.3 for local development quite some time ago. I occasionally need to work with older projects that don’t run so well without a few adjustments. Magento versions prior to 1.4 need a little help when running on PHP 5.3. I would take a look at the Migrating to 5.3.x to get familiar, but to get going quickly just following the below steps.

The first error you’ll see if you experience this problem is probably this:

Fatal error: Method Varien_Object::__tostring() cannot take arguments in /lib/Varien/Object.php

Let’s take care of it by editing the file lib/Varien/Object.php and look around line 477 for this:

/**
 * Convert object attributes to string
 *
 * @param  array  $arrAttributes array of required attributes
 * @param  string $valueSeparator
 * @return string
 */
public function __toString(array $arrAttributes = array(), $valueSeparator=',')
{
    $arrData = $this->toArray($arrAttributes);
    return implode($valueSeparator, $arrData);
}

Replace the above code (more specifically, line 484) with the following:

/**
 * Convert object attributes to string
 *
 * @return string
 */
public function __invoke(array $arrAttributes = array(), $valueSeparator=',')
{
    $arrData = $this->toArray($arrAttributes);
    return implode($valueSeparator, $arrData);
}

The additional errors you’ll run into have to do with usage of deprecated functions. Some of the Magento developers like to use the PHP function split() which has been deprecated. Luckily, explode() is a functional replacement that you can swap in. You can search the source code and replace all the occurrences or just alter the error reporting declarations so you don’t trigger the warnings.

Open up the index.php file and around line 35 you’ll find:

error_reporting(E_ALL | E_STRICT);

Change the line to exclude deprecated errors:

error_reporting((E_ALL | E_STRICT) & !E_DEPRECATED)

Good luck!

Posted in Web Development | Tagged , | Leave a comment