[MySQL] Querry verstehen lernen und modifizieren

[AK_CCM]

Hallo zusammen,

hoffentlich bin ich hier richtig - falls nicht: sorry.

Habe für einen Bekannten die Bildergalerie Coppermine Photo Gallery eingerichtet. Jetzt möchte ich die Bilderverwaltung um ein Inhaltsverzeichnis erweitern, das in Alben gegliederte, alphabtisch sortierte Bildtitel-Links enthält. Im CPG-Forum habe ich einen entsprechenden Code-Schnipsel gefunden und versucht anzupassen: http://www.pilzteam-bayern.de/galerie/inhalt.php

Leider verstehe ich als Laie die DB-Abfrage nicht:

SELECT a.pid, a.aid, a.title, b.title atitle from {$CONFIG['TABLE_PICTURES']} a, {$CONFIG['TABLE_ALBUMS']} b where a.aid = b.aid order by a.aid, a.pid desc

Mein Ziel ist es, die DB-Abfrage nachvollziehen zu könen und sie anschließend wie folgt zu modifizieren:
1.) Die Bildtitel-Links sollen alphabetisch sortiert ausgegeben werden.
2.) Identische Bildtitel sollen zu einem einzigen Eintrag zusammengefasst werden.
3.) Bezieht sich auf 2.): Existieren mindestens 2 identische Bildtitel, soll hinter dem zusammengefassten Eintrag die Anzahl der Bildtitel in eckigen Klammern ausgegeben werden.

Zur Verdeutlichung hänge ich noch einen Vorher- und Nachher-Screenshot dran.
Der Vollständigkeit halber findet ihr auch die PHP-Datei für das Inhaltsverzeichnis im Anhang.

Würde mich riesig freuen, wenn mir jemand helfen könnte, Licht ins Dunkel zu bringen.

Gruß, Andreas

 

[CPoly]

Ich versuchs mal zu erklären. Das Query ist wirklich nicht kompliziert.

Im Query werden zwei Tabellen miteinander verbunden. Einmal die Tabelle mit den Fotos (kurz a) und die Tabelle mit den Alben (kurz b).

Im Ergebnis des Query sind folgende Werte
a.pid -> die eindeutige ID des Fotos
a.aid -> die eindeutige ID des Albums, in welchem das Foto ist
a.title -> der Titel des Fotos
b.title atitle -> der Titel des Albums (mit dem alias atitle)

Jetzt zur WHERE-Bedingung. Dort werden die beide Tabellen miteinander verknüpft (JOIN ist der Fachausdruck).
WHERE a.aid = b.aid -> legt fest, dass beide Tabellen anhand der AlbumID verknüpft werden sollen. Ohne diese Bedingung würdest du als Ergebnis nur zusammenhanglose Werte bekommen (für jedes Foto und für jedes Album eine Zeile, wobei die Spalten von der jeweils anderen Tabelle NULL sind)

Jetzt noch die Sortierung.
ORDER BY a.aid, a.pid DESC -> sortiert wird nach der ID des Albums aufsteigend und alle Fotos mit gleichem Album werden nach der ID absteigend sortiert. Also sind die älteren Alben vor den neuen und innerhalb der Alben sind die neueren Fotos oben.


Deine Änderungen sind mit dem Wissen jetzt kein Problem mehr.

1.) Die Bildtitel-Links sollen alphabetisch sortiert ausgegeben werden.
Also ändern wir
ORDER BY a.aid, a.pid DESC
zu
ORDER BY a.aid, a.title

Punkt zwei und drei erfordern etwas mehr Änderung. Erstmal gruppieren wir die Abfrage nach Bildtitel. Also ans Ende der Abfrage GROUP BY a.title anhängen.
Jetzt möchtest du also im Klammern die Anzahl sehen, sofern es mindestens zwei sind. Die Anzahl der gruppierten Spalten gibt uns COUNT(*)
Also anstatt einfach a.title abzufragen machen wir folgendes
IF(COUNT(*)>1, CONCAT(a.title, '[', COUNT(*), ']'), a.title) AS title

Die Abfrage sollte also am Ende so aussehen

