Fragen zur Volltextsuche

[Max]

Hallo zusammen!
Ich versuche gerade eine Volltext-Suche über PHP zu realisieren.
Über 10 Input-Felder können Zutaten eingegeben werden, die dann mit den Zutaten von gespeicherten Rezepten abgeglichen werden.
Das funktioniert auch soweit:

$abfrage = "SELECT * FROM rezepte
WHERE MATCH zutat1,zutat2,zutat3,zutat4,zutat5,zutat6,zutat7,zutat8,zutat9,zutat10)
AGAINST ('$zutat1, $zutat2, $zutat3, $zutat4, $zutat5, $zutat6, $zutat7, $zutat8, $zutat9, $zutat10' IN BOOLEAN MODE)";

Allerdings möchte ich keine Boolsche-Volltextsuche verwenden, weil dabei nicht automatisch nach absteigender Relevanz sortiert wird. Sobald ich aber "IN BOOLEAN MODE" aus der Abfrage entferne, bekomme ich kein Suchergebnis mehr.
Dies könnte, soweit ich das verstanden habe, daran liegen, dass ich für die Spalten keinen gemeinsamen FULLTEXT-Index erstellt habe und hier weiß ich nicht weiter:
Legt man in phpMyAdmin einen solchen Index an, muss man jeweils rechts neben dem Feld, das indiziert werden soll, eine Größe eingeben.
Um was für eine Größe handelt es sich dabei?

Eine weitere Unsicherheit habe ich hier:
In der deutschen Mysql-Dokumentation steht, dass sich FULLTEXT-Indices nur für CHAR-, VARCHAR- und TEXT-Spalten erstellen lassen. Gehören Felder vom Typ TINYTEXT dann nicht dazu?

Vielen Dank schonmal!

[Maik W. aus E.]

Tach auch Max,

$abfrage = "SELECT * FROM rezepte
WHERE MATCH zutat1,zutat2,zutat3,zutat4,zutat5,zutat6,zutat7,zutat8,zutat9,zutat10)
AGAINST ('$zutat1, $zutat2, $zutat3, $zutat4, $zutat5, $zutat6, $zutat7, $zutat8, $zutat9, $zutat10' IN BOOLEAN MODE)";

Da scheint die Datenhaltung aber nicht so brillant definiert worden zu sein. Redundantes Mehl kommt sehr häufig vor... Was machst Du denn mit Rezepten, die mehr als zehn Zutaten haben? Bei weniger Zutaten schleppst Du viel leere Felder mit.

Allerdings möchte ich keine Boolsche-Volltextsuche verwenden, weil dabei nicht automatisch nach absteigender Relevanz sortiert wird. Sobald ich aber "IN BOOLEAN MODE" aus der Abfrage entferne, bekomme ich kein Suchergebnis mehr.

Dies könnte, soweit ich das verstanden habe, daran liegen, dass ich für die Spalten keinen gemeinsamen FULLTEXT-Index erstellt habe

Genau!

und hier weiß ich nicht weiter:
Legt man in phpMyAdmin einen solchen Index an, muss man jeweils rechts neben dem Feld, das indiziert werden soll, eine Größe eingeben.
Um was für eine Größe handelt es sich dabei?

Es handelt sich um die Index-Größe (Da war doch mal was), was aber nicht zwingend ist. Will sagen: Setze Deinen Index und gut ist.

Eine weitere Unsicherheit habe ich hier:
In der deutschen Mysql-Dokumentation steht, dass sich FULLTEXT-Indices nur für CHAR-, VARCHAR- und TEXT-Spalten erstellen lassen. Gehören Felder vom Typ TINYTEXT dann nicht dazu?

Offensichtlich

http://www.gruss-aus-essen.de

Maik

[Max]

Danke für die Antwort erstmal!
Ich habe jetzt mal einen FULLTEXT-Index angelegt und den Feldern den Typ "Text" statt "Tinytext" zugewiesen. Allerdings bekomme ich trotzdem die Fehlermeldung "Can't find FULLTEXT index matching the column list".

Mein PHP-Quelltext sieht folgendermaßen aus:

<?php
error_reporting(E_ALL); // alle Fehler anzeigen

@mysql_connect('localhost:8889', 'root', 'root') or die ("Keine Verbindung moeglich");
    mysql_select_db("test") or die ("Die Datenbank existiert nicht");

$name    = $_POST["name"];
    $zutat1  = $_POST["zutat1"];
    $zutat2  = $_POST["zutat2"];
    $zutat3  = $_POST["zutat3"];
    $zutat4  = $_POST["zutat4"];
    $zutat5  = $_POST["zutat5"];
    $zutat6  = $_POST["zutat6"];
    $zutat7  = $_POST["zutat7"];
    $zutat8  = $_POST["zutat8"];
    $zutat9  = $_POST["zutat9"];
    $zutat10 = $_POST["zutat10"];
    $text    = $_POST["text"];
    $zeit    = $_POST["zeit"];

