Frage zur Zusammengesetzen Abfrage über mehrere Felder

[hawkeye78]

Hallo,

ich bastele im moment als privates Projekt an einer Musik-Datenbank zur Verwaltung meiner CD-Sammelung. Nun bin ich allerdings bei der Implementierung beim Thema "Suchen in der Datenbank" angelagt, und stehe nun vor dem Problem das ich nicht weiß wie ich dieses möglichst elegant lösen kann.

Ich habe mir für den PHP / HTML Teil überlegt das ich zwei Sucharten anbieten möchte, zum einen eine Ausführliche Suche wo die Eingabe für die unterschiedlichen Datenarten (Künstlername, Albumtitel, genre etc.) klar getrennt ist und eine Art Schnellsuche die nur die Felder für den Künstlernamen und Album durchsucht.

Nun stehe ich allerdings vor dem Problem das ich mir nicht wirklich sicher bin wie ich mit minimaler Komplexität die schnellsuche implementieren kann. Da Vorname, Nachname (des Künstlers) sowie Albumtitel in 3 getrennten Feldern stehen, befürchte ich im Moment das ich die Eingabe in das Suchfeld zerlegen muß und alle Permutationen von möglichen Eingaben testen muß, habe ich nun die Hoffnung das es vielleicht etwas wie einen Hash-Wert, einen Index oder sonst etwas in der Art gibt, was die Anzahl der WHERE Bedingungen etwas reduziert. Ich habe bereits heraus gefunden das es so etwas wie CONCAT für MySQL gibt, aber ich bin mir nicht sicher ob mir das in diesem Fall wirklich weiterhilft...

Wenn sich wirklich keine andere möglichkeit findet, werde ich wohl genötigt sein es so zu implementieren, aber ich wäre sehr dankbar wenn mir vielleicht jemand eine existierende Vereinfachung zeigen könnte.

Viele Grüsse

Dan

[flashpixx]

Hallo,

nur eine Frage zum Verständnis:

Sieht dann die Schnellabfrage bei Dir so aus "select * from tabelle where künster=$kuenstler or album=$album"

und für die erweiterte dann eben mehrere Bedingungen?

Evtl könnte Dir da die ADODB (ADOdb Database Abstraction Library for PHP (and Python) for MySQL, PostgreSQL, Microsoft SQL Server, Oracle, Firebird, Interbase, LDAP, Access, VFP, DB2 and many other databases) etwas helfen, damit ginge dann:

if ($schnell)

   $data = array("kuenstler" => $kuenstler, "album" => $album);

else

   $data = array( mehr inhalt );


$sql->query("select * from tabelle where ".implode("like %?% or", array_keys($data)), array_values($data));

mit der Hoffnung dass ich es richtig verstanden habe

HTH Phil

Bearbeitet 10. September 2008 von flashpixx
Fehler im Code

[hawkeye78]

Hallo Phil,

erst einmal Danke für deine Antwort, aber meine Anfrage schaut im moment so aus:

SELECT K.Vorname, K.Nachname, A.Titel

FROM Kuenstler K, Album A

WHERE K.Album_ID=A.Album_ID AND....

Ich benutze also zwei Tabellen eine für die Künstler (und diese dann auch wieder aufgeteilt in Vor- und Nachname) und eine für das Album.

Ich habe allerdings bereits ein bißchen im Netz gestöbert und bin dabei auf eine Seite gestossen in der erklärt wird wie man eine Volltextsuche in MySQL benutze. Aus diesem Grund habe ich mir überelgt falls mir gar nix einfällt, lege ich einen Index auf den Albumtitel, sowie auf den Namen des Künstlers und führe dann zwei Abfragen mit MATCH...AGAINST aus und füge diese beiden zusammen....Ich bin mir zwar nicht sicher ob es eine schönere Lösung gibt, aber im moment erachte ich diese als sinnvoller als alle Felder aus einander zu nehmen und für die einzelnen Felder die Permutationen durch zu probieren. Aber vielleicht gibt es eine noch bessere Lösung ich wäre über weitere Anregungen / Vorschläge auf jeden Fall sehr dankbar.

Viele Grüsse

Dan

[flashpixx]

Ich benutze also zwei Tabellen eine für die Künstler (und diese dann auch wieder aufgeteilt in Vor- und Nachname) und eine für das Album.

Was hat das jetzt damit zu tun? Wie komplex Deine Abfrage ist, spielt doch keine Rolle, es geht doch hier um die Where-Bedingung.

