✔ Verknüpfungstabelle mit SELECT GROUP_CONCAT ausgeben

print147 · 21. April 2012

Hallo zusammen,

Ich möchte die Spalte optID der im Anhang zu findenden Verknüpfungstabelle mit Hilfe von group_concat ausgeben.

Die Tabelle zeigt auf, welche Dimensionen (Farben und Größen) und Merkmalsausprägungen (rot, blau, gelb, small, medium, large) an den einzelnen Artikelvarianten (Spalte combiOptID) beteiligt sind. Die Spalte combiOptGrID faßt die erzeugten Artikelvarianten unter einer ID und einem Namen (z.B. T-Shirt) zusammen. Kombiniert wurden die Farben 3=rot, 4=blau und die Größen (6=small, 7=medium und 8=large).

PHP:

$values = array( array( '3', '4' ), array( '6', '7', '8' ));
$valuesText = array( array( 'rot', 'blau' ), array( 'small', 'medium', 'large' ));
Ich habe es mit folgender Query versucht:
$sqlB    =   "   SELECT ";
$sqlB    .=  "   GROUP_CONCAT($optCombiRel.optID SEPARATOR ' - ') as optID";
$sqlB    .=  "   FROM $optCombiRel ";
$sqlB    .=  "   WHERE $optCombiRel.combiOptGrID = 9 ";
$sqlB    .=  "   GROUP BY $optCombiRel.combiOptID ";
$resB    =   mysql_query($sqlB) or die(mysql_error());
$numB    =   mysql_num_rows($resB);
echo "numB: ".$numB."<br>";
$r=1;
while($rows = mysql_fetch_array($resB))
{
echo $r.":  ".$rows['optID']."<br>";
$r++;
}
numB: 6
1: 3 - 6
2: 3 - 7
3: 3 - 8
4: 6 - 4
5: 4 - 7
6: 4 – 8
Er vertauscht hier jedoch Farbe und Größe:

Ich hoffe, Ihr könnt mir weiterhelfen. Vielen Dank im voraus!

Parantatatam · 21. April 2012

Hast du es schon mit ORDER BY versucht?

print147 · 22. April 2012

Ja, aber trotz ORDER BY bleibt der Fehler in Zeile 4 der Ausgabe weiter erhalten.

Yaslaw · 23. April 2012

Was für ein Fehler?

Dein SQL sieht grundsätzlich sauber aus.
Nur habe ich a) nicht verstanden was du genau haben willst, b) was für ein Fehler auftritt und c) welches diese Zeile 4 ist.

SQL:

-- Dein SQL, zur besseren lesbarkeit mal aus dem PHP extrahiert.
SELECT
	GROUP_CONCAT(optID SEPARATOR ' - ') as optID
FROM 
	{$optCombiRel}
WHERE 
	combiOptGrID = 9
GROUP BY 
	combiOptID

Die combiOptID, nach welcher du gruppiertst würde ich auch noch in den SELECT nehmen um anzuzeigen zu welchem combiOptID die Zeile gehört.

print147 · 26. April 2012

Schön, daß noch jemand zu dem Thema Stellung nimmt.
Wo liegt der Fehler?
numB: 6
1: 3 – 6 ( rot – small)
2: 3 – 7 ( rot – medium)
3: 3 – 8 ( rot – large)
4: 6 - 4 (small – blau)
5: 4 – 7 (blau – medium)
6: 4 – 8 (blau –large)
Meine Group-Concat-Query erzeugt mir folgende sechs Kombinationen von Farbe und Größe eines T-Shirts (combiOptGrID). Diese T-Shirt-Varianten sind in der obigen Verknüpfungstabelle untereinander aufgelistet (für jede Farbe und jede Größe ein separater Datensatz). So können später dem T-Shirt problemlos neue Kombinationen hinzugefügt werden. Fehler: 4: 6 - 4 (small – blau).

Was möchte ich erreichen:
Ich möchte aus der Verknüpfungstabelle eine Ergebnistabelle erzeugen. Diese zeigt auf, welche Varianten zu den einzelnen Kombigruppen gehören und aus welchen Merkmalen und Merkmalsausprägungen jede Variante besteht Durch Anklicken des Icons in der Spalte Update oder Delete kann der Anwender gezielt ein Variante(Kombination) ändern oder löschen. Die Zahlenwerte oder IDs brauche ich lediglich zur Konstruktion der Image-Links. Der Anwender sieht nur die Nicht-Zahlenwerte. Eine Combi kann aus beliebig vielen Dimensionen und Ausprägungen bestehen.

