ORDER BY ASC - but not quite...

[jaymc]

Take a look at the following data which is displayed in the ASC order

[b]Bounce To The Bass 13
Bounce To The Bass 16
Bounce To The Bass 5
Bounce To The Bass 6
Bounce To The Bass 7
Bounce To The Bass 8
NRGetik Beatz Battle Round 1
NRGetik Beatz Battle Round 2[/b]

As you can see, it is officially, in ascending order. However, and I think it would be easier to just demonstrate this, I want it ordered/displayed like this

[b]
Bounce To The Bass 5
Bounce To The Bass 6
Bounce To The Bass 7
Bounce To The Bass 8
Bounce To The Bass 13
Bounce To The Bass 16
NRGetik Beatz Battle Round 1
NRGetik Beatz Battle Round 2[/b]

I assume the example is enough clarification. How do I go about this?

[extrovertive]

SELECT  REVERSE(TRIM(SUBSTR(REVERSE(title), 3))) as title, (TRIM(RIGHT(title, 2))+0) as track
FROM songtitle
ORDER By title, track

[jaymc]

I take it track = Bounce To The Bass 13 variables etc?

[extrovertive]

title is the whole name - i.e. "Bounce To The Bass 5"

I then truncate that to "Bounce To The Bass" - stripping off the number at the end.

So now,
track is just the track #
title is the name of the album

Example:
title = Bounce To The Bass
track = 5

"songtitle" is the name of the table (you can change it to whatever you want).

I had to split the title field into two alias columns to properly sort it.

But if you want them all in one field use, you can also use a subqeury

[code=php:0]
SELECT CONCAT(title, '  ', track) as title FROM
(
SELECT  REVERSE(TRIM(SUBSTR(REVERSE(title), 3))) as title, (TRIM(RIGHT(title, 2))+0) as track
FROM songtitle
ORDER By title, track
) as tracktable
[/code]

Just change the table name "songtitle" to whatever you have.

Of course, there are better ways to do this.

[jaymc]

It does seem a bit far fetched, even though it works

Are their any alternitive ways to combat this issue?

[shoz]