Mir ist noch nicht so ganz klar "wie" Du suchen willst. Also eine Volltextsuche auf Namen, Album usw?

Phil

[hawkeye78]

entschuldige bitte dann haben wir an einander vorbei geredet, ich hatte deine Frage so interpretiert das du wissen möchtest wie meine Daten strukturiert sind.

Aber um nun die Frage wirklich zu beantworten, ja ich wollte eine Volltextsuche implementieren, also das ich im ideal fall so etwas eingebe wie:

Mark Knopfler Sailing to Philadelphia

und er mir im ideal fall wirklich nur das eine Album findet.

Dan

[flashpixx]

Hallo,

ah okay ;-)

Also ich weiß jetzt nicht mit welchem DBMS du arbeitest, aber ich habe mal vor Ewigkeiten für Postgres eine auf Fuzzy Logik basierte Volltextsuche gesehen. Wenn Du dich auf das DBMS noch nicht 100%ig festgelegt hast, dann würde ich das empfehlen.

Für die Anbindung kann ich von PHP nur wirklich die ADODB empfehlen, denn die konkrete Umsetzung auf den Datenbankspezifischen SQL Syntax macht dann die ADODB für dich.

Ich müsste aber bei Interesse noch einmal genauer nachsehen. Wie die Volltextsuche unter mySQL ist weiß ich nicht. Hier in meinem Fall ging es um Longtext Felder, in denen eine komplette Suche bis runter auf einzelne k-Tupel möglich sein musste

Phil

[hawkeye78]

Moin,

ich hab nun mal zwei Abfragen zusammen geschustert die beide für sich auch wunderbar funktionieren bloß wenn ich aus beiden die Schnittmengebilden möchte, behauptet phpmyadmin, das ich einen Fehler in meiner Syntax in der Nähe vom INTERSECT hätte.

Die Abfrage schaut im moment so aus

 SELECT DISTINCT A.Album_ID AS ID, A.Titel AS Titel, K.Nachname AS Name, K.Vorname AS Vorname

FROM Album A, Kuenstler K, Track T, CD C, Art S, Genre G

WHERE S.show_Interpret =0

AND A.Album_ID = C.Album_ID

AND C.CD_ID = T.CD_ID

AND T.Kuenstler_ID = K.Kuenstler_ID

AND A.Art_ID = S.Art_ID

AND T.Genre_ID = G.Genre_ID

AND MATCH (

Titel

)

AGAINST (

'knopfler mark sailing to philadelphia')

INTERSECT

SELECT DISTINCT A.Album_ID AS ID, A.Titel AS Titel, K.Nachname AS Name, K.Vorname AS Vorname

FROM Album A, Kuenstler K, Track T, CD C, Art S, Genre G

WHERE S.show_Interpret =0

AND A.Album_ID = C.Album_ID

AND C.CD_ID = T.CD_ID

AND T.Kuenstler_ID = K.Kuenstler_ID

AND A.Art_ID = S.Art_ID

AND T.Genre_ID = G.Genre_ID

AND MATCH (

Vorname, Nachname

)

AGAINST (

'knopfler mark sailing to philadelphia'

)

Nach meinem Verständnis liefern beide Abfragen eine Menge und auf Mengen sollten doch eigentlich sollche Operationen wie Vereinigung und Schnitt erlaubt sein? Aus diesem Grund stehe ich im moment auch ganz fürchterlich auf dem Schlauch

@Phil

Ich wollte eigentlich schon ganz gerne bei MySQL bleiben, da das Projekt schon etwas länger existiert und ich eigentlich nur noch einmal das (komplette) Frontend umschreiben wollte.

Viele Grüsse

Dan

[flashpixx]

Ich habe mal schnell in der 5.1er Referenz zu mySQL nach geschlagen: MySQL :: MySQL 5.1 Referenzhandbuch :: 12.7.1 Boolesche Volltextsuche

Im Grunde soll doch bei diesem Select in Titel, Vorname und Nachname nach "knopfler mark sailing to philadelphia" gesucht werden.

Warum dann 2 Selects?

Müsste die Where Bedingung nicht "where MATCH (Titel, Vorname, Nachname) AGAINST ('knopfler mark sailing to philadelphia')" lauten und den Select würde ich mit einem Select * from Album Join .... Where formulieren.

HTH Phil