SELECT a.pid, a.aid, IF(COUNT(*)>1, CONCAT(a.title, '[', COUNT(*), ']'), a.title) AS title, b.title atitle FROM {$CONFIG['TABLE_PICTURES']} a, {$CONFIG['TABLE_ALBUMS']} b WHERE a.aid = b.aid ORDER BY a.aid, a.title GROUP BY a.title

Edit:
Ich sehe gerade, dass beim Klick auf den Titel eines Fotos, der Titel als Variable übertragen wird und dort zur Abfrage der Foto benutzt wird (wieso auch immer). Das würde durch die eckigen Klammern nicht mehr funktionieren, also müsstest du das COUNT(*) vermutlich als extra Spalte abfragen und in PHP erst die eckigen Klammern setzen.

 

[AK_CCM]

Im Query werden zwei Tabellen miteinander verbunden. Einmal die Tabelle mit den Fotos (kurz a) und die Tabelle mit den Alben (kurz b).

Prima, das mit a und b war das erste an der Abfrage, was ich nicht verstand.

a.pid -> die eindeutige ID des Fotos
a.aid -> die eindeutige ID des Albums, in welchem das Foto ist
a.title -> der Titel des Fotos

Das hatte ich widerum verstanden: pid ist höchstwahrscheinlich die Abk. für "picture identification number" und aid entsprechend "album identification number".

b.title atitle -> der Titel des Albums (mit dem alias atitle)

Wozu der Alias?

Jetzt zur WHERE-Bedingung. Dort werden die beide Tabellen miteinander verknüpft (JOIN ist der Fachausdruck).
WHERE a.aid = b.aid -> legt fest, dass beide Tabellen anhand der AlbumID verknüpft werden sollen. Ohne diese Bedingung würdest du als Ergebnis nur zusammenhanglose Werte bekommen (für jedes Foto und für jedes Album eine Zeile, wobei die Spalten von der jeweils anderen Tabelle NULL sind)

Hatte interessehalber die WHERE-Bedingung aus dem Syntax entfernt, konnte aber keine Änderung feststellen..!?

Jetzt noch die Sortierung.
ORDER BY a.aid, a.pid DESC -> sortiert wird nach der ID des Albums aufsteigend und alle Fotos mit gleichem Album werden nach der ID absteigend sortiert. Also sind die älteren Alben vor den neuen und innerhalb der Alben sind die neueren Fotos oben.

Wäre fast geschickter, wenn die Reihenfolge der Alben der Reihenfolge der Alben in der Galerie entspricht, d.h. falls der Anwender Alben verschiebt oder dazwischen ein neues Album anlegt, ändern sich auch die Positionen im Inhaltsverzeichnis. Aber ich denke, das wäre deutlich aufwändiger zu realisieren. Fürs Erste kann ich daher gut mit der Sortierung der Alben nach ihrer ID leben.

1.) Die Bildtitel-Links sollen alphabetisch sortiert ausgegeben werden.
Also ändern wir
ORDER BY a.aid, a.pid DESC
zu
ORDER BY a.aid, a.title

Das funktioniert hervorragend - anscheinend muss man gar kein ASC dranhängen, um die Daten aufsteigend zu sortieren.

Punkt zwei und drei erfordern etwas mehr Änderung. Erstmal gruppieren wir die Abfrage nach Bildtitel. Also ans Ende der Abfrage GROUP BY a.title anhängen.

Der Syntax hat funktioniert, nachdem ich ihn vor ORDER BY verschoben hatte. Seinerzeit hatte ich das Gruppieren, als ich nur eine Bildtitel-Link-Liste mit sämtlichen Bildern der Galerie brauchte, mit DISTINCT gelöst.

Jetzt möchtest du also im Klammern die Anzahl sehen, sofern es mindestens zwei sind. Die Anzahl der gruppierten Spalten gibt uns COUNT(*)
Also anstatt einfach a.title abzufragen machen wir folgendes
IF(COUNT(*)>1, CONCAT(a.title, '[', COUNT(*), ']'), a.title) AS title

Das muss ich noch genauer mit Google beleuchten und nachvollziehen.

Edit:
Ich sehe gerade, dass beim Klick auf den Titel eines Fotos, der Titel als Variable übertragen wird und dort zur Abfrage der Foto benutzt wird (wieso auch immer).

