Tabelle korrekt sortieren trotz verschiedener Datentypen (String, Zahl, Datum)

[Wursel]

Hallo zusammen,

ich habe eine eher grundsätzliche Frage (auch abseits von PHP/Datenbanken) zum Thema Sortieren von Tabellen. Wie würde man am geschicktesten vorgehen, wenn in einer Tabelle unterschiedliche Datentypen vorkommen und diese sortiert werden müssen? Z.B. eine Spalte mit Strings, die hauptsächlich Zahlen beinhalten, aber auch andere Strings. Wenn man diese sortiert, dann steht 10 direkt hinter 1 usw., weil Zeichen für Zeichen sortiert wird und nicht numerisch. Dann gibt es Datumsspalten (26.08.2015), die als String sortiert keinen Sinn machen. Klar könnte man 2015-08-26 ausgeben, aber das sieht für den Anwender wieder komisch aus.
Wie macht man das am besten? Die Spalten immer doppelt (Anzeige- + Sortierspalte) übermitteln von der Datenbank-Query aus oder irgendwelche Filter im Backend- (PHP) oder im Frontend-Skript (JavaScript), die dann sämtliche Fälle abdecken müssen?

Gibt es da ein best practice?

Grüße,
Yusuf

[fanderlf]

Also was Du z.B. machen kannst ist, dass Du Dir aus den Werten einen fiktiven Sortierwert berechnest. Wie das dann genau umgesetzt wird haengt aber sehr stark von Deinem Problem ab. Zuerst musst Du ja auch erstmal entscheiden welche Spalte Du von welchem Datensatz verwendest. Wenn Du das weisst kannst Du ja z.B. alle Zahlen 10 stellig in einen String umwandeln, bei dem vorne mit 0en aufgefuellt wird. Vom Datum kannst Du je nach geforderter Genauigkeit irgendeine Zahl in einen String umwandeln und davor dann z.B. den Buchstaben 'a' haengen, dann werden alle Datumswerte hinter die Zahlen geschrieben. Falls Du die Strings ans Ende haben moechtest, packst Du vor den Wert beim Sortieren einfach "XXX".
Es sei noch angemerkt dass diese Werte NUR zum sortieren sind und nicht fuer die Anzeige. Sehen nicht sehr gut aus funktioniert aber super.

[Christian Marquardt]

Datenbanken beherrschen üblicherweise die korrekte Sortierung mit Datumswerten. Man kann sich also die Daten schon sortiert aus der DB geben lassen

dann steht 10 direkt hinter 1 usw.

Das Stichwort dazu heißt Natural Sort.
Dafür wird es auch in PHP eine Implementierung geben, so dass man hier nicht die Daten von der DB sortieren lässt, sondern manuell nach dem lesen aus der DB

[defo]

Ich möchte noch ergänzen oder verdeutlichen, dass (fast) alle Probleme, die Du nennst, mit falschen Feldtypen einhergehen.
Ein Datumswert in einer Datumsspalte (Typ Date, Datetime, timestamp, ..) wird wunderbar richtig sortiert.
Ebenso Zahlentypen und Text.

Bei Text kann es vorkommen, das es Probleme mit Zahlensortierung gibt, weil der Mensch an der Stelle vielleicht was anderes erwartet (Einrücken, rechtsbündig Problem,,), das wurde ja bereits beantwortet.
Vielleicht noch ein Hinweis dazu:
Viele der Probleme rühren auch daher, dass Daten Darstellung und Haltung durcheinandergeworfen wird.

Es ist ohne weiteres möglich, eine Datumsspalte sortiert anzuzeigen nach Datum aufsteigend bspw., die Ausgabe allerdings nach deutschem Format zu setzen.

[Wursel]

Ok vielen Dank für die Antworten! Ja das mit natural sort ist das was ich brauche.
Und was natürlich auch stimmt: Wenn die Datenbank die richtigen Typen hätte (z.B. Typ Date o.ä. statt String), dann würde das direkt per "ORDER BY Datum" funktionieren und es gäbe gar nicht erst das Problem. Und bei Integer natürlich auch.

