MySql Statement - wie ist es richtig?

[Kermit]

Hallo,
arbeite mit MySql und Php.

habe mehrere Tabellen die untereinander über FK verknüpft sind. Ich möchte nun alle Produkte angezeigt bekommen, bei denen die Bezeichnung wie $name ist und die nicht in der Kategorie 13 sind. Dafür habe ich folgende Abfrage:

$query_1 = ("select distinct(p.produkt_id), p.name, at.AT_Katalognr, ty.Typ_Name from products p, producttyp at, productcategorie ptc, typ ty where p.bezeichnung LIKE '%$name%' and at.ID=p.produkt_id ty.ID = at.ID and ptc.id=p.produkt_id and ptc_kategorie !='13' order by at.AT_Release desc");

Soweit so gut, aber mein Problem ist, daß ein Prodkut mehreren Kategorien zugeteilt sein kann und durch die obere Abfrage bekomme ich dann natürlich auch die Produkte angezeigt, die z.B. sowohl der Kategorie 13 als auch der Kategorie 14 zugeteilt sind (ist ja klar) aber ich will wenn ein Produkt der 13 zugeteilt ist, dieses Produkt nicht angezeigt bekommen, egal in welchen anderen Kategorien es sonst noch vorkommt. Außerdem will ich jedes Produkt nur einmal dargestellt bekommen, egal wievielen Kategorien es zugeteilt ist. Die Kategorie ist für die Anzeige vollkommen egal, die wird nur gebraucht, um alle Produkte aus der 13 auszuschließen.

Irgendwie habe ich ein Brett vorm Kopf und komme da nicht weiter.
Wäre euch dankbar wenn ihr mir kurz helfen könntet.

Grüße,
Kermit

[Kermit]

Und gleich das nächste Problem,
achja, habe vorher vergessen zu sagen: arbeite mit MySql 4.0

habe die gleiche Abfrage wie oben nur noch mit folgendem Zusatz
where p._side LIKE '%$name%' or (p.anzeige LIKE '%$name%' && p.leitung NOT LIKE '%name%')

Also ich möchte alle Produkte angezeigt bekommen, in denen $name entweder bei side oder bei anzeige vorkommt aber nicht bei leitung. Dachte das wäre so ok, die Ausgabe spricht aber was anderes, zeigt mir alle Ergebnisse bei dem der name entweder bei side, anzeige oder leitung vorkommt.....

Krieg noch die Krise,
Kermit

[Vinzenz Mai]

Hallo Kermit,

Dein logischer Ausdruck

where p._side LIKE '%$name%' or (p.anzeige LIKE '%$name%' && p.leitung NOT LIKE '%name%')

stimmt leider nicht mit dem überein, was Du gerne hättest ...

Also ich möchte alle Produkte angezeigt bekommen, in denen $name entweder bei side oder bei anzeige vorkommt aber nicht bei leitung.

Hmm, wirklich _entweder_ bei side
              _oder_     bei anzeige?

Eine eher ungewöhnliche Anforderung. Dafür gibt es XOR.

WHERE (p.side LIKE '%[code lang=php]$name%' XOR p.anzeige LIKE '%$name%')
    AND p.leitung NOT LIKE '%$name%'[/code]

wäre die WHERE-Klausel für das was Du beschreibst. Ob dies auch wirklich das ist, was Du als Ergebnis wünschst, das steht auf einem anderen Blatt. Bring' bitte ein paar Beispieldatensätze - und Dein gewünschtes Ergebnis.

Freundliche Grüße

Vinzenz

[Kermit]

Hallo Vinzenz,
denke schon das ich das richtig meine....
habe z.B.
Spalte Leitung
1."Hans";
2."Martin";
3."Peter";
Spalte Side
1."Günther, Peter, Stefan";
2."Stefan, Thomas, Paul";
3."Julius, Cäsar, Gaius";
Spalte Anzeige
1."Hans, Martin, Ursula";
2."Hans, Ursula, Peter";
3."Ursula, Martin, Klaus";

Wenn ich nun nach Hans suche möchte ich das er mir Zeile 2 ausgibt. In Zeile 1 ist Hans in Leitung also nicht ausgeben, in Zeile 2 ist er in Anzeige in Spalte 3 kommt er gar nicht vor.
Suche ich nach Peter gibt er mir Zeile 1 und 2 aus da in 3 Peter in Leitung also nicht ausgeben, in 1 Peter in Side und in 2 Peter in Anzeige.
Sollte ein Name in Side und Anzeige vorkommen (was eigentlich nicth der Fall sein dürfte aber man weiß ja nie...) dann soll er diese Zeile nur einmal ausgeben.