$abfrage = "SELECT * FROM rezepte
                WHERE MATCH (zutat1,zutat2,zutat3,zutat4,zutat5,zutat6,zutat7,zutat8,zutat9,zutat10)
                AGAINST ('$zutat1*, $zutat2*, $zutat3*, $zutat4*, $zutat5*, $zutat6*, $zutat7*, $zutat8*, $zutat9*, $zutat10*')";

$resultat = mysql_query($abfrage) or die (mysql_error());  // -- FEHLERMELDUNG!! --

echo "<font size=1><br />";
    echo "<table border=none bgcolor=#d3d3d3>";

while ($row = mysql_fetch_array($resultat)) // Sämtliche Datensätze durchlaufen und ausgeben
    {
      extract ($row);      // Mithilfe der Feldbezeichnungen auf Feldinhalte zugreifen und diese ausgeben
      echo "<tr>
              <td width=250 height=30><a href=#><b>$name</b></a></td>
             <td width=100 height=30>$zutat1</td>
             <td width=100 height=30>$zutat2</td>
             <td width=100 height=30>$zutat3</td>
             <td width=100 height=30>$zutat4</td>
             <td width=100 height=30>$zutat5</td>
             <td width=100 height=30>$zutat6</td>
             <td width=100 height=30>$zutat7</td>
             <td width=100 height=30>$zutat8</td>
             <td width=100 height=30>$zutat9</td>
             <td width=100 height=30>$zutat10</td>
             <td width=250 height=30>$text</td>
             <td width=50  height=30>$zeit</td>
        </tr>";
    }

echo "</table>";
    echo "</font>";
?>

Das mySQL-CREATE TABLE sieht so aus:

CREATE TABLE rezepte (
  ID int(11) NOT NULL auto_increment,
  name text character set utf8 NOT NULL,
  zutat1 text character set utf8 NOT NULL,
  zutat2 text character set utf8 NOT NULL,
  zutat3 text character set utf8 NOT NULL,
  zutat4 text character set utf8 NOT NULL,
  zutat5 text character set utf8 NOT NULL,
  zutat6 text character set utf8 NOT NULL,
  zutat7 text character set utf8 NOT NULL,
  zutat8 text character set utf8 NOT NULL,
  zutat9 text character set utf8 NOT NULL,
  zutat10 text character set utf8 NOT NULL,
  text text character set utf8 NOT NULL,
  zeit smallint(11) unsigned NOT NULL,
  PRIMARY KEY  (ID),
  FULLTEXT KEY zutaten (name,zutat1,zutat2,zutat3,zutat4,zutat5,zutat6,zutat7,zutat8,zutat9,zutat10)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
]

oder so in phpMyAdmin:

Was machst Du denn mit Rezepten, die mehr als zehn Zutaten haben? Bei weniger »» Zutaten schleppst Du viel leere Felder mit.

Ich hatte eigentlich vor später noch mehr Felder hinzuzufügen und dachte, dass sich leere Felder nicht auf die Performance der Suche auswirken. Dein Vorschlag wäre, alle Zutaten eines Rezeptes in ein Tabellenfeld zu speichern?

Nochmals danke für jede Hilfe!

[Maik W. aus E.]

Tach auch Max,

Danke für die Antwort erstmal!
Ich habe jetzt mal einen FULLTEXT-Index angelegt und den Feldern den Typ "Text" statt "Tinytext" zugewiesen. Allerdings bekomme ich trotzdem die Fehlermeldung "Can't find FULLTEXT index matching the column list".

Das weist darauf hin, daß der Index und das "MATCH AGAINST" nicht übereinstimmen

Mein PHP-Quelltext sieht folgendermaßen aus:

Nur das SQL-Statement ist -eigentlich- relevant...

$name    = $_POST["name"];
    $zutat1  = $_POST["zutat1"];
    $zutat2  = $_POST["zutat2"];

[...]
Das kannst Du Dir sparen, verwende einfach die $_POST-Variablen.

$abfrage = "SELECT * FROM rezepte
                WHERE MATCH (zutat1,zutat2,zutat3,zutat4,zutat5,zutat6,zutat7,zutat8,zutat9,zutat10)
                AGAINST ('$zutat1*, $zutat2*, $zutat3*, $zutat4*, $zutat5*, $zutat6*, $zutat7*, $zutat8*, $zutat9*, $zutat10*')";

Wo kommen diese Sterne her? Was soll das?

Das mySQL-CREATE TABLE sieht so aus:

