MySQL - nächst liegende Zahl suchen

[Patrick]

Hallo zusammen!

Ich entwickle für ein Schulprojekt derzeit eine simple Fahrplanauskunft auf Datenbankbasis.
Dabei habe ich bereits die Datenbank angelegt und gefüllt.

Sie verzeichnet über 3000 Zeilen. In jeder wird eine Abfahrtszeit, eine ID und der Name der Haltestelle in Form einer zweistelligen Zahl angegeben.

Mit einer Abfragemaske möchte ich nun erreichen, dass die nächst liegende Abfahrtszeit für eine bestimmte Haltestelle ausgegeben wird.

Beispiel:
In der Datenbank zeigt eine Zeile, dass die Linie 5 um 13:30 Uhr an der Haltestelle "Bahnhof" abfährt.
Um 13:20 Uhr und 13:40 Uhr - sagen wir im 10-Minuten-Takt - fährt die gleiche Linie an der gleichen Haltestelle wieder ab.

Nun gibt jemand an, um 13:27 Uhr abfahren zu wollen.

Es soll nun ausgegeben werden, dass um 13:30 Uhr eine Fahrt stattfindet. Nicht um 13:20 Uhr und nicht um 13:40 Uhr, denn 13:30 Uhr liegt am nächsten an 13:27 Uhr dran.

Wie soll ich nun eine solche Abfrage gestalten? Wie finde ich mit MySQL (die Auskunft selbst ist mit PHP geschrieben) heraus, welche Zeile aus der Datenbank dem eingegeben Wert am nächsten ist, am meisten ähnelt?

Ich denke, es gibt dafür eine relativ einfache Lösung, aber ich komme einfach nicht darauf.

Bitte helft mir!

[Stefan Einspender]

Hallo,

wenn jemand erst 13:33 Uhr abfahren kann, dann hilft es ihm wenig, wenn Du
sagst, er soll 13:30 Uhr an der Haltestelle sein. D.h. erstmal solltest Du
ihm die nächstmögliche Abfahrtszeit ausgeben. Netterweise kannst Du ihn ja
auch noch darauf hinweisen, dass er auch drei Minuten eher hätte abfahren
können. Aber erstmal die nächste Abfahrtszeit ...

Viele Grüße,
Stefan

[Patrick]

Hallo Stefan,

D.h. erstmal solltest Du ihm die nächstmögliche Abfahrtszeit ausgeben.

Das ist eigentlich auch das, was ich wollte.

Jemand sagt "Hey, ich möchte gerne um ca. 13 Uhr abfahren". Das System schaut nach, findet eine Abfahrt um 13.03 Uhr und sagt "Kein Problem, um 13.03 Uhr fährt eine Bahn."
Es gibt also die nächstmögliche Abfahrtszeit nach der Wunsch-Abfahrtszeit aus.

Das Problem ist nun auch gelöst.

Netterweise kannst Du ihn ja
auch noch darauf hinweisen, dass er auch drei Minuten eher hätte abfahren
können.

Das kommt ggf. später noch. Danke für Deine Anregung!

[Samoht]

Hi,

sowas wie

select min(abfahrtszeit)from tabelle where abfahrtszeit>NOW()

müsste funktionieren, oder was meint die gemeinschaft?

Samoht

[Samoht]

Hi,

sowas wie

select min(abfahrtszeit)from tabelle where abfahrtszeit>NOW()

müsste funktionieren, oder was meint die gemeinschaft?

Samoht

NOW() bezieht sich natürlich auf die aktuelle Uhrzeit. Hier kann genauso gut ein übergebener Wert stehen.

Samoht

[Patrick]

Hi,

sowas wie

select min(abfahrtszeit)from tabelle where abfahrtszeit>NOW()

müsste funktionieren, oder was meint die gemeinschaft?

Samoht

NOW() bezieht sich natürlich auf die aktuelle Uhrzeit. Hier kann genauso gut ein übergebener Wert stehen.