Ich habe öfter die Schwierigkeit, dass die Datenbanken vorgegeben sind (ältere, gewachsene) und dort fast alles nur als String abgelegt wurde - mit allen entsprechenden Nachteilen. Das wurde wohl u.A. gemacht, um möglichst datenbankunabhängig zu sein (yeah - würde auch direkt mit CSV "Datenbank"-Dateien laufen... .

Im konkreten Fall sind dort Zahlen als Strings abgelegt, weil derjenige Sonderfälle vorgesehen hat (bestimmte Zeichen, die einen Korrekturbedarf der Zahl anzeigen). Z.B. ein ? (Fragezeichen), falls die Texterkennung nicht sicher ist, ob die Zahl korrekt erkannt wurde und noch mal den User fragt oder ein Leerzeichen, wenn das Feld vergessen wurde usw. D.h. man kann das Feld nicht einfach als Integer formatieren/konvertieren und ggf. mit NULL arbeiten.
Wie hätte man das sauberer implemetieren können? Immer eine Extraspalte dazu mit dem Status der Zahl? Z.B. Index 1 = valide, 2 = OCR unsicher: User checken lassen, 3 = Wertebereich falsch: User checken lassen etc. pp. und dann die Zahl selbst als Integerspalte?
Gut man könnte auch mit negativen Werten rumfrickeln: -1 ist x, -2 ist y usw. aber da mischt man dann schon wieder Funktionalitäten und wenn man später negative Werte braucht, schießt man sich selbst ins Bein... nein das ist Quatsch.

Aktuell muss das alles im PHP selbst abgefangen werden, weil auch datenbankspezifische Typkonvertierungen via SQL und ODBC nicht überall gleich laufen... "Lustig" wird es, wenn es sehr (!) viele Datensätze sind und man Teile davon sortiert ausgeben will (die ersten 100 aufsteigend). Mal schnell alles in ein gigantisches Array ziehen, sortieren und dann ausgeben... Spaß sieht anders aus. Nun ja - sonst wäre einem ja vielleicht langweilig und man wüsste nicht, wohin mit der ganzen freien Zeit...

Grüße,
Yusuf

[Christian Marquardt]

Üblicherweise validiert man Daten bevor man sie persistiert. Da keiner deinen Geschäftsprozess kennt - irgendwas mit OCR? - wird man wohl auch nicht sagen, können, wo man da etwas verbessern oder ändern sollte. Warum werden offenbar von OCR erhobene Daten ungeprüft in die DB geschrieben?
Warum macht man nicht extra Korrekturläufe? Datensätze die maschinell nicht korrigiert werden können, werden angezeigt und müssen manuell berichtigt werden.
Und wenn das nicht anders geht, muss man halt bei der Auswertung entsprechende Prüfungen, Konvertierungen und Umsetzungen machen

[Arne Drews]

Ich habe öfter die Schwierigkeit, dass die Datenbanken vorgegeben sind (ältere, gewachsene) und dort fast alles nur als String abgelegt wurde

Und eine Übernahme in ein modernes DBMS mit Struktur und Normalisierung ist nicht verhandelbar? Ich mein Vorteile gäbe es ausreichend anzuführen. Ihr arbeitet ja auch nicht mehr mit Lochkarten, nehme ich mal an...

[defo]

Ich habe öfter die Schwierigkeit, dass die Datenbanken vorgegeben sind (ältere, gewachsene) und dort fast alles nur als String abgelegt wurde - mit allen entsprechenden Nachteilen.

Na schön, aus Sch..troh kann man kein Gold machen.
Wenn es Dein Job ist, den Dreck am Laufen zu halten, dann ist es eben so.
Vielleicht ist aber auch niemand klar, was da für Probleme vorliegen. Also klar und unmissverständlich kommunizieren nach oben. Vielleicht droht man Seppuku an oder sowas

Wenn das Thema durch ist und alle Verantwortlichen Bescheid wissen und genug Schmerzensgeld fließt, dann muss man das beste draus machen.
Views und Konvertierungsfunktionen einsetzen, alles blocken und rauswerfen, was nicht validierbar ist, Kataloge mit Highendhardware besorgen und Postit einkleben, dabei nach Möglichkeit den ganzen Tag laut fluchen, Mitarbeiter anrempeln, wenn man den Arbeitsplatz verlässt oder betritt, Kotztütenspender aufstellen

[Wursel]

Vielleicht droht man Seppuku an oder sowas

Gute Idee - ich habe gleich über Amazon Prime ein Kilo Wattebällchen bestellt mit denen ich alle bewerfen werde. ;-)