CREATE TABLE rezepte (
  ID int(11) NOT NULL auto_increment,
  name text character set utf8 NOT NULL,
  zutat1 text character set utf8 NOT NULL,
  zutat2 text character set utf8 NOT NULL,
  zutat3 text character set utf8 NOT NULL,
  zutat4 text character set utf8 NOT NULL,
  zutat5 text character set utf8 NOT NULL,
  zutat6 text character set utf8 NOT NULL,
  zutat7 text character set utf8 NOT NULL,
  zutat8 text character set utf8 NOT NULL,
  zutat9 text character set utf8 NOT NULL,
  zutat10 text character set utf8 NOT NULL,
  text text character set utf8 NOT NULL,
  zeit smallint(11) unsigned NOT NULL,
  PRIMARY KEY  (ID),
  FULLTEXT KEY zutaten (name,zutat1,zutat2,zutat3,zutat4,zutat5,zutat6,zutat7,zutat8,zutat9,zutat10)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
]

Und hier ist der Unterschied! Im Index ist 'name' mit drin. Fang doch erstmal klein an im Sinne von

  
SELECT name FROM rezepte WHERE MATCH  
     (zutat1, zutat2, zutat3)  
     AGAINST ('$POST[such_zutat]')  
;  

Was machst Du denn mit Rezepten, die mehr als zehn Zutaten haben? Bei weniger »» Zutaten schleppst Du viel leere Felder mit.
Ich hatte eigentlich vor später noch mehr Felder hinzuzufügen und dachte, dass sich leere Felder nicht auf die Performance der Suche auswirken.
Dein Vorschlag wäre, alle Zutaten eines Rezeptes in ein Tabellenfeld zu speichern?

Oh nein, ich würde die Zutaten in eine Tabelle tun, die Rezepte in eine andere und die beiden über eine Kreuztabelle verknüpfen. Dann kann man x-beliebig viele Zutaten mit x-beliebig vielen Rezepten performant n:m miteinander verknüpfen...

Nochmals danke für jede Hilfe!

Gern geschehen.

http://www.gruss-aus-essen.de

Maik

[Max]

Es funktioniert jetzt endlich (das "name" im Index war die Ursache, danke!). Zwar leidet die Suche immer noch ein wenig unter den Eigenschaften der MySQL-Volltextsuche (Nachteil im Standard-Modus: keine Sortierung nach Relevanz, Nachteil im Boolschen-Modus: keine Wildcards), aber insgesamt ist sie jetzt zu gebrauchen.

Wo kommen diese Sterne her? Was soll das?

Die Sternchen lassen sich als Wildcard benutzen. Ich dachte bisher auch, dass man "%" benutzt. Damit funktionert es aber nicht.

Oh nein, ich würde die Zutaten in eine Tabelle tun, die Rezepte in eine andere und die beiden über eine Kreuztabelle verknüpfen. Dann kann man x-beliebig viele Zutaten mit x-beliebig vielen Rezepten performant n:m miteinander verknüpfen...

Ich werde mich mal damit auseinandersetzen. Danke für den Tipp!

[Maik W. aus E.]

Tach auch Max,

Es funktioniert jetzt endlich (das "name" im Index war die Ursache, danke!).

Dann wird es Zeit für den zweiten Schritt, wie Jonathan schon hingewiesen hat, Dich gegen SQL-Injection abzusichern.

Ich werde mich mal damit auseinandersetzen. Danke für den Tipp!

Mach das, denn das macht Deine DB erst relational...

http://www.gruss-aus-essen.de

Maik

[Max]

Mach das, denn das macht Deine DB erst relational...

Hallo nochmal Maik W. aus E.!
Ich habe mich jetzt mit Verknüpfungen von Tabellen auseinandergesetzt.

Meine Tabelle könnte also z.B. in zwei "Relationen" aufgeteilt werden:

REZEPTE                   ZUTATEN
+----+-----------+      +---------+--------+
| ID |  Name     |      |Rezept_ID| Zutat  |
+----+-----------+      +---------+--------+
|  5 | Wurstbrot |      |    5    |  Wurst |
+----+-----------+      +---------+--------+
                        |    5    |  Brot  |
                        +---------+--------+
                        |    5    | Butter |
                        +---------+--------+

Die Suche müsste dann aus zwei Abfragen bestehen:

Zuerst wird überprüft welche Zutaten mit den Such-Eingaben (aus den Input-Feldern des Formulars) übereinstimmen und dann deren Rezept_ID geliefert.
$abfrage1 = " SELECT Rezept_ID.Zutaten FROM Zutaten
              WHERE MATCH ($_POST['zutat1'], $_POST['zutat2'], $_POST['zutat...'])
              AGAINST (Zutat)";

