Nate's blog

What’s the problem? Say you have a version number such as 1.2.3 stored in a MySql database as a varchar, and you want to sort this version number. Without any decimals, here is the sorting descending:
+———+
| version |
+———+
| 9960 |
| 9952 |
| 9765 |
| 9764 |
| 10011 |
+———+
5 rows in set (0.00 sec)
It thinks 9960 > 10011
If you don’t have a decimal point this can be easily remedied by doing:
ORDER BY CAST(version AS UNSIGNED) DESC
But as soon as you put a decimal into the version, it ignores everything to the right of the decimal point.
My solution is a PHP sorting algorithm.
$versions is an indexed array that has this format, which could easily come from a database
array
  0 => 
    array
      ’id’ => int 88
      ’version’ => 
        array
          0 => int 0
          1 => int 9
          2 => int 10011
  1 => 
    array
      ’id’ => int 87
      ’version’ => 
        array
          0 => int 0
          1 => int 9
          2 => int 9960

// convert all values to ints
array_walk_recursive($versions, function(&$val, $i)
{
    if (is_string($val))
        $val = (int)$val;
});
// make to arrays the same length by padding them with 0s
function normalize($a, $b)
{
    $al = count($a);
    $bl = count($b);
    if ($al < $bl)
    {
        // add 0s to $a until they are the same size
        while (count($a) < $bl)
            $a[] = 0;
    }
    elseif ($al > $bl)
    {
        // add 0s to $b until they are the same size
        while ($al > count($b))
            $b[] = 0;
    }
    return array($a, $b);
}
//compare to integers and decide which one is bigger
// return 0 if they are equal
function check($a, $b)
{
    if ($a < $b)
        return 1;
    elseif ($a > $b)
        return -1;
    else
        return 0;
}
// sort the array
usort($versions, function($a, $b)
{
    // make sure the major_version arrays have the same length, pad with 0s to correct
    list($a['version'], $b['version']) = normalize($a['version'], $b['version']);
    for ($i=0; $i < count($a['version']); $i++)
        $check = check($a['version'][$i], $b['version'][$i]);
    return $check;
});