[SOLVED] order by issue

[friedemann_bach]

Hello all,

 

I have a ORDER BY question. In a VARCHAR field, I have written some values like:

1

1.1

1.1.3.5

1.1.3.5.1

1.1.3.5.2

 

and so on. It is meant as a hierarchical structure.

 

Now, on ordering entries like

 

1.9

1.10

1.11

 

MySQL will order this naturally as follows:

 

1.10

1.11

1.9

 

Which is, as you will guess, not my idea. Is it somehow possible to order it in the correct way without modifying the data?

 

[Yesideez]

Not really - sorting goes character by character and not the way we (as humans) would "logically" order them.

[friedemann_bach]

It would be nice if I there was a method to convert the outline fields to another format that is sortable, like with leading zeros:

 

01.09

01.10

01.11

 

I would like to hide the leading zeros from the user, though.

 

[Yesideez]

You can easily convert leading 0 to no leading zero by exploding the data...

 

SOURCE: 01.01.01.02.12.01

$arrElements=explode('.',$source);
$arrElements=ltrim($arrElements,'0');
$final=implode('.',$arrElements);

 

That would convert "01.01.01.02.12.01" into "1.1.1.2.12.1"

[Yesideez]

You can easily write a script to convert the existing data in your database to readable format:

$query=mysql_query("SELECT id,source FROM table");

while ($row=mysql_query($query)) {

  $arrElements=explode('.',$row['source']);

  foreach ($arrElements as $element) {

    $element=($element<9 ? '0' : '').$element;

  }

  $converted=implode('.',$arrElements);

  mysql_query("UPDATE table SET source='".$converted."' WHERE id=".$row['id'].");

}[/code]

 

Something like that.

[friedemann_bach]

Many thanks for this!

 

Initially, I hoped that there was a solution to solve this in MySQL, but this works fine as well.

 

Best regards!