Samoht

Hi Samoht,

vielen Dank - so habe ich es nun gelöst:

$auswaehlen2= "SELECT MIN(Uhrzeit) AS Uhrzeiten FROM Fahrzeiten WHERE Wochentag = '$SuchWochentag1' AND Haltestelle = '$auswaehlen1' AND Uhrzeit > '$Uhrzeit1'";
$auswaehlen2= mysql_query($auswaehlen2);
$auswaehlen2= mysql_fetch_object($auswaehlen2);
$auswaehlen2= $auswaehlen2->Uhrzeiten;

echo $auswaehlen2;

In $Uhrzeit1 steht die gewünschte Abfahrtszeit; in $auswaehlen1 die ID der Haltestelle, in $SuchWochentag1 der gewünschte Abfahrts-Wochentag.

[Vinzenz Mai]

Hallo Patrick,

$auswaehlen2= "SELECT MIN(Uhrzeit) AS Uhrzeiten FROM Fahrzeiten WHERE Wochentag = '$SuchWochentag1' AND Haltestelle = '$auswaehlen1' AND Uhrzeit > '$Uhrzeit1'";

$auswaehlen2= mysql_query($auswaehlen2);

bitte behandle Eingabwerte, die von außen kommen, gemäß ihrem Kontext.
Hier wäre die Verwendung von mysql_real_escape_string() zwingend erforderlich, wenn Du Dir nicht ein Sicherheitsloch öffnen willst. Das passende Stichwort heißt SQL-Injection.

In $Uhrzeit1 steht die gewünschte Abfahrtszeit; in $auswaehlen1 die ID der Haltestelle, in $SuchWochentag1 der gewünschte Abfahrts-Wochentag.

Außerdem: bitte keine Fullquotes.

Freundliche Grüße

Vinzenz

[Patrick]

Hallo Vinzenz,

danke für Deine Antwort und den Hinweis zur Sicherheit.

Dazu muss ich jedoch anmerken, dass eine SQL-Injection eigentlich nicht möglich ist.

Die Auswahl der Haltestelle und des Wochentags geschieht in einem Dropdown-Menü, die Eingabe der Uhrzeit wird geprüft (ob die Eingabe nur aus Zahlen besteht - wenn nicht, kommt gleich eine Fehlermeldung).

Soweit ich das sehe, ist damit das Einschleusen von schadhaften Code ausgeschlossen.

[Tom]

Hello,

Soweit ich das sehe, ist damit das Einschleusen von schadhaften Code ausgeschlossen.

Wenn Du die Prüfung auf dem Server vornimmst, kann das reichen,
wenn Du allerdings auf dem Client (z.B. mit JavaScript) prüfst, ist das wertlos.

Bedenke, dass jedes Formular fälschbar ist und dass z.B. Bots gar keine "Formulare" benutzen, sondern gleich einen Post-to-Host ausführen.

Liebe Grüße

Tom vom Berg

[Patrick]

Hallo,

Wenn Du die Prüfung auf dem Server vornimmst, kann das reichen

Ja, die Prüfung findet per PHP - also serverseitig - statt.

Bedenke, dass jedes Formular fälschbar ist und dass z.B. Bots gar keine
"Formulare" benutzen, sondern gleich einen Post-to-Host ausführen.

Gewiss ist das alles nicht 100 %ig sicher vor Angriffen, aber die Fahrplanauskunft wird ja auch nicht jedem zur Verfügung gestellt.
Wobei das schon wieder fast schade ist. ;-)

So oder so sollte und werde ich mich aber um Sicherheitsmaßnahmen vertraut(er) machen - auch wenn nicht unbedingt für dieses Projekt.

[Struppi]

Dazu muss ich jedoch anmerken, dass eine SQL-Injection eigentlich nicht möglich ist.

Die Auswahl der Haltestelle und des Wochentags geschieht in einem Dropdown-Menü,