Hoffe das ist einigermaßen verständlich ausgedrückt ;-)

Grüße,
Kermit

[Vinzenz Mai]

Hallo Kermit,

am besten lässt man das PHP-Geraffel weg, wenn es um ein SQL-Statement geht. JOIN-Syntax zu benutzen ist ebenfalls eine gute Idee und dann haben wir

select distinct(p.produkt_id), p.name, at.AT_Katalognr, ty.Typ_Name from products p, producttyp at, productcategorie ptc, typ ty where p.bezeichnung LIKE '%$name%' and at.ID=p.produkt_id ty.ID = at.ID and ptc.id=p.produkt_id and ptc_kategorie !='13' order by at.AT_Release desc");

SELECT DISTINCT  -- wieso eigentlich DISTINCT?  
    p.produkt_id,  
    p.name,  
    at.AT_Katalognr,  
    ty.Typ_Name  
FROM products p  
INNER JOIN producttyp at  
ON p.produkt_id = at.ID  
INNER JOIN productcategorie ptc  
ON p.produkt_id = ptc.id  
INNER JOIN typ ty  
ON at.ID = ty.ID  
WHERE ptc_kategorie != 13       -- wenn das Zahlen sind, weg mit den  
                                -- einfachen Anführungszeichen  
    AND p.bezeichnung LIKE '%[code lang=php]$name
~~~%[/code]  
  
So haben wir schon eine viel übersichtlichere und leichter verständliche SQL-Anweisung.  
  

> Soweit so gut, aber mein Problem ist, daß ein Prodkut mehreren Kategorien zugeteilt sein kann und durch die obere Abfrage bekomme ich dann natürlich auch die Produkte angezeigt, die z.B. sowohl der Kategorie 13 als auch der Kategorie 14 zugeteilt sind (ist ja klar) aber ich will wenn ein Produkt der 13 zugeteilt ist, dieses Produkt nicht angezeigt bekommen, egal in welchen anderen Kategorien es sonst noch vorkommt.  
  
Hmm, eigentlich ein typischer Fall für Subselects - aber das setzt MySQL 4.1 voraus, über das Du nicht verfügst.  
  

> Außerdem will ich jedes Produkt nur einmal dargestellt bekommen, egal wievielen Kategorien es zugeteilt ist. Die Kategorie ist für die Anzeige vollkommen egal, die wird nur gebraucht, um alle Produkte aus der 13 auszuschließen.  
  