[hawkeye78]

Ich hatte das so verstanden das eine Volltextsuche immer nur auf eine Tabelle funktioniert und da Vor- und Nachname in einer Tabelle stehen und Titel in einer anderen habe ich mir überlegt, das ich zwei Abfragen ausführe und dann nur die Ergebnisse anzeigen die im Schnitt dieser beiden Abfragen liegen.

Aber leider funktioniert das nicht so wie vorgesehen

[flashpixx]

Also im Moment ist es ein bisschen schwer zu erkennen wo was bei Dir steht:

Ich gehe mal davon aus Name und Vorname stehen auf der Künstlertabelle und Album auf der Album Tabelle. So wie ich das sehe unterscheiden sich dann Deine Selects nur innerhalb des Match. Im Grunde wäre ja einmal die führende Tabelle für das Match gegen den Namen die Künstlertabelle und einmal der Titel auf der Albumtabelle (somit wäre das einmal from Kuenstler und einmal from Album).

Ich würde folgendes versuchen: Ändere mal den Select so ab, dass es ein Join wird. Hinterlege den einmal als View in der DB und auf den View machst Du die Volltextsuche

Habe leider grade kein Bsp wo ich das mal selbst testen könnte

HTH Phil

[hawkeye78]

also der Aufbau der Tabellen ist so (ähnlich) wie du beschreibst, aber vom Prinzip her geht es hier um 4 Tabellen

Album

- Album_ID

- Titel

CD

- CD_ID

- Album_ID

Track

- Track_ID

- CD_ID

- Kuenstler_ID

Kuenstler

- Kuenstler_ID

- Vorname

- Nachname

Ich habe nun mal versucht wirklich eine View zu erzeugen was soweit auch geklappt hat, allerdings kann man auf einer View offenbar keine Volltextsuche ausführen? zumindestens kann ich kein ALTER TABLE albensammelung FULLTEXT(Name) machen. da dieses keine Base Tabelle ist. Darum funktioniert das wohl auch auf diesem weg leider nicht

NOch einmal vielen dank für deine Hilfe und Geduld mit mir

Viele Grüsse

Dan

[flashpixx]

Ich habe nun mal versucht wirklich eine View zu erzeugen was soweit auch geklappt hat, allerdings kann man auf einer View offenbar keine Volltextsuche ausführen? zumindestens kann ich kein ALTER TABLE albensammelung FULLTEXT(Name) machen. da dieses keine Base Tabelle ist. Darum funktioniert das wohl auch auf diesem weg leider nicht

Dass Du keinen Index auf einen View setzen kannst ist klar. Nur das Match-Against müsste auch auf einem View funktionieren und es verkürzt für den Test das komplette Statement. Wenn es dann klappt, würde ich mir die Laufzeit mit explain mal einmal über den View und einmal über dem Statement + das Match anzeigen lassen.

NOch einmal vielen dank für deine Hilfe und Geduld mit mir

Kein Problem, mich interessiert das auch gerade

Phil

[hawkeye78]

also wenn ich auf die view Namens albensammelung folgende Abfrage los lasse:

SELECT *

FROM `albensammelung`

WHERE MATCH (Name, Vorname)

AGAINST ('Mark Knopfler')

Ergibt das, die Fehlermeldung:

#1214 - The used table type doesn't support FULLTEXT indexes