Da ich mit GROUP_CONCAT nicht weitergekommen bin, habe ich das Control-Panel über Queries und multidimensionale Arrays erzeugt. So kann ich auf die einzelnen Paramter einer Combi gezielt zugreifen und auch doppelte Spalten ausblenden.
Die Lösung erfüllt zwar ihren Zweck, aber sicherlich kann man das auch eleganter lösen. Vielleicht kann mal ein kluger Kopf einen Blick auf die Queries und Arrays werfen.
Der Quellcode befindet sich in der Textdatei. Ebenso im Anhang: die mit dem Code erzeugte Ergebnistabelle. Bei Bedarf kann ich auch noch die beteiligten Datenbanktabellen und Verknüpfungen grafisch oder als sql-Datei liefern. Vielen Dank im voraus für Eure konstruktive Kritik.

Yaslaw · 26. April 2012

item: Und was ist an '4: 6 - 4 (small – blau)' Falsch? Was müsste denn da stehen?

item: In deiner combi_options_tabelle hast du viele Spalten ohne Namen.
a) Ist diese Tabelle ein IST oder SOLL Zustand?
b) Was sind das für Spalten (Bezeichnung plus was es sein soll. Also id1 und id2 sind nciht hilfreich)

item: Es sit schwer zu sagen was da wie läuft wenn einem die Basis fehlt. Für dich mag so manches klar sein, für mich gar nicht. Ich sehe nur Nummern und eine Angabe das da eine Nummer falsch ist. Um zu sagen wie ein SQL aussehen soll, muss man die Datenbankstruktur kennen.

Am besten machst du mal Datenbeispiel mit Tabellen. So in der Art:

Das hab ich:

Code:

Tabelle mitarbeiter
id | firma_id | name
--------------------
1  | 1        | MA1
2  | 1        | MA2
3  | 2        | MA3

Tabelle Firma
id | name
---------------------
1  | firma1
2  | firma2

Nun will ich wissen, welche Firma wieviele Mitarbeiter hat. Das soll dann so aussehen

Code:

firma_id | firma_name | anzahl_mitarbeiter
------------------------------------------
1        | firma1     | 2
2        | firma3     | 1

item: Ein Tipp zu deinen SQL-Statements in PHP. Arbeite mit Tabellenalias. Es wird viel lesbarer wenn du die Tabellennamenvariable nur einmal einsetzen musst

item: Tipp 2. mMn wird es mit den folgenden 2 Punkten noch lesbarer.... (Ist aber wie immer bei solchen Sachen geschmacksache)
a) den SQL-Teil in PHP formatieren: Heredoc-Syntax
b) Die Complex (Curly) Syntax anwenden um die Strings zusammenzusetzen.

PHP:

$sqlC0   = <<<SQL
SELECT DISTINCT 
	ocr.combiOptGrID, 
	ocr.combiOptID, 
	ogd.optGrName
FROM 
	{$optCombiRel} 	AS ocr, 
	{$optGroupDescr} 	AS ogd
WHERE
	ocr.combiOptGRID         = ogd.optGrID
	AND ogd.language_id   = 4
ORDER BY 
	ocr.combiOptGrID ASC;
SQL;

print147 · 26. April 2012

item: Und was ist an '4: 6 - 4 (small – blau)' Falsch? Was müsste denn da stehen?

Richtig wäre: 4: 4 – 6 (blau –small)

item: In deiner combi_options_tabelle hast du viele Spalten ohne Namen.
a) Ist diese Tabelle ein IST oder SOLL Zustand?
b) Was sind das für Spalten (Bezeichnung plus was es sein soll. Also id1 und id2 sind nciht hilfreich)

a) Soll-Zustand
b) Nachfolgend Tabelle mit Legende

item: Es sit schwer zu sagen was da wie läuft wenn einem die Basis fehlt. Für dich mag so manches klar sein, für mich gar nicht. Ich sehe nur Nummern und eine Angabe das da eine Nummer falsch ist. Um zu sagen wie ein SQL aussehen soll, muss man die Datenbankstruktur kennen.

Stimmt. Anbei Tabellendiagramm und die DB-Struktur sowie die Datei option_combi_panel_inc5b2.txt.

Code:

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Erstellungszeit: 26. Apr 2012 um 21:11
-- Server Version: 5.5.16
-- PHP-Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Datenbank: `y1_multishop`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `optiongroup`
--