und genau das ist nicht sicher. Alle Werte die ungefiltert, egal aus welcher Quelle in der Datenbank landen sind unsicher.

Auch wenn es nur eine interne Seite ist, dir ist klar dass mit so einem Angriff u.U. die Daten komplett gelöscht werden können und zwar von allen Tabellen? Da das, soweit ich sehe, ein Schulprojekt ist, würde ich so einen "Scherz" nicht ausschliessen.

Struppi.

[Patrick]

Hallo Struppi,

und genau das ist nicht sicher. Alle Werte die ungefiltert, egal aus welcher Quelle in der Datenbank landen sind unsicher.

In Ordnung. Ich hab's verstanden und werde mich darüber mal näher informieren.

Auch wenn es nur eine interne Seite ist, dir ist klar dass mit so einem Angriff u.U. die Daten komplett gelöscht werden können und zwar von allen Tabellen? Da das, soweit ich sehe, ein Schulprojekt ist, würde ich so einen "Scherz" nicht ausschliessen.

Der Benutzer hat nur Zugriff auf zwei Tabellen. Die Datenbanken werden regelmäßig gesichert.
Aber grundlegend hast Du wohl recht.

[Struppi]

Auch wenn es nur eine interne Seite ist, dir ist klar dass mit so einem Angriff u.U. die Daten komplett gelöscht werden können und zwar von allen Tabellen? Da das, soweit ich sehe, ein Schulprojekt ist, würde ich so einen "Scherz" nicht ausschliessen.

Der Benutzer hat nur Zugriff auf zwei Tabellen.

Nein. der Benutzer hat, wenn die SQL Injection erfolgreich ist, den gleichen Zugriff wie das PHP Skript, also vermutlich auf die komplette DB und alle darin enthalteten Tabellen, evtl sogar noch mehr, je nachdem wie das bei dir konfiguriert ist. Er kann dann jeden x-beliebigen SQL Befehl ausführen (z.b. DROP TABLE)

Struppi.

[Patrick]

Nein. der Benutzer hat, wenn die SQL Injection erfolgreich ist, den gleichen Zugriff wie das PHP Skript, also vermutlich auf die komplette DB und alle darin enthalteten Tabellen, evtl sogar noch mehr, je nachdem wie das bei dir konfiguriert ist. Er kann dann jeden x-beliebigen SQL Befehl ausführen (z.b. DROP TABLE)

Ich verstehe nicht, wie das funktionieren soll. Wie soll denn an die Daten anderer Benutzer herangekommen werden?
Oder wie soll ein Datenbank-Benutzer, der nur Rechte für den Zugriff auf eine Datenbank hat, auf andere Datenbanken zugreifen können?

Ich werde mich mal genau(er) die "Verbesserte MySQL Erweiterung" informieren.

[Struppi]

Ich verstehe nicht, wie das funktionieren soll.

Das Stichwort hat dir Vinzenz schon gesagt: https://forum.selfhtml.org/?t=169045&m=1103587

Struppi.

[Patrick]

Das Stichwort hat dir Vinzenz schon gesagt: https://forum.selfhtml.org/?t=169045&m=1103587

Ich habe das ganze nun (überall) so abgeändert:

$auswaehlen2= "SELECT MIN(Uhrzeit) AS Abfahrtszeit FROM Fahrzeiten WHERE Wochentag = '".mysqli_real_escape_string($link,$SuchWochentag1)."' AND Haltestelle = '".mysqli_real_escape_string($link,$auswaehlen1)."' AND Uhrzeit >= '".mysqli_real_escape_string($link,$Uhrzeit1)."'";

Man sieht auch, dass ich nun überall - wie angeraten wurde - auf mysqli gesetzt habe. Funktionieren tut das Ganze wie gewohnt, das Umschreiben war auch nicht so aufwendig wie ich befürchtet habe.

Ist denn so das Risiko einer SQL-Injection gemindert bzw. wird eine solche verhindert? Oder habe ich da (noch etwas) falsch verstanden bzw. noch nicht umgesetzt?