(

[flashpixx]

Ergibt das, die Fehlermeldung:

#1214 - The used table type doesn't support FULLTEXT indexes

(

Schade, ich dachte das Dinge und er nimmt dann intern den passenden Index. Dann musst Du den Select wohl komplett einfügen

[hawkeye78]

ja um so länger ich mich damit beschäftige um so mehr befürchte ich auch das das eine Abfrage über 20 Teilen gibt wo ich jede Permutation testen muß. Alternativ könnte ich noch eine redundante Tabelle anlege wo ich die AlbumID und Titel mit Interpreten zwei Feldern ablege. Aber eigentlich finde ich diese Lösung auch doof, weil das wieder extrem hässliche Redudanz gibt.

[flashpixx]

Evtl nimmst Du die einzelnen Abfragen auseinander und verpackst das in der Datenbank als Stored Procedure, die Du dann von außen aufrufst.

Ich kann da leider zu mySQL nicht viel sagen, ich habe das nur mal unter Postgres gemacht und das dann mit Hilfe von PLSQL und Python innerhalb einer Procedure verwendet. Der Aufruf der Procedure kam dann via ADODB von außen.

LG Phil

[hawkeye78]

Möglicherweise kann ich mir das ausienander nehmen der Abfrage doch sparen denn ich habe gerade dank des Geistesblitz eines bekannten erfahren das MySQL den Befehl INTERSECT gar nicht überstützt. Das es gar nicht möglich ist damit die Schnittmenge zwischen zwei Abfragen in MySQL zu bilden, es soll allerdings einen Workaround mittels INNER JOIN geben allerdings bin ich mir noch nicht zu 100% sicher ob das in meinem Fall auch funktioniert.

Viele Grüsse

Dan

[Amstelchen]

Schade, ich dachte das Dinge und er nimmt dann intern den passenden Index

wenn du volltextindizierung verwenden willst (was in deiner datenbank durchaus sinn macht) - verwende den richtigen tabellentypen (MyISAM) sowie einen volltextfähigen index (FULLTEXT). von allein weiss MySQL nicht, dass du das feature verwenden willst.

das MySQL den Befehl INTERSECT gar nicht überstützt

das ist auch aus der dokumentation zu entnehmen.

generell kann es nie schaden, VOR implementierung sich mal die unterstützung einzelner features herauszupicken. man mietet sich ja auch kein moped, wenn man 5 raummeter brennholz transportieren muss.

s'Amstel

[hawkeye78]

Hallo Amstelchen,

ich habe halt den Fehler gemacht und mich nach einem Buch gerichtet in dem der SQL-Standard beschrieben ist, und nicht nach der Dokumentation von MySQL.

Allerdings ergibt nachdem ich heute morgen darüber nachgedacht habe noch ein anderes Problem, wenn ich nur nach Künstler oder dem Albumtitel suche findet er logischerweise nicht, weil er ja nach der Schnittmenge von beiden Abfragen schaut...Ein Freund hat dann in den Raum geworfen das ich eine weitere Tabelle erstellen soll. Diese soll dann aus 2 Feldern bestehen nämlich der ID des eigentlichen Albums und einem String zusammengesetzt aus dem Künstler und dem Albumtitel, und darüber soll ich dann meine Abfrage laufen lassen. Zum einen finde ich die idee durch aus sinnvoll, zum anderen stört mich der Gedanken damit Redundanz in der Datenbank zu haben. Aus diesem Grund hätte ich die Frage wie wird so etwas eigentlich für große Datenbanken gelöst sprich solche Datenbank wie amazon, oder eBay etc.

Viele Grüsse

Dan

[flashpixx]

Er meinte hier sicher nicht die redundante Speicherung sondern wohl eine m:n Beziehung.

Prüfe vielleicht noch einmal Dein ERD und die normalisierten Tabellen ggf. einmal posten. Dann vielleicht noch einmal zu überdenken wie der Suchalgo funktionieren soll

HTH Phil

[hawkeye78]

Hallo Phil,

nein, ich glaube schon das er meinte eine zusätzliche Tabelle anzulegen, den zum Beispiel für einen Fall wie

Tabelle: Album

Album_ID: 42 (PK)

Titel: Sailing To Philadephia

Tabelle: Artist:

Artist_ID: 3 (PK)

Vorname: Mark

Nachname: Knopfler

Tabelle search

keyphrase: Mark Knopfler Sailing to Philadelphia (PK)

Album_ID: 42 (FK)

Vielleicht meintest Du das auch so, aber ich möchte es gerne vermeiden das Missverständnisse entstehen und wir jetzt 10 posts weiter von der gleichen Sache reden Also entschuldig bitte falls ich einfach deine Idee wiederholt habe.

Ansonsten schaut mein ERM im moment so aus:

Kostenloser Bilder Upload Service - Gratis Bilder hochladen / uploaden ohne Anmeldung

viele Grüsse

Dan

[dr.dimitri]

Weswegen hast Du denn Album und CD voneinander getrennt? Ich würde das eher als eine Entität sehen.

Dim

[flashpixx]

Sorry, ich hab mir das ERD zwar angeschaut, muss aber noch mal drüber nachdenken. Das mit dem Album und der CD hatte ich auch gedacht, aber Ich hab hier ein ein Album, was aus mehrere DVD's besteht und in dem Hinblick würde ich dann auch 2 Entities modellieren.

Phil

[dr.dimitri]

Stimmt. Da hab ich nicht dran gedacht.

Dim