Ganz einfach: Will ein Nutzer in der Galerie nur die Bilder einer bestimmten Pilzart oder zu einem bestimmten Event (setzt identische Titel innerhalb eines Events voraus) ansehen, braucht er lediglich im Inhaltsverzeichnis auf den jeweiligen Link zu klicken und bekommt dann auf einer neuen Seite die gewünschten Suchergebnisse mit Vorschaubildern präsentiert.

Das würde durch die eckigen Klammern nicht mehr funktionieren, also müsstest du das COUNT(*) vermutlich als extra Spalte abfragen und in PHP erst die eckigen Klammern setzen.

Klingt vielleicht doof, aber wie kann ich eine extra Spalte abfragen bzw. die Abfrage muss auch in $result landen? Hoffentlich habe ich mich jetzt nicht bis aufs Mark blamiert...

[edit] Übrigens vielen Dank für Deine Hilfe - Deine Antwort hat mich deutlich weitergebracht! [/edit]

 

[CPoly]

Wozu der Alias?

Um zwischen a.title und b.title zu unterscheiden (beides heißt in der Antwort sonst title)

Hatte interessehalber die WHERE-Bedingung aus dem Syntax entfernt, konnte aber keine Änderung feststellen..!?

Das kann man aber allgemein nicht tun. Warum es hier jetzt funktioniert, kann ich nicht auf Anhieb sagen.

Das funktioniert hervorragend - anscheinend muss man gar kein ASC dranhängen, um die Daten aufsteigend zu sortieren.

ASC ist der Standardwert.

Der Syntax hat funktioniert, nachdem ich ihn vor ORDER BY verschoben hatte. Seinerzeit hatte ich das Gruppieren, als ich nur eine Bildtitel-Link-Liste mit sämtlichen Bildern der Galerie brauchte, mit DISTINCT gelöst.

Ja, da hab ich die Reihenfolge aus versehen vertauscht. Und soweit ich weiß, macht der MySQL Optimierer aus DISTINCT und GROUP BY die gleiche Abfrage, also spielt es keine Rolle. Beides lässt sich äquivalent ineinander umformen.

Das muss ich noch genauer mit Google beleuchten und nachvollziehen.

Kurz zur Erklärung des If-Statements
IF(Bedingung, Dann, Sonst)
Also, wenn COUNT(*) größer als 1 ist, wird der erste Teil nach dem Komma abgerufen, ansonsten der zweite.

Klingt vielleicht doof, aber wie kann ich eine extra Spalte abfragen bzw. die Abfrage muss auch in $result landen? Hoffentlich habe ich mich jetzt nicht bis aufs Mark blamiert...

Dazu einfach nach dem Schlüsselwort SELECT weitere Spalten mit Komma getrennt anhängen. Z.B.

SELECT a.pid, a.aid, a.title, b.title atitle, COUNT(*) AS num_pics FROM

Ich habe hier außerdem das Schlüsselwort "AS" benutzt, um einen Alias zu definieren. Das Schlüsselwort ist aber optional. Ich finde es erhöht die Lesbarkeit.

 

[AK_CCM]

Habe noch ein seltsames Phänomen bemerkt, das ich während meines letzten Postings nicht bemerkt hatte:

Nachdem Ergänzen der Abfrage mit GROUP BY a.title wird das Album "VHS-Kurse" nicht mehr angezeigt.

Kann mir leider keinen Reim darauf machen...

 

[AK_CCM]

Nachtrag: Anscheinend liegt das daran, dass in dem Album keine Bilder mit Titeln vorhanden sind, denn nachdem ich einen Titel vergeben habe, werden Album und Bildtitel aufgelistet. Fände es zwar praktisch, wenn alle Alben ausgeblendet werden, die keine Bilder mit Titeln enthalten. Andererseits beinhaltet das Album "Pilzberatungen" auch keine Bilder mit Titeln, wird aber angezeigt. Erkenne darin kein Muster...

 

[CPoly]

Durch GROUP BY a.title werden alle Fotos ohne Titel in einen Topf geworfen. Also musst du das noch pro Album machen, spricht GROUP BY a.aid, a.title. Dadurch wird nach der Kombination aus AlbumID und Foto-Title gruppiert.