[dedlfix]

echo $begrüßung;

Auch wenn es nur eine interne Seite ist, dir ist klar dass mit so einem Angriff u.U. die Daten komplett gelöscht werden können und zwar von allen Tabellen?

Ganz so kooperativ gegenüber derartigen Angriffen zeigt sich MySQL nicht. Ein SELECT lässt sich nicht zu einem DELETE umformen, noch kann man ihm ein solches (z.B. per Subquery) einbauen, oder eins hintendranhängen (was dann eine Query mit mehreren Statements wäre).

Nichtsdestotrotz ist SQL-Injection bzw. überhaupt das Ignorieren kontextgerechter Behandlung von Werten kein Kavaliersdelikt und sollte immer, nicht erst für das nächste Projekt, oder wenn man mal Zeit hat, beachtet werden.

echo "$verabschiedung $name";

[Vinzenz Mai]

Hallo Patrick,

danke für Deine Antwort und den Hinweis zur Sicherheit.

Dazu muss ich jedoch anmerken, dass eine SQL-Injection eigentlich nicht möglich ist.

Die Auswahl der Haltestelle und des Wochentags geschieht in einem Dropdown-Menü,

sind also beliebig fälschbar, wie bereits angemerkt wurde. Hier besteht Handlungsbedarf

die Eingabe der Uhrzeit wird geprüft (ob die Eingabe nur aus Zahlen besteht - wenn nicht, kommt gleich eine Fehlermeldung).

damit verschließt Du für einen Wert die Möglichkeit, Schadcode einzuschleusen, aber nicht für alle.

Soweit ich das sehe, ist damit das Einschleusen von schadhaften Code ausgeschlossen.

Nein. Grundsätzlich empfehle ich Dir, Dich von den althergebrachten und ebenso vertrauten, wohldokumentierten wie leistungsarmen mysql_*-Funktionen zu verabschieden und statt dessen die verbesserte MySQL-Unterstützung oder PDO mit prepared Statements zu nutzen. Bei diesem Verfahren werden SQL-Statement und Parameter getrennt zum DBMS übermittelt und das DBMS kümmert sich um die notwendige Maskierung.

Das Warnungsfenster in der veralteten deutschen Übersetzung kannst Du getrost ignorieren, statt dessen sollte eher bei der traditionellen MySQL-Unterstützung ein Warnfenster sein, dass diese Funktionen veraltet sind und nicht mehr verwendet werden sollten :-)

Freundliche Grüße

Vinzenz

[ChrisB]

Hi,

so habe ich es nun gelöst:

$auswaehlen2= "SELECT MIN(Uhrzeit) AS Uhrzeiten FROM Fahrzeiten WHERE Wochentag = '$SuchWochentag1' AND Haltestelle = '$auswaehlen1' AND Uhrzeit > '$Uhrzeit1'";

Na ja, das ist nur 'ne halbe Loesung.

Wenn ich um 23:30 nach 'nem Bus suche, und der naechste aber erst um 0:05 am naechsten Morgen faehrt - dann bekomme ich von dir gar keinen Vorschlag ...?

MfG ChrisB

[Tom]

Hello,

$auswaehlen2= "SELECT MIN(Uhrzeit) AS Uhrzeiten FROM Fahrzeiten WHERE Wochentag = '$SuchWochentag1' AND Haltestelle = '$auswaehlen1' AND Uhrzeit > '$Uhrzeit1'";

Na ja, das ist nur 'ne halbe Loesung.

Wenn ich um 23:30 nach 'nem Bus suche, und der naechste aber erst um 0:05 am naechsten Morgen faehrt - dann bekomme ich von dir gar keinen Vorschlag ...?

Wenn er die Zeit als Datetime oder Timestamp abspeichert, schon.

Liebe Grüße

Tom vom Berg

[ChrisB]

Hi,