CREATE TABLE IF NOT EXISTS `optiongroup` (
  `optGrID` int(11) NOT NULL AUTO_INCREMENT,
  `optGrForCombi` int(11) DEFAULT NULL,
  PRIMARY KEY (`optGrID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Daten für Tabelle `optiongroup`
--

INSERT INTO `optiongroup` (`optGrID`, `optGrForCombi`) VALUES
(1, 0),
(2, 0),
(3, 0),
(4, 0),
(6, 1),
(7, 1),
(8, 1),
(9, 2),
(10, 2);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `optiongroup_description`
--

CREATE TABLE IF NOT EXISTS `optiongroup_description` (
  `optGrID` int(11) DEFAULT NULL,
  `language_id` int(11) DEFAULT NULL,
  `optGrName` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `optiongroup_description`
--

INSERT INTO `optiongroup_description` (`optGrID`, `language_id`, `optGrName`) VALUES
(1, 4, 'Lizenz_de'),
(1, 5, 'Lizenz_en'),
(2, 4, 'Templates'),
(2, 5, 'Templates'),
(3, 4, 'Copyrightvermerk entfernen'),
(3, 5, 'Remove Copyright'),
(4, 4, 'Versand auf CD / DVD'),
(4, 5, 'Shipping on CD / DVD'),
(6, 4, 'Farbe'),
(6, 5, 'Colour'),
(7, 4, 'Größe'),
(7, 5, 'Size'),
(8, 4, 'Material'),
(8, 5, 'Material'),
(9, 4, 'T-Shirt'),
(9, 5, 'T-Shirt'),
(10, 4, 'Combi II'),
(10, 5, 'Combi_en');

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `optiongroup_to_option`
--

CREATE TABLE IF NOT EXISTS `optiongroup_to_option` (
  `optGrID` int(11) DEFAULT NULL,
  `optID` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `optiongroup_to_option`
--

INSERT INTO `optiongroup_to_option` (`optGrID`, `optID`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(1, 2),
(6, 3),
(6, 4),
(6, 5),
(7, 6),
(7, 7),
(7, 8),
(8, 9),
(8, 10),
(6, 11),
(6, 12);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `options`
--

CREATE TABLE IF NOT EXISTS `options` (
  `optID` int(11) NOT NULL AUTO_INCREMENT,
  `optForCombi` int(11) DEFAULT NULL,
  PRIMARY KEY (`optID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;

--
-- Daten für Tabelle `options`
--

INSERT INTO `options` (`optID`, `optForCombi`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 0),
(6, 1),
(7, 1),
(8, 1),
(9, 1),
(10, 1),
(11, 0),
(12, 0);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `option_combis`
--

CREATE TABLE IF NOT EXISTS `option_combis` (
  `combiOptID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`combiOptID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;

--
-- Daten für Tabelle `option_combis`
--

INSERT INTO `option_combis` (`combiOptID`) VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12),
(13),
(14),
(15),
(16),
(17),
(18);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `option_combi_relations`
--

CREATE TABLE IF NOT EXISTS `option_combi_relations` (
  `combiOptGrID` int(11) DEFAULT NULL,
  `combiOptID` int(11) DEFAULT NULL,
  `optGrID` int(11) DEFAULT NULL,
  `optID` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `option_combi_relations`
--

INSERT INTO `option_combi_relations` (`combiOptGrID`, `combiOptID`, `optGrID`, `optID`) VALUES
(9, 1, 6, 3),
(9, 1, 7, 6),
(9, 2, 6, 3),
(9, 2, 7, 7),
(9, 3, 6, 3),
(9, 3, 7, 8),
(9, 4, 6, 4),
(9, 4, 7, 6),
(9, 5, 6, 4),
(9, 5, 7, 7),
(9, 6, 6, 4),
(9, 6, 7, 8),
(10, 7, 6, 3),
(10, 7, 7, 6),
(10, 7, 8, 9),
(10, 8, 6, 3),
(10, 8, 7, 6),
(10, 8, 8, 10),
(10, 9, 6, 3),
(10, 9, 7, 7),
(10, 9, 8, 9),
(10, 10, 6, 3),
(10, 10, 7, 7),
(10, 10, 8, 10),
(10, 11, 6, 3),
(10, 11, 7, 8),
(10, 11, 8, 9),
(10, 12, 6, 3),
(10, 12, 7, 8),
(10, 12, 8, 10),
(10, 13, 6, 4),
(10, 13, 7, 6),
(10, 13, 8, 9),
(10, 14, 6, 4),
(10, 14, 7, 6),
(10, 14, 8, 10),
(10, 15, 6, 4),
(10, 15, 7, 7),
(10, 15, 8, 9),
(10, 16, 6, 4),
(10, 16, 7, 7),
(10, 16, 8, 10),
(10, 17, 6, 4),
(10, 17, 7, 8),
(10, 17, 8, 9),
(10, 18, 6, 4),
(10, 18, 7, 8),
(10, 18, 8, 10);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `option_description`
--

CREATE TABLE IF NOT EXISTS `option_description` (
  `optID` int(11) DEFAULT NULL,
  `language_id` int(11) DEFAULT NULL,
  `optName` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Daten für Tabelle `option_description`
--

INSERT INTO `option_description` (`optID`, `language_id`, `optName`) VALUES
(1, 4, 'nicht notwendig'),
(1, 5, 'not required'),
(2, 4, 'EPL-Lizenz'),
(2, 5, 'EPL License'),
(3, 4, 'rot'),
(3, 5, 'red'),
(4, 4, 'blau'),
(4, 5, 'blue'),
(5, 4, 'gelb'),
(5, 5, 'yellow'),
(6, 4, 'small'),
(6, 5, 'small'),
(7, 4, 'medium'),
(7, 5, 'medium'),
(8, 4, 'large'),
(8, 5, 'large'),
(9, 4, '100% Baumwolle'),
(9, 5, '100% Cotton'),
(10, 4, '80% Bamwolle'),
(10, 5, '80% Cotton'),
(11, 4, 'schwarz'),
(11, 5, 'black'),
(12, 4, 'wei&szlig;'),
(12, 5, 'white');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Kurze Erklärung zu den Tabellen:
optiongroup und optiongroup_description.
Anlegen der Optionsgruppen und CombiOptionsgruppen in den Tabellen optiongroup und optiongroup_description. Die Spalte optGrForCombi hat folgende Bedeutung:
0 = diese Optionsgruppe kann nicht mit einer anderen Optionsgruppe kombiniert werden
1 = diese Optionsgruppe darf mit beliebig vielen Optionsgruppen kombiniert werden.
2 = Name der Kombi-Optionsgruppe (z.B. T-Shirt, Combi-II).

Options, options_description und optiongroup_to_option
Anlegen der Merkmalsausprägungen (Options) in verschiedenen Sprachen. Über die Spalte optForCombi kann gesteuert werden, welche Optionen kombiniert werden dürfen (1) und welche nicht (0).
In der Tabelle optiongroup_to_option wird jede Option einer oder mehreren Optionsgruppen zugeordnet.
Option_combis und option_combi_relations
In der Tabelle option_combis wird die ID einer Variante eingetragen, die durch Kombination von zwei oder mehreren Optionsgruppen und deren Merkmalsausprägungen erzeugt wurde. Die Tabelle option_combi_relations zeigt auf, aus welchen Optionsgruppen und Merkmalsausprägungen eine Variante besteht.

Yaslaw · 27. April 2012

Jetzt versteh ich mehr oder minder worum es geht. Ist glaub nicht so kpmpliziert wie ich dachte.

Ist es immer Aufsteigend nach der optID? Wenn ja, kann men in GROUP_CONCAT eine ORDER BY mitgeben.

SQL:

SELECT
    ocr.combiOptID,
    GROUP_CONCAT(ocr.optID ORDER BY ocr.optID SEPARATOR ' - ') AS optIDs,
    -- Du kannst auch ein anderes Feld auswählen und nach der optID sortieren
    GROUP_CONCAT(od.optName ORDER BY ocr.optID SEPARATOR ' - ') AS optNames
FROM 
    option_combi_relations ocr
    INNER JOIN option_description od
    	  ON ocr.optID = od.optID
WHERE 
    ocr.combiOptGrID = 9
    AND od.language_id = 4
GROUP BY 
    ocr.combiOptID

Code:

| combiOptID | optIDs | optNames       |
----------------------------------------
|          1 | 3 - 6  | rot - small    |
|          2 | 3 - 7  | rot - medium   |
|          3 | 3 - 8  | rot - large    |
|          4 | 4 - 6  | blau - small   |
|          5 | 4 - 7  | blau - medium  |
|          6 | 4 - 8  | blau - large   |

print147 · 29. April 2012

Yaslaw, nochmals Danke für Deinen Lösungsvorschlag. Habe ihn angepaßt. Funktioniert prima.

✔ Verknüpfungstabelle mit SELECT GROUP_CONCAT ausgeben

print147

Grünschnabel

Parantatatam

mag Cookies & Kekse

print147

Grünschnabel

Yaslaw

alter Rempler

print147

Grünschnabel

Anhänge

Yaslaw

alter Rempler

print147

Grünschnabel

Anhänge

Yaslaw

alter Rempler

print147

Grünschnabel

Neue Beiträge