Dann werden alle Rezepte herausgesucht, deren ID mit der Rezept_ID der Zutaten übereinstimmt.
$abfrage2 = " SELECT Rezept.Name From Rezepte
              WHERE Rezepte.ID = Rezept_ID.Zutaten";

Wäre sehr nett von dir, wenn du mir ein kurzes Feedback geben könntest. Ist das so ungefähr sinnvoll?
Leidet unter dieser doppelten Abfrage nicht die Performance der Suche?

Gruß nach E.,
Max

[Jonathan]

Hallo Max,

$name    = $_POST["name"];
    $zutat1  = $_POST["zutat1"];
    $zutat2  = $_POST["zutat2"];
    $zutat3  = $_POST["zutat3"];
    $zutat4  = $_POST["zutat4"];
    $zutat5  = $_POST["zutat5"];
    $zutat6  = $_POST["zutat6"];
    $zutat7  = $_POST["zutat7"];
    $zutat8  = $_POST["zutat8"];
    $zutat9  = $_POST["zutat9"];
    $zutat10 = $_POST["zutat10"];
    $text    = $_POST["text"];
    $zeit    = $_POST["zeit"];

$abfrage = "SELECT * FROM rezepte
                WHERE MATCH (zutat1,zutat2,zutat3,zutat4,zutat5,zutat6,zutat7,zutat8,zutat9,zutat10)
                AGAINST ('$zutat1*, $zutat2*, $zutat3*, $zutat4*, $zutat5*, $zutat6*, $zutat7*, $zutat8*, $zutat9*, $zutat10*')";

Dein Code ist anfällig gegen SQL injections und sollte verbessert werden.

Jonathan

[Max]

Dein Code ist anfällig gegen SQL injections und sollte verbessert werden.

Habe ihn (wie in "http://de.wikipedia.org/wiki/SQL-Injektion" vorgeschlagen) abgeändert.

Ist dieser Teil auch unsicher?

if ($_POST[name] != "" AND $_POST[name] != "Rezeptname" AND $_POST[zutat1] != "" AND $_POST[zutat1] != "- Hier Zutaten eintragen")  // Speichern in der Datenbank
 {
  mysql_query("
   INSERT INTO rezepte
    (name, zutat1, zutat2, zutat3, zutat4, zutat5, zutat6, zutat7, zutat8, zutat9, zutat10, text, zeit)
   VALUES
    (
     '$_POST[name]',
     '$_POST[zutat1]',
     '$_POST[zutat2]',
     '$_POST[zutat3]',
     '$_POST[zutat4]',
     '$_POST[zutat5]',
     '$_POST[zutat6]',
     '$_POST[zutat7]',
     '$_POST[zutat8]',
     '$_POST[zutat9]',
     '$_POST[zutat10]',
     '$_POST[text]',
     '$_POST[zeit]'
    )"
  );
 }

Vielen Dank!

[Jonathan]

Hallo Max,

Ist dieser Teil auch unsicher?

Ja, du hast ja auch nicht viel verändert, außer ein paar werte zu überprüfen und das Query anders anzuordnen.

Wichtig ist, dass du _alle_ $_POST[]s und andere Daten, die aus unsicheren Quellen (nämlich vom Benutzer) kommen auf Plausibilität überprüfst, bzw. unerwünschte Zeichen maskierst.

Für dein SQL-Query solltest du alle $_POST-Werte vorher durch mysql_real_escape_string() jagen.

Also anstatt

mysql_query("
   INSERT INTO rezepte
    (name, zutat1, zutat2, zutat3, zutat4, zutat5, zutat6, zutat7, zutat8, zutat9, zutat10, text, zeit)
   VALUES
    (
     '$_POST[name]',
[...]
    )"

folgendes machen:

mysql_query("
   INSERT INTO rezepte
    (name, zutat1, zutat2, zutat3, zutat4, zutat5, zutat6, zutat7, zutat8, zutat9, zutat10, text, zeit)
   VALUES
    (
     '" . mysql_real_escape_string($_POST[name]') . ",
[...]
    )"

Natürlich könnte man überlegen, die Werte der Übersicht halbder in Variablen zwischenzuspeichern oder eine Hilfsfunktion zum zusammensetzen zu verwenden.

Jonathan

[Max]

Hallo nochmal!

Ja, du hast ja auch nicht viel verändert, außer ein paar werte zu überprüfen und das Query anders anzuordnen.

Tut mir leid, da hab ich mich undeutlich ausgedrückt. Ich wollte eigentlich nur wissen, ob man mysql_real_escape_string() auch bei INSERT INTO anwenden kann (bei SELECT WHERE MATCH AGAINST hatte ich die Änderungen schon vorgenommen), aber das hast du mir ja jetzt auch beantwortet.
Jetzt müsste eigentlich alles sicher sein!

Danke für deine Bemühungen!