Wenn ich um 23:30 nach 'nem Bus suche, und der naechste aber erst um 0:05 am naechsten Morgen faehrt - dann bekomme ich von dir gar keinen Vorschlag ...?

Wenn er die Zeit als Datetime oder Timestamp abspeichert, schon.

Dann aber auch nur, wenn er wirklich fuer *jedes* Datum die Daten ablegt - was bei woechentlichen Fahrplaenen aber weniger sinnvoll sein duerfte.

MfG ChrisB

[Tom]

Hello,

Wenn ich um 23:30 nach 'nem Bus suche, und der naechste aber erst um 0:05 am naechsten Morgen faehrt - dann bekomme ich von dir gar keinen Vorschlag ...?

Wenn er die Zeit als Datetime oder Timestamp abspeichert, schon.

Dann aber auch nur, wenn er wirklich fuer *jedes* Datum die Daten ablegt - was bei woechentlichen Fahrplaenen aber weniger sinnvoll sein duerfte.

Ist auch was dran.
Dann muss er eine Funktion daraus machen.

Allerdings denke ich, dass es einfacher sein wird, tatsächlich jede Fahrt einzutragen.
Damit er das nicht händisch tun muss, würde ich mir Vorlagen bauen.

Mo-Fr Standard
Mo-Fr Winter
Sa Standard
Sa Winter
So Standard
So Winter
Feiertag Standard
Feiertag Winter

Angenommen, so ein Bus fährt von 06:00 bis 20:00 Uhr alle 20 Minuten, sind das 42 Fahrten
Gehen wir mal im Mittel von 30 Haltestellen auf der Strecke aus, dann sind es 1260 Datensätze pro Tag.
Wenn er Samstags und Sonntags nur halb soviele Fahrten hat, kommt man auf ca. 394.380 Datensätze pro Linie im Jahr.

Wieviele solcher Datensätze verträgt eine MySQL-Tabelle?

Angesichts dessen, dass vielleicht auch Anschlüsse zu anderen Linien festgestellt werden sollen, erachte ich die Brutalmethode wirklich für die beste.

Liebe Grüße

Tom vom Berg

[Patrick]

Hallo Chris,

Wenn ich um 23:30 nach 'nem Bus suche, und der naechste aber erst um 0:05 »» am naechsten Morgen faehrt - dann bekomme ich von dir gar keinen Vorschlag »» ...?

Ja, das stellt in der Tat noch ein Problem dar.
Bei Verbindungen, bei der die Ankunft am nächsten Tag stattfindet, versagt das System (noch).

Die Eingabemaske sieht die Felder

• Haltestelle (Dropdown)
• Wochentag (Dropdown)
• Uhrzeit (Eingabefeld für Stunde, Eingabefeld für Minute)
  vor. Einmal für die Abfahrt, einmal für die Ankunft.

Es ist nun Blödsinn, wenn jemand um 14:00 Uhr abfahren will und bei der Ankunft den Wochentag angeben muss - das öffentliche Verkehrsmittel wird wohl keine 10 Stunden unterwegs sein.

In der Datenbank ist jede Abfahrt am Werktag, Samstag und Sonntag verzeichnet. Dabei ist die Zeit im Format HH:MM:SS angegeben.
Beispiel:

Haltestelle: 4
Uhrzeit: 12:15:00
Wochentag: WT

Wie schon einmal erklärt steckt hinter jeder Haltestellennummer ein Haltestellenname (welcher separat herausgesucht wird).
Für den Wochentag gibt es die Angaben WT (Werktag), SA (Samstag) und SO (Sonntag).

Und das erstreckt sich über einige tausend Zeilen (welche ich natürlich nicht per Hand sondern mit Hilfe eines Scripts eingefügt habe). Denn es ist halt für jede Abfahrt am WT, SA und SO eine eigene Zeile vorhanden.

Soweit zum Sachverhalt.

[Tom]

Hello,

