Dateinamen in Datenbank (Suche)

[truemaster]

Hallo zusammen,

ich würde gerne Datensätze anhand des Namen und der Schlagwörter , die mit Komma getrennt sind suchen.

CREATE TABLE `allg_dokumente` (
  `ID` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `dokument` varchar(255) NOT NULL,
  `datei_format` varchar(5) DEFAULT NULL,
  `datei_groesse` int(11) DEFAULT NULL,
  `stichwoerter` text NOT NULL,
  `aktiv` smallint(6) NOT NULL,
  `benutzerID` int(11) NOT NULL,
  `erfasst` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Mein Ansatz:

FROM allg_dokumente WHERE (name LIKE '%".$_GET['name']."%' OR stichwoerter LIKE '%".$_GET['name']."%')

Die Suche ist aber nicht gut. Wenn ich das Schlagwort Testbaum, test, Peter und nur nach Baum suche, findet er trotzdem den Datensatz.

Habt Ihr eine Idee, wie ich sowas umsetzen kann?

[hausl]

Ja, weil du mit %% und OR suchst.

Ich würde es aber nicht so machen, du hast so eine riesige SQL-Injection Lücke. Besser mit Prepared Statments arbeiten.

[protestix]

Stichwoerter ist bei dir vom Typ Text, wenn du aber niemals mehr als 64 Stichwörter hast, bietet es sich an diese in Set zu speichern. Die Suche ist dann besser und schneller.
Siehe https://dev.mysql.com/doc/refman/8.0/en/set.html

Dort kannst du dann wesentlich schneller auch mit dem LIKE Operator suchen.

Hast du mehr als 64 Stichworte und zudem viele Datensätze, (viel ist vielleicht mehr als 100 000, dann würde ich für diese Art der Suche alle Worte, auch die Namen und sonstigen Begriffe alle in einer extra Tabelle auslagern mit id_referenz auf die jetzige Tabelle. Dabei kommt dann 1 Wort in einen Datensatz. Diese Tabelle ist dann nur für die Suche zuständig, sonst nix.

Du kannst auch nur die Stichorte in eine extra Tabelle auslagern mit einer Referenz zur jetzigen, das wäre eigentlich der Normalfall, wenn man eine Datenbank anlegt, dann hast du diese Problem auch nicht mehr und schneller ist es auch noch.

Wenn du das alles nicht willst oder brauchst kannst du auch weiterhin so suchen nur musst vor dem Wort dann ein Leerzeichen haben. Das Erste Stichwort muss dann aber auch ein Leerzeichen als erstes haben oder ein Komma, weil es sont nicht gefunden wird und da siehst du auch schon die Einschränkungen. Deine bisherige Lösung ist also nur dritte Wahl iund sollte tunlichst vermieden werden..

[truemaster]

Erstaml vielen Dank für Eure Hilfe.


Verstehe ich das richtig?

Bestenfalls 2 Tabellen: Tabelle 1 wie gehabt nur ohne Suchwörter
Tabelle 2 ist nur für die Suchwörter. Jedes Suchwort bekommt eine Zeile. Diese ist verknüpft mit der ID aus der Dateittabelle.

Wenn jetzt die User bei unterschiedlichen Dateien identische Suchwörter eingeben, habe ich aber diese mehrfach in der Datenbank. Wie wird denn das vermieden?

Gerne korrigieren wenn ich flasch liege.

[LudwigBr]

Ich empfehle dir mal diesen Beitrag hier ab dem gesetzten Marker und gegebenenfalls noch die Beispiele darüber: https://de.wikipedia.org/wiki/Kardin...en_Datenmodell

Am besten liest du erst danach hier weiter, dann sollte das auch besser verständlich sein.
Du hast bei den n:m Fall. Der wird in der Regel mit 3 Tabellen abgebildet.
Dokument(id, ...)
Schlagwort(id, ...)
Dokument_Schlagwort(dokument_id, schlagwort_id)

Wenn du nun nach einem Schlagwort suchen möchtest musst du die Tabellen mit JOIN verbinden und mit WHERE nach dem Schlagwort filtern.

[protestix]

Gute Frage wir nähern uns der 3. Normalform, dazu ein Beispiel

Du hast bist jetzt

Tabelle stichworte

id_allg_dokumente | Stichwort
-----------------------------
                1 | Testbaum
                1 | Test
                1 | Peter
                2 | Baumschule
                2 | Peter
                2 | Tannenbaum

Hier fällt nun auf das Peter zwei mal vorkommt, demzufolge kann man das auch auslagern

Tabelle stichworte
id | Stichwort
--------------
 1 | Testbaum
 2 | Test
 3 | Peter
 4 | Baumschule
 5 | Tannenbaum

und die Tabelle die beide Ids zueinander in Zisammenhang bringt, man spricht hier in Fachkreisen gerne von Refernzen

Tabelle stichworte
id | Stichwort
--------------
 1 | Testbaum
 2 | Test
 3 | Peter
 4 | Baumschule
 5 | Tannenbaum

Für die SQL-Abfrage brauchst du also eine Query mit 2 LEFT JOINS, aber das nachzulesen überlasse ich dir als Übung.

Es hat sich übrigens etabliert, dass man gern englische Bezeichner für Variablen, Parameter,Tabellen- und Spaltennamen benutzt, versuch das mal umzusetzen.

[truemaster]

Ist das sinnvoll so?

Ich habe jetzt folgendes gemacht und es klappt offensichtlich auch:

CREATE TABLE `allg_dateien` (
  `ID` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `datei` varchar(255) NOT NULL,
  `datei_format` varchar(5) DEFAULT NULL,
  `datei_groesse` int(11) DEFAULT NULL,
  `stichwoerter` text NOT NULL,
  `aktiv` smallint(6) NOT NULL,
  `benutzerID` int(11) NOT NULL,
  `erfasst` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `allg_dateien_suchwoerter` (
  `ID` int(11) NOT NULL,
  `suchwort` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `allg_dateien_suchwoerter_kombi` (
  `dateiID` int(11) NOT NULL,
  `suchwortID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Abfrage nach Suchwörtern und Dateibezeichnung

SELECT
allg_dateien.ID AS ID,
allg_dateien.name,
allg_dateien.datei,
allg_dateien.datei_format,
allg_dateien.datei_groesse,
DATE_FORMAT(allg_dateien.erfasst, '%d.%m.%Y') AS erfasst
FROM allg_dateien_suchwoerter
LEFT JOIN allg_dateien_suchwoerter_kombi ON allg_dateien_suchwoerter_kombi.suchwortID = allg_dateien_suchwoerter.ID
LEFT JOIN allg_dateien ON allg_dateien.ID = allg_dateien_suchwoerter_kombi.dateiID
WHERE allg_dateien_suchwoerter.suchwort = :suche XOR allg_dateien.name = :suche
GROUP BY allg_dateien.ID

Und ja, ich sollte alles auf Englisch schreiben.

[protestix]

Seht doch gut aus, prima, dennoch sind einge Verbesserungen dringend anzuraten

ENGINE=InnoDB DEFAULT CHARSET=latin1;
Bei der ersten Tabelle hast du den richtichen Zeichesatz ausgewählt, bitte vergesse das es latin1 jemals gab, alles in UTF8.

`allg_dateien_suchwoerter`
Warum nicht einfach halten und die Tabelle suchworte nennen? Man muss Dinge nicht unnötig verkomplizieren.
KISS Prinzip - Keep it stupid simple

`allg_dateien_suchwoerter_kombi`
Hier weiss schon mal niemand was sich dahinter verbirgt, mache das nicht,
Schreib lieber
suchworte_id_zu_allg_dokumente_id

[LudwigBr]

Ergänzend zu den Namengebungshinweisen von protetix noch:
Ich empfehle Fremdschlüssel immer "Tabellenname+id" zu nennen.

Wennd u die Tabellen nicht noch umbenennst wäre das also:

 CREATE TABLE `allg_dateien_suchwoerter_kombi` (  
`allg_dateien_id` int(11) NOT NULL,  
`allg_dateien_suchwoerter_id` int(11) NOT NULL
)

Das macht die Zuordnung schlichtweg einfacher, insbesondere wenns (vielleicht später) mal noch andere ähnliche Tabellennamen gibt.

[protestix]

Noch ein Nachtrag zu GROUP BY allg_dateien.ID
Wenn du gruppierst, müssen alle Spalten, die keine Aggregatfunktion haben, mit im GROUP BY aufgeführt werden, leider liefert Mysql da standardmässig keine Fehlermeldung, andere DBMS schon.

Hier wird dir das deutlicher erklärt: https://riptutorial.com/de/mysql/top...enthalten----#

`stichwoerter` text NOT NULL,
Das kann dann auch weg.

[truemaster]

Vielen Dank. Ihr habt mir sehr geholfen und ich habe wieder viel dazugelernt. DANKE!