Suchfunktion MySql DB PHP

[Förster]

Hallo Freunde der Nacht, ich versuche zur Zeit ein autocompletefeld mit Jquery zu basteln, bzw. habe es schon geschafft. Nun habe ich aber Probleme bei dem "Suchallgorhytmus".

In meiner Tabelle in der DB ist Beispielsweise so aufgebaut:

ID | Vorname | Nachname | Ort

Dort sind hinterlegt

1 | Oliver | Hermann | Hintertupfingen

2 | Olivia | Brunse | Hintertupfingen

3 | Oliver | Kerner | Dortmund

Nun "schicke" ich den Suchbegriff per Ajax an die Datei search.php. Als Beispiel Suche ich nach >> Oli Hinter <<

Meine search.php sieht folgendermaßen aus (ohne die ganzen Sicherheitschecks):

if(isset($_GET['term'])){
      $term = trim($_GET['term']);
      $term = ltrim($term, "\0x20"); 
      
      $searchTerms = explode(" ",$term);
      
      foreach($searchTerms as $searchTerm){
        $sql[] = '\'%'.$searchTerm.'%\'';
      }
      
       
      $query = $mysqli->query("SELECT id, vorname, nachname, ort FROM table WHERE vorname LIKE ".implode(" OR ", $sql)." OR nachname LIKE ".implode(" OR ", $sql)." OR ort LIKE ".implode(" OR ", $sql)." ORDER BY name ASC");

Nun bekomme ich bei dieser Anfrage natürlich alle angezeigt, da ja alle im ersten Kriterium gefunden werden. Ich möchte nun aber natürlich nur Id 1 & 2 als Ergebniss bekommen. Geht dieses nur durch Verschachtelung des Where-Parts der Abfrage in mehrere AND Teile oder gibt es da einen Trick?

Hoffe mich einigermaßen vernünftig erklärt zu haben.

Mit freundlichen Grüßen Der Förster

[dedlfix]

Tach!

if(isset($_GET['term'])){
      $term = trim($_GET['term']);
      $term = ltrim($term, "\0x20"); 

trim() trimmt auch die Leerzeichen weg. Aber das was du da im ltrim() geschrieben hast ist eine Kombination aus NUL-Byte einem x und den Ziffern 2 und 0. Ich glaube ja nicht, dass du diese 4 Zeichen am Anfang entfernen möchtest. \x20 wäre ein Leerzeichen. Aber das hättest du auch direkt als Leerzeichen schreiben können - was aber auch wenig sinnvoll ist, weil das ja bereits vom trim() eliminiert wurde.

      $searchTerms = explode(" ",$term);
      
      foreach($searchTerms as $searchTerm){
        $sql[] = '\'%'.$searchTerm.'%\'';
      }
      
       
      $query = $mysqli->query("SELECT id, vorname, nachname, ort FROM table WHERE vorname LIKE ".implode(" OR ", $sql)." OR nachname LIKE ".implode(" OR ", $sql)." OR ort LIKE ".implode(" OR ", $sql)." ORDER BY name ASC");`

Lass dir mal das fertige Statement ausgeben. Das ist zwar (oftmals) syntaktisch richtig, aber nicht das was du eigentlich haben möchtest. Das wird fast immer alle Ergebnisse finden, weil die einzelnen Werte die durch das implode() mit dem OR drin entstehen, die Bedingung meistens wahr werden lassen.

x LIKE a OR b evaluiert zu (x LIKE a) OR (b). Es gibt keine Kombination innerhalb eines LIKE. Zum Kombinieren musst du mehrere einzelne Bedingungen formulieren à la (x LIKE a) OR (x LIKE b). (Die Klammern können wegbleiben, die dienen nur zur Veranschaulichung der Teilausdrücke.)

Außerdem kann man da SQL-Injection betreiben, weil du die Eingabewerte unmaskiert ins Statement bringst.

dedlfix.

[Förster]

Hi und Danke für Deine Antwort

if(isset($_GET['term'])){
      $term = trim($_GET['term']);
      $term = ltrim($term, "\0x20"); 

Oh, \0x20 sollte es sein, da die ID bei im System mit 0 auf 5-stellig aufgefüllt wird und ich mit der Angabe 0 irgendwie nicht das Ergebnis bekam.

Außerdem kann man da SQL-Injection betreiben, weil du die Eingabewerte unmaskiert ins Statement bringst.

Die Eingabewerte werden doch in der foreach maskiert. Vorher prüße ich die einegehenden Werte auch noch mit verschiedenen Regex auf Injection, habe dieses hier nur rausgelassen um den thread nicht aufzublähen.

Mir geht es halt darum das nur nicht diese 3 Felder durchsucht werden können, sondern insgesamt 7, und dann kommt da ja mit der Verschachtelung eine riesige Abfrage raus. Ich weiß halt nicht in welcher reihenfolge es eingegeben wird, ob der nutzer nun nach Oliver Hermann hintertupfingen sucht oder nach Hermann oliver hintertupfingen.

Gibt es da denn nicht eine elegantere Lösung?

Danke schonmal für die Hilfe 😀

[bobby]

Moin,

Ich denke schon dass es eleganter geht. Indem du die 3 Feldwerte über CONCAT verbindest. Und die Begriffe einzeln gegen diesen verbundenen String prüfst.

Habs jetzt nicht getestet. Sollte aber funktionieren.

Gruß Bobby

[dedlfix]

Tach!

if(isset($_GET['term'])){
      $term = trim($_GET['term']);
      $term = ltrim($term, "\0x20"); 

Oh, \0x20 sollte es sein, da die ID bei im System mit 0 auf 5-stellig aufgefüllt wird und ich mit der Angabe 0 irgendwie nicht das Ergebnis bekam.

\0x20 ist \0x20, und ist nach wie vor keine 0 und keine syntaktisch korrekte Escape-Sequence. Wenn bei dir eine 0 direkt angegeben nicht funktioniert, dann hast du irgendein anderes Problem. "\x30" und "0" jedenfalls muss dasselbe Ergebnis bringen.

Außerdem kann man da SQL-Injection betreiben, weil du die Eingabewerte unmaskiert ins Statement bringst.

Die Eingabewerte werden doch in der foreach maskiert.

Nein, da werden sie nur quotiert (in Anführungszeichen gesetzt) und mit den für das LIKE notwendigen Jokerzeichen ergänzt. Eine Entschärfung von in der Eingabe enthaltenen Anführungszeichen findet nicht statt. Das wäre das Maskieren, das Verstecken der Anführungszeichen vor dem Parser, damit dieser da kein String-Ende erkennt.

Vorher prüße ich die einegehenden Werte auch noch mit verschiedenen Regex auf Injection, habe dieses hier nur rausgelassen um den thread nicht aufzublähen.

Das ist nicht notwendig. Eigene Prüfroutinen brauchst und solltest du nur für fachliche Anforderungen vornehmen. Für eine syntaktisch korrekte Aufbereitung (inklusive Injection-Verhinderung) gibt es eine entsprechende MySQL-Funktion: mysqli::real_escape_string().

Mir geht es halt darum das nur nicht diese 3 Felder durchsucht werden können, sondern insgesamt 7, und dann kommt da ja mit der Verschachtelung eine riesige Abfrage raus. Ich weiß halt nicht in welcher reihenfolge es eingegeben wird, ob der nutzer nun nach Oliver Hermann hintertupfingen sucht oder nach Hermann oliver hintertupfingen.

Erstmal solltest du eine funktionierende Lösung notieren, die deine gewünschte Aufgabe in die entsprechende SQL-Syntax umsetzt und nicht in eine nicht funktionierende Phantasie-Syntax. Und dann ist es doch im Prinzip egal, wie lang das Statement wird. Du schreibst das für ein Feld richtig und den Rest macht eine Schleife.

Gibt es da denn nicht eine elegantere Lösung?

Die können wir dann suchen, wenn du eine funktionierende hast. Es gibt da zwei Möglichkeiten. Die eine ist, die Suchbegriffe auf jedes Feld einzeln anzuwenden, die andere ist, ein großes Feld zu erstellen und darin zu suchen. Keine gescheite Lösung wäre allerdings, im SQL-Statement die Felder zu einem großen String zu konkatenieren, und die Suche darauf zu fahren, weil dann immer mit Full Table Scann gesucht werden muss, ohne eine Index zu haben.

Eine Full-Text-Suche geht auch, aber dann bist du meines Wissens daraufhin eingeschränkt, entweder keine Jokerzeichen oder nur den * am Wortende zur Verfügung zu haben.

dedlfix.

[pl]

Auch MySQL bietet die Möglichkeit einer Indizierung für die Volltextsuche und stellt entsprechende Funktionen zur Verfügung. Lies Dir das mal durch und guck mal ob das zu Deiner Aufgabenstellung passt. MfG

[bobby]

Moin,

Auch über mehrere Tabellen? Du sprichst sicher match() against() an. Funktioniert das nicht nur über eine Tabelle?

Gruß Bobby

[pl]

Auch über mehrere Tabellen? Du sprichst sicher match() against() an. Funktioniert das nicht nur über eine Tabelle?

Es ist egal wieviele Tabellen ein Statement verbindet. Entscheidend ist der Index auf dem jeweiligen Feld. MfG

[dedlfix]

Tach!

Auch über mehrere Tabellen? Du sprichst sicher match() against() an. Funktioniert das nicht nur über eine Tabelle?

Du kannst kein Index-Feld über mehrere Tabellen anlegen. Aber innerhalb einer Tabelle ist es möglich, einen FULLTEXT-Index über mehrere Felder zu legen und die Suche darin mit einfachem Aufzählen der Felder zu notieren. Wenn du mehrere Fulltext-Indexe in mehreren Tabellen hast, musst du die Suchbedingung für jede Tabelle einzeln formulieren. Alternativ kannst du eine redundante Datenhaltung erstellen, indem du die Suchfelder zusätzlich zu den relationalen Tabellen in eine Tabelle nur für die Suche kopierst.

dedlfix.

[pl]

Also ich würde im Zweifelsfall die Dokumentation studieren und ggf. mein DB-Design so anpassen, dass ich zu einer Lösung komme. Extra für die Suche ein View anzulegen wäre auch noch eine Variante die zu prüfen ist.

Man sollte auch daran denken, dass ein Feintuning, was gerade für eine Volltextsuche wichtig ist, seitens MySQL Adminrechte erfordert und das ist nicht immer möglich.

MfG

[bobby]

Moin,

Siehste... deshalb ist die match () against () lösung auch evtl. Für den OP nicht die zielführende.

Gruß Bobby