das haben wir schon mal gelöst und es muss sich im Archiv befinden.
Allerdings war da die Aufgabenstellung festzustellen, ob der nächst kleinere Wert oder der nächst größere Wert näher dran liegt. Wenn man nur den nächstgrößeren finden will, geht das mit einem einfachen

... where wert > $wert order by wert limit 1

Liebe Grüße

Tom vom Berg

[Vinzenz Mai]

Hallo Tom,

Allerdings war da die Aufgabenstellung festzustellen, ob der nächst kleinere Wert oder der nächst größere Wert näher dran liegt. Wenn man nur den nächstgrößeren finden will, geht das mit einem einfachen

... where wert > $wert order by wert limit 1

nein, das gibt den größten Wert, der größer ist als der "Suchwert", nicht den nächst größeren. Unnötige Backticks könnte man in Postings gerne weglassen, die nötige Maskierungsfunktion sollte man hingegen nicht weglassen.

Freundliche Grüße

Vinzenz

[Tom]

Hello,

... where wert > $wert order by wert limit 1

nein, das gibt den größten Wert, der größer ist als der "Suchwert", nicht den nächst größeren. Unnötige Backticks könnte man in Postings gerne weglassen, die nötige Maskierungsfunktion sollte man hingegen nicht weglassen.

Ui... tatsächlich?

Jetzt habe ich doch tatsächlich extra nochmal meinen XAMPP angeschmissen.

select zahl from zahl where zahl > 33 order by zahl limit 1;

funktioniert zu meiner Zufriedenheit.
Es liefert den Datensatz, dessen Feld zahl das nächstgrößere nach 33 ist.

Und welches Escpaping möchtest Du für numerische Größen haben?
Wenn Du meinst, dass man vor dem Einsetzen in die Abfrage

$wert = intval($wert);

durchführen könnte, damit keine SQL-Injection möglich ist, dann hättest Du das gleich hinschreiben können. Also muss da noch ein Geheimnis kommen, das ich nicht kenne. Nun bin ich aber gespannt ;-)

Liebe Grüße

Tom vom Berg

[Frank (no reg)]

Hi,

also ich war auch der Meinung, dass

order by wert`` ein ASCENDING impliziert, d.h. von kleinst nach grösst ... ;)

Cheers, Frank

[Vinzenz Mai]

Hallo

... where wert > $wert order by wert limit 1

nein, das gibt den größten Wert, der größer ist als der "Suchwert", nicht den nächst größeren. Unnötige Backticks könnte man in Postings gerne weglassen, die nötige Maskierungsfunktion sollte man hingegen nicht weglassen.

Ui... tatsächlich?

Nö, natürlich nicht. Aber bitte keine Backticks. Ich habe eine Backtick-Allergie :-)

Freundliche Grüße

Vinzenz

[Tom]

Hello,

[...] Aber bitte keine Backticks. Ich habe eine Backtick-Allergie :-)

Die passen, soweit ich das weiß, auch nur zu MySQL.
Ich habe mir auch lange überlegt, ob ich meine Texte an den entsprechenden Stellen in Backticks packe oder nicht. Auch wenn Du eine Allergie dagegen hast (ich selber eigentlich auch), finde ich doch, dass die Zusammenhänge durch diese kleinen Mücken schneller klar werden. Jedes Wort in Backticks kennzeichent einen Bezeichner aus dem Datenbankumfeld und keinen Befehl. Das war schon immer das, was ich als Anfänger jeglicher Programmiersprachen nie auseinander halten konnte. Und wie ich inzwiwschen weiß, geht das ca. 70 bis 80% der neulinge in einem Kontext so.

Wir sollten also unsere Allergie überwinden, und für die Klarheit arbeiten, auch wenn das nur die "Spielzeugsysteme" so machen. Übrigens finde ich, dass MySQL inzwischen überhaupt nicht mehr zu den Spielzeugsystemen gehört. Es wird langsam ernst!

Liebe Grüße

Tom vom Berg