Warum werden offenbar von OCR erhobene Daten ungeprüft in die DB geschrieben?

Es wird ein handausgefülltes Papierformular (so richtig toter Baum, Gutenberg usw.) eingescannt, u.A. wird die Handschrift in bestimmten Feldern per OCR erkannt und dabei in der Datenbank gespeichert. Direkt danach werden die Felder zur Korrektur durch den Scannenden angezeigt. Das kann auch zeitversetzt (jemand scannt, ein anderer korrigiert später eine ganze Liste) erfolgen. Daher sind für eine gewisse Zeit ungeprüfte / nicht validierte Daten in der Datenbank. Diese Datensätze sind aber markiert und werden nicht für Auswertungen verwendet, bis sie durch einen Menschen korrigiert und validiert/bestägt wurden. Wenn in einem Feld steht 3148?, dann hat OCR 3148 erkannt, ist sich aber nicht sicher und hebt das Feld noch mal extra hervor, damit es in jedem Fall korrigiert wird.. Der Anwender kann die Zahl übernehmen oder entsprechend korrigieren.
Könnte man das sauberer lösen, den Status zu hinterlegen?

Und eine Übernahme in ein modernes DBMS mit Struktur und Normalisierung ist nicht verhandelbar? Ich mein Vorteile gäbe es ausreichend anzuführen. Ihr arbeitet ja auch nicht mehr mit Lochkarten, nehme ich mal an...

Du wirst jetzt vermutlich lachen, aber direkt neben mir liegt ein Stapel Z5 Lochkarten von Hummel Magstadt. Aber ehrlich gesagt benutze ich die nur noch als nerdigen Notizzettel (Rückseite). Die waren aber noch lange hier im Einsatz... von daher zur Frage neues DBMS: Ich bin froh, dass hier Altbestände von dBASE IV und MS-Access migriert wurden zu etwas, was so aussieht wie ein DBMS. Aber es ist wie es ist: Wenn die Struktur der Daten nicht sauber ist, hilft einem ein Management selbiger auch nicht mehr viel weiter... Die müssen leider so krude bleiben wie sie sind.

Wenn es Dein Job ist, den Dreck am Laufen zu halten, dann ist es eben so.

Das ist vermutlich so ein Fetisch. Statt Peitsche, Lack und Leder werde ich mit den drei Normalformen gequält... oh yeah.

[defo]

Gute Idee - ich habe gleich über Amazon Prime ein Kilo Wattebällchen bestellt mit denen ich alle bewerfen werde. ;-)

Nein! Es geht um Deinen eigenen Tod, ehrenhalber, also nach dem Motto, diese Tätigkeiten sind so unwürdig, dass .. also glaub ich zumindest. Es gibt ja neben Sepuku auch noch Harakiri, aber das ist noch was anderes.

..und hebt das Feld noch mal extra hervor, damit es in jedem Fall korrigiert wird.. Der Anwender kann die Zahl übernehmen oder entsprechend korrigieren.
Könnte man das sauberer lösen, den Status zu hinterlegen?

Also ich würde nach Möglichkeit die OCR Werte und die validierten Werte trennen. Eine Tabelle mit OCR Werten und eine Tabelle mit validierten Werten und dann eben richtig typisiert, Datums in DATE, Zahlen in INT, Fließkommawerte in NUMBER() Typen usw. Bedeutet letztlich, solange nicht alles in einem Record validiert ist, existieren die Daten nicht. Was / wie sich das umsetzen lässt, hängt natürlich davon ab, wie stark Du in Front- und Backend eingreifen kannst, im schlimmsten Fall Tabellen umbenennen und gegen Views austauschen.

Das ist vermutlich so ein Fetisch. Statt Peitsche, Lack und Leder werde ich mit den drei Normalformen gequält... oh yeah.

Moment, auch wenn das hier thematisch nicht der Schwerpunkt des Forums ist, aber müsste es nicht eher so sein, dass die Peitschen / Fetischnummer für DE-normalisierte Sachen steht?! Die Normalform wäre sozusagen der Standard, also nicht vor der Ehe, Löffelchenstellung usw. .. Gut und die perversen, denormalisierten Sachen, muss ich nicht weiter erläutern.. .