[quote author=jaymc link=topic=112611.msg457360#msg457360 date=1161781269]
It does seem a bit far fetched, even though it works

Are their any alternitive ways to combat this issue?
[/quote]

You can sort it in PHP using [url=http://www.php.net/usort]usort()[/url]. It may be faster.
[code]
<?php
function sort_text($a, $b)
{
    $a_parts = preg_split('/([0-9]+)$/', $a, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
    $b_parts = preg_split('/([0-9]+)$/', $b, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);

    if (0 == ($cmp = strcmp($a_parts[0], $b_parts[0])))
    {
        $a_num = isset($a_parts[1])? (int)$a_parts[1]: -1;
        $b_num = isset($b_parts[1])? (int)$b_parts[1]: -1;
        if ($a_num == $b_num)
        {
            return 0;
        }
        return ($a_num < $b_num)? -1: 1;
    }
    else
    {
        return $cmp;
    }
}
$a = array('b', 'b 01', 'a 1', 'a 31', 'a 30', 'a 15', 'a 10', 'a 01');
print_r($a);

usort($a, 'sort_text');
print_r($a);
?>
[/code]

I felt that if I was going to provide an alternate query to do the ordering that it should be comprehensive. This will sort any text that may end with digits. Sorting based on the preceding text and then the digits.

It should be very accurate but I would recommend doing the sorting in PHP because it's easier to understand , modify and I'd have to assume faster. I'd be surprised if the sorting in PHP wasn't faster than this query.
[code]
SELECT
*
FROM
tablename
ORDER BY
TRIM(TRAILING REVERSE(TRIM(LEADING '-1' FROM CONCAT('-1', REVERSE(columnname)) + 0)) FROM columname),
IF(columname REGEXP('[0-9]+$'), REVERSE(TRIM(LEADING '-1' FROM CONCAT('-1', REVERSE(columnname)) + 0)), -1) + 0;
[/code]

EDIT: modified usort snippet.

[shoz]

The linked/quoted usort snippet goes a step further and will sort throughout the text numerically when a number is encoutered anywhere in the text.

You may want to remove or comment the first 2 lines of the cmp function however.

http://www.php.net/manual/en/function.usort.php#55591
[quote=http://www.php.net/manual/en/function.usort.php#55591]
function cmp($a,$b){
  list($a)=explode(".",$a);
  list($b)=explode(".",$b);
  $s=0;
  for($i=0;$i<=strlen($a);++$i){
    if(is_numeric($a[$i])&&is_numeric($b[$i])){
        $n=1;
        $anum=$bnum=0;
        while((is_numeric($a[$i])||is_numeric($b[$i]))&&
                  (strlen($a)>=$i||strlen($b)>=$i)){
          if(is_numeric($a[$i])&&$i<=strlen($a)) $anum=$a[$i]+$anum*$n;
          if(is_numeric($b[$i])&&$i<=strlen($b)) $bnum=$b[$i]+$bnum*$n;
          $n*=10;
          ++$i;
        }
        if($n>1) --$i;
        if($anum!=$bnum) return $anum<$bnum?-1:1;
    }elseif($a[$i]!=$b[$i]) return $a[$i]<$b[$i]?-1:1;
  }
}
?>
[/quote]

[jaymc]

Ive got this example working fine, still got a problem though which i'll explain later

[code]<?php
function sort_text($a, $b)
{
    $a_parts = preg_split('/([0-9]+)$/', $a, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
    $b_parts = preg_split('/([0-9]+)$/', $b, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);

    if (0 == ($cmp = strcmp($a_parts[0], $b_parts[0])))
    {
        $a_num = isset($a_parts[1])? (int)$a_parts[1]: -1;
        $b_num = isset($b_parts[1])? (int)$b_parts[1]: -1;
        if ($a_num == $b_num)
        {
            return 0;
        }
        return ($a_num < $b_num)? -1: 1;
    }
    else
    {
        return $cmp;
    }
}
$a = array('b', 'b 01', 'a 1', 'a 31', 'a 30', 'a 15', 'a 10', 'a 01');
print_r($a);[/code]

That is working fine, however, its not doing exactly what I want

here is an example of the array i will be using

[code]$a = array('b|34', 'b 01|65', 'a 1|435', 'a 31|345', 'a 30|43', 'a 15|345', 'a 10|224', 'a 01|678');[/code]

I need that sorting like the function above is doing, but, not to display anything on the right hand side of the |

However, once the sort has been completed, I will need the numbers on the right and side of the | for each element thats been sorted.

The sort will be echoed as html as a href link you see, and the numbers on the right are ID's of which I need. The text on the left of the | is just the URL text link

Hope ive explained that well enough, any ideas?

[shoz]

If you're only asking how to modify the function to do the sort based on the text on the left of the "|", then the following should work
[code]
<?php
function sort_text($a, $b)
{
    $a_parts = preg_split('/(?:([0-9]+)?\|[0-9]+)$/', $a, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);
    $b_parts = preg_split('/(?:([0-9]+)?\|[0-9]+)$/', $b, -1, PREG_SPLIT_DELIM_CAPTURE|PREG_SPLIT_NO_EMPTY);

    if (0 == ($cmp = strcmp(trim($a_parts[0]), trim($b_parts[0]))))
    {
        $a_num = isset($a_parts[1])? (int)$a_parts[1]: -1;
        $b_num = isset($b_parts[1])? (int)$b_parts[1]: -1;

        if ($a_num == $b_num)
        {
            return 0;
        }
        return ($a_num < $b_num)? -1: 1;
    }
    else
    {
        return $cmp;
    }
}
$a = array('b|34', 'b 01|65', 'a 1|435', 'a 31|345', 'a 30|43', 'a 15|345', 'a 10|224', 'a 01|678');
print_r($a);

usort($a, 'sort_text');
print_r($a);
?>
[/code]

//output
[code]
Array
(
    [0] => b|34
    [1] => b 01|65
    [2] => a 1|435
    [3] => a 31|345
    [4] => a 30|43
    [5] => a 15|345
    [6] => a 10|224
    [7] => a 01|678
)
Array
(
    [0] => a 01|678
    [1] => a 1|435
    [2] => a 10|224
    [3] => a 15|345
    [4] => a 30|43
    [5] => a 31|345
    [6] => b|34
    [7] => b 01|65
)
[/code]

EDIT: modified snippet

[jaymc]

Worked perfect!

Thanks!!!!