✔ mysql: Zahlen mit Unterstrichen sortierung

[Microhome]

Hallo liebe Community,
in einer Datenbank habe ich die Spalte 'path' in welcher Werte wie bspw. '1_3_7', '1_3_8', '1_3_9' und '1_3_10' drinstehen. Nun möchte ich diese ordnen. Da der Datentyp der Spalte TEXT ist, ordnen mir mysql die Werte leider falsch (nach der 1 kommt bspw. die 10 und erst danach die 2). Wie kann ich es schaffen diese korrekt zu ordnen


Vielen Dank für eure Tipps!

 

[SimonErich]

Hallo @Microhome

Mit dem sollte es eigentlich gehen:

SELECT spalte FROM tabelle
ORDER BY (SUBSTRING(spalte, INSTR(spalte, ' _')) + 0);

Das ist aber ein SQL und kein PHP Problem.
Ausser du willst die Sortierung in PHP realisieren.

Dann liest du alle aus und machst ein Array mit diesem Feld als Array-element-key, der dann mit nat_sort sortiert wird.


Grüße Simon

 

[Microhome]

Danke für deine schnelle Antwort.
Leider hauts damit alles durcheinander. Genau wie wenn ich einfach 'ORDER BY path+0 ASC' mache. Gibt es denn keine andere Möglichkeit das direkt via mysql query zu sortieren?

 

[Sven Mintel]

Wenns ein String ist, wird es auch als solcher sortiert...und da ist 10 kleiner als 2

Du musst das irgendwie in einen anderen Typ umwandeln....bei den 3 Zahlen-Teilen ginge das bspw., indem man den Datums-Typ missbraucht :suspekt::

order by time_to_sec(replace(spalte,'_',':'))

...allerdings nur, wenn die 1. Zahl nicht grösser als 23 ist, und die anderen beiden nicht grösser als 59 :-(

 

[Microhome]

Hmm nee das ist leider nicht vorhersehbar, weil es um ein CMS geht und dieser Pfad die Reihenfolger der einzelnen Menüpunkte angibt und ich den Nutzer nicht beschränken möchte. Könnte ich vielleicht die Unterstriche in Nullen umwandeln und dann den String zur Abfrage in ein INT konvertieren?


// Edit: Wenn die Zahl einstellig ist muss doch lediglich jedesmal zur Abfrage eine Null davorgesetzt werden, dann sollte es doch auch funktionieren, oder?

 

[Sven Mintel]

// Edit: Wenn die Zahl einstellig ist muss doch lediglich jedesmal zur Abfrage eine Null davorgesetzt werden, dann sollte es doch auch funktionieren, oder?

Ich denke, das ist nicht vorhersehbar?

Abgesehen davon: wenn du bei einstelligen Zahlen eine 0 davor setzt, wäre 10_11_1 gleich 1_1_11

Im allgemeinen wäre es schon vorteilhaft, wenn du dies entweder in einem sinnvollen Typ speicherst, oder aber wenigstens dafür sorgst, dass alle Zahlen gleich viel Stellen haben.

Falls es dir ausreicht, nach dem 1. Teil zu sortieren, ginge dies natürlich einfacher.

 

[Microhome]

Na eben das meine ich ja, dass also während der Abfrage die Zahlen aufgefüllt werden. Nur wie kann ich das in der Abfrage realisieren?

3_1_1 -> 0003_0001_0001
3_1_2 -> 0003_0001_0002
3_1_9 -> 0003_0001_0009
3_1_10 -> 0003_0001_0010

So wäre die Sortierung korrekt.

 

[Sven Mintel]

Nur wie kann ich das in der Abfrage realisieren?

Garnicht, würde ich mal behaupten wollen.

 

[Sven Mintel]

Geht doch irgendwie

SELECT * 
  FROM `tabelle`  
    order by 
      CONCAT(
        REPEAT('0',4-LENGTH(SUBSTRING_INDEX(`spalte`,'_',1))),
        SUBSTRING_INDEX(`spalte`,'_',1),
        REPEAT('0',4-LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(`spalte`,'_',-2),'_',1))),
        SUBSTRING_INDEX(SUBSTRING_INDEX(`spalte`,'_',-2),'_',1),
        REPEAT('0',4-LENGTH(SUBSTRING_INDEX(`spalte`,'_',-1))),
        SUBSTRING_INDEX(`spalte`,'_',-1)
        )

 

[Microhome]

Hallo Sven,
vielen Dank für deine Hilfe! Ich hab das ganze jetzt jedoch in PHP realisiert, weil es denke ich einfacher ist. Hier mein Code:

$res = mysql_query("SELECT * FROM content");
while ($check = mysql_fetch_object($res)){
   $id = $check->id;
   $path = $check->path;
   $sortpath[$id] = $path;
}
natsort($sortpath);