Aha, daher Dein Versuch mit DISTINCT :-)  
Nein, das ist der falsche Ansatz. Ich fürchte fast, dass Du mit MySQL 4.0 nicht weiterkommst. Was Du haben willst, schreit an den verschiedensten Stellen nach Subselects. Die gibt es aber erst ab MySQL 4.1 :-(  
  
Ob Du Deine Anforderungen mit den Beschränkungen von MySQL 4.0 umsetzen kannst, das kann ich im Moment leider nicht abschätzen.  
  
  
Freundliche Grüße  
  
Vinzenz

[Kermit]

Hi Vinzenz,
danke für die Hilfe!

Ja ja mit den Joins habe ich mich noch nicht anfreunden können ;-( irgendwie kapiere ich da die richtige Syntax nicht....
Hmm, wenn das mit MySql 4.0 nicht hin geht ist das ärgerlich aber hilft nichts. Da muß ich dann die unelegante Lösung nehmen und während der Ausgabe nochmal abfragen ob das Produkt in der Kategorie 13 ist oder nicht.....

Denke mal, das selbe mache ich bezüglich Problem zwei da anscheinend Not Like auch nicht unterstützt wird.....

Trotzdem Danke und Grüße,
Kermit

[Ilja]

yo,

Vinzenz hat damit recht, dass eine unterabfrage das hier elegant lösen würde. aber da diese nicht zur verfügung steht, kann man vielleicht auch ein wenig tricksen. Ich klau mir mal Vinzenz strukturierte Abfrage unter ändere sie ein wenig, wobei ich mich nur um die produkte tabelle und kategorie kümmere. dort scheint mir der knackpunkt zu sein:

SELECT DISTINCT p.produkt_id, p.name
FROM products p
LEFT JOIN productcategorie ptc
ON (p.produkt_id = ptc.id)
WHERE ptc_kategorie = 13
AND ptc.id IS NULL

versuch das mal und schau, ob es die richtigen datensätze trifft. wenn ja kannst du die anderen tabellen mit reinbringen.

ps: das LIKE kann man eventuell durch eine prüfung auf gleichheit ersetzen, wenn es den ein vollständiger name ist, das bringt sehr viel performance oder aber eventuell das führende % weglassen, wenn es nur um den hinteren bereich geht.

Ilja

[Kermit]

Hi Ilja,
Habe das Statement noch nicht ausprobiert, aber habe enie verständnisfrage: "ptc.id IS NULL" - in der Tabelle ist kein NULL angegeben - sprich wenn ein Datensatz vorhanden ist ist sowohl ptc.id ptc.category besetzt. Oder verstehe ichd as jetzt falsch...?
Bezüglich %$name% das ist leider nötig, da in der Spalte mehrere Namen stehen können und nicht klar ist an welcher Stelle der gesuchte Name zu finden ist.

Grüße,
Kermit

[Ilja]

yo,

Habe das Statement noch nicht ausprobiert

solltest du nach möglichkeit immer sofort, weil wenn es nicht zum gewünschten ergebnis führt, können wir uns die arbeit sparen. ;-)

aber habe enie verständnisfrage: "ptc.id IS NULL" - in der Tabelle ist kein NULL angegeben - sprich wenn ein Datensatz vorhanden ist ist sowohl ptc.id ptc.category besetzt. Oder verstehe ichd as jetzt falsch...?

es geht ja darum, datensätze auszuschließen, die in einem anderen datensatz einen bezug zu der ID 13 haben. und da du keine unterabfragen anwenden kannst, muss man sich einem trick verhelfen, indem man einen OUTER JOIN macht, der nur gegen diese ID 13 datensätze geprüft wird.

ein OUTER JOIN macht nun folgendes, er versucht datensätze die datensätze der beiden tabellen gemäß der join bedingung miteinander zu verbinden, so wie auch bei einem "normalen" INNER JOIN. im gegensatz aber zu einem INNER JOIN, der keinen datensatz in die ergebnismenge schreiben würde, falls die join bedingung nicht wahr ist, schreibt der OUTER JOIN immer einen Datensatz in die ergebnismenge und fehlende daten der auszugebende spalten füllt er eigenständig mit NULL werten auf, da ihm ja bestimmte daten aus einer Tabelle fehlen. Da kommen auch die NULL werte zustande, nach denen du gefragt hast.

kurz noch ein beispiel, um das anschaulicher zu machen. wenn du eine abfrage über alle mitarbeiter machst und zusätzlich noch alle kinder ausgeben willst, falls sie den welche haben, dann löst ein OUTER JOIN genau dieses problem. ein INNER JOIN würde ja die mitarbeiter ohne kinder "verschlucken", einer OUTER JOIN zeigt hingegen immer alle mitarbeiter an und falls vorhanden, auch die entsperchenden kinder.

Bezüglich %$name% das ist leider nötig, da in der Spalte mehrere Namen stehen können und nicht klar ist an welcher Stelle der gesuchte Name zu finden ist.

klingt nach unsauberen datendesign, sprich nach verletzung der 1. normalfotm. gibt es den gute gründe dafür, dass in einer spalte mehrere namen stehen ? dieses LIKE ist nämlich sehr teuer, was die performance betrifft.

Ilja

[Kermit]

Hi Ilja,
danke für die ausführliche Erklärung - jetzt kapier ich das endlich mal! Werde die Abfrage gleich ausprobieren.

klingt nach unsauberen datendesign, sprich nach verletzung der 1. normalfotm. gibt es den gute gründe dafür, dass in einer spalte mehrere namen stehen ? dieses LIKE ist nämlich sehr teuer, was die performance betrifft.

Ja in diesem Fall ist es unsauberes Datendesign mit Doppellungen.
Aber das mußte ausnahmsweise so gemacht werden (war eine Vorgabe) - ich hätte auch lieber alle Namen in einer eigenen Tabelle und diese dann dem jeweiligen Datensatz zugeordnet (würde mir Probleme wie dieses sparen und wäre auch noch einheitlicher....)

Tausend Dank,
Kermit

[Ilja]

yo,

Ja in diesem Fall ist es unsauberes Datendesign mit Doppellungen.
Aber das mußte ausnahmsweise so gemacht werden (war eine Vorgabe) - ich hätte auch lieber alle Namen in einer eigenen Tabelle und diese dann dem jeweiligen Datensatz zugeordnet (würde mir Probleme wie dieses sparen und wäre auch noch einheitlicher....)

manchmal muss man solchen vorgaben entgegensteuern, wenn man es den kann. es sollten schon triftige gründe vorliegen, so einen "murks" zu fabrizieren.

Ilja

[Sven Rautenberg]

Moin!

Nur eines zu deinem Kommentar:

  

> SELECT DISTINCT  -- wieso eigentlich DISTINCT?  
>     p.produkt_id,  
>     p.name,  
>     at.AT_Katalognr,  
>     ty.Typ_Name  
> FROM products p  
> INNER JOIN producttyp at  
> ON p.produkt_id = at.ID  
> INNER JOIN productcategorie ptc  
> ON p.produkt_id = ptc.id  
> INNER JOIN typ ty  
> ON at.ID = ty.ID  
> WHERE ptc_kategorie != 13       -- wenn das Zahlen sind, weg mit den  
>                                 -- einfachen Anführungszeichen  
>     AND p.bezeichnung LIKE '%$name%'  

Egal ob das Zahlen oder keine Zahlen sind: Immer rein mit den einrfachen Anführungszeichen! MySQL unterscheidet nicht, ob eine Zahl in Anführungszeichen steht, oder nicht - das Parsing, um aus einem Text eine Zahl zu machen, geschieht so oder so. Aber mit Anführungszeichen drumrum kann man die Zahl dynamisch aus einer Variablen reinschreiben, und weil Variablen, die vom User kommen, ja potentiell böse sein können, kann man auf solche dynamischen Zahlenwerte trotzdem mysql_real_escape_string() anwenden.

Außerhalb von einfachen Anführungsstrichen hingegen würde mysql_real_escape_string() versagen!

Deshalb: Alle Werte, auch reine Zahlenwerte, in MySQL immer in einfache Anführungsstriche packen, und immer escapen, wenn sie nicht statisch im Statement stehen!

- Sven Rautenberg

[Vinzenz Mai]

Hallo Sven,

Deshalb: Alle Werte, auch reine Zahlenwerte, in MySQL immer in einfache Anführungsstriche packen, und immer escapen, wenn sie nicht statisch im Statement stehen!

sicherlich hast Du recht, wenn es um ein dynamisch generiertes Statement handelt - und diese 13 kommt wahrscheinlich nicht statisch rein.

Scheibe ich ein SQL-Statement von Hand, werde ich Zahlen sicher in _keinem_ Fall in einfache Anführunszeichen packen, genauswenig wie ich von Hand überflüssigerweise irgendwelche Tabellen- oder Spaltenname quote, da ich in beiden Fällen finde, dass die Lesbarkeit und die Verständlichkeit darunter leidet. Bei statischen Anweisungen fällt dies unter Geschmackssache. Die von phpMyAdmin generierten SQL-Statements finde ich ungenießbar, sie sind mit Backticks versalzen :-)

Freundliche Grüße

Vinzenz

[Alexander Brock]

Hallo Forum,

Egal ob das Zahlen oder keine Zahlen sind: Immer rein mit den einrfachen Anführungszeichen! MySQL unterscheidet nicht, ob eine Zahl in Anführungszeichen steht, oder nicht - das Parsing, um aus einem Text eine Zahl zu machen, geschieht so oder so. Aber mit Anführungszeichen drumrum kann man die Zahl dynamisch aus einer Variablen reinschreiben, und weil Variablen, die vom User kommen, ja potentiell böse sein können, kann man auf solche dynamischen Zahlenwerte trotzdem mysql_real_escape_string() anwenden.

Ich bevorzuge es, Zahlen nach integer zu konvertieren, dann enthalten sie auch keine bösen Zeichen mehr (zumindest nicht in den mir bekannten SQL-Dialekten *g*).

Da wird auch der eigentliche Typ einer Variable verdeutlicht, der Code ist für Außenstehende besser verständlich.

Gruß
Alexander Brock