✔ Joins über mehrere Tabellen

[slimox]

Hallo zusammen,

Das Problem sieht einfach aus, doch finde ich keine Lösung...

Es gibt eine Tabelle mit Büchern und jedes Buch hat eine buch_id.
Es gibt sechs weitere Tabellen: Die buchautor_join mit der buchautor_name, die verlags_join mit der verlags_name und die farben_join mit der farben_name Tabelle.

In den _namen Tabellen sind nur die Namen von z.B Autor, Verlag oder der Farbe. In den _join Tabellen sind nur die ID's vom Buch und von der _namen Tabelle. Pro Buch kann es beliebige Anzahl Autoren, Verläge und Farben geben.

Mein Ziel: Mit PHP einen Array aufbereiten, in welchem alle Bücher aufgelistet sind $bucher = array(). Zu jedem Buch braucht es einen SubArray $bucher[buch_id][autor] = array() für Autor, Verlag und Farben.
So könnte der Array für ein Buch aussehen:

array(
    'buch_id' => 2,
    'name'    => 'Buch123',
    'autor'   => array(
        array(
            'id'   => 1,
            'name' => 'AutorABC'
        ),
        array(
            'id'   => 3,
            'name' => 'AutorXYZ'
        )
    ),
    'verlag' => array(),
    'farbe'  => array(
        array(
            'id'   => 3,
            'name' => 'Gelb'
        )
    )
);

Ich möchte es aber nicht für jedes Buch eine MySQL abfrage erstellen, welchen Autor etc es hat. Das würde die DB zu stark belasten.

Wie kriege ich die SQL Abfrage hin, und wie das Ergebniss mit PHP in einen Array abfüllen?

Hier der Deom SQL Code:

CREATE TABLE buch
    (
     buch_id int auto_increment primary key,
     name varchar(10),
     iban varchar(10)
    );

INSERT INTO buch
(buch_id, name, iban)
VALUES
('1', 'BuchABC', '12341234'),
('2', 'Buch123', '43214321'),
('3', 'BuchXYZ', '44443333'),
('4', 'Buch456', '66667777');


CREATE TABLE buchautor_name
    (
     buchautor_name_id int primary key,
     name varchar(10)
    );

INSERT INTO buchautor_name
(buchautor_name_id, name)
VALUES
('1', 'AutorABC'),
('2', 'Autor123'),
('3', 'AutorXYZ');

CREATE TABLE verlags_name
    (
     verlags_name_id int primary key,
     name varchar(10)
    );

INSERT INTO verlags_name
(verlags_name_id, name)
VALUES
('1', 'VerlagABC'),
('2', 'Verlag123'),
('3', 'VerlagXYZ');

CREATE TABLE farben_name
    (
     farben_name_id int primary key,
     name varchar(10)
    );

INSERT INTO farben_name
(farben_name_id, name)
VALUES
('1', 'Blau'),
('2', 'Rot'),
('3', 'Gelb');



CREATE TABLE buchautor_join
    (
     buchautor_join_id int primary key,
     buch_id int(11)
     buchautor_name_id int(11)
    );

INSERT INTO buchautor_join
(buchautor_join_id, buch_id, buchautor_name_id)
VALUES
('1', '1', '2'),
('2', '1', '3'),
('3', '2', '1'),
('4', '2', '3'),
('5', '3', '2'),
('6', '4', '1');

CREATE TABLE verlags_join
    (
     verlags_join_id int primary key,
     buch_id int(11)
     verlags_name_id int(11)
    );

INSERT INTO verlags_join
(verlags_join_id, buch_id, verlags_name_id)
VALUES
('1', '1', '1'),
('2', '1', '2'),
('3', '1', '3'),
('4', '3', '1'),
('5', '3', '1');

CREATE TABLE farben_join
    (
     farben_join_id int primary key,
     buch_id int(11)
     farben_name_id int(11)
    );

INSERT INTO farben_join
(farben_join_id, buch_id, farben_name_id)
VALUES
('1', '2', '3'),
('2', '3', '1'),
('3', '3', '3'),
('4', '4', '1'),
('5', '4', '2'),
('6', '4', '3');

Dankbar um jede Ideee !

 

[Yaslaw]

Ein Mehrstufige Tabelle kriegst du mit SQL nicht hin. Die Ausgabe ist immer eine flache Tabelle.
Ich würde:
1) Alle Bücher in einen Array schreiben mit der buch_id als key
2) Ein JOIN-SQL auf buchautor_name und buchautor_name öffnen und damit den Bucharray ergänzen

Ungetestet:

$result = mysqli_query($dblink, 'SELECT *  FROM buch');
$books = array
while ($book = mysqli_fetch_assoc($result)) {
    $book['autor'] = array();
    $books[$book['buch_id']] = $book;
}
$result->close();

$sql = <<<SQL
  SELECT
    j.buch_id,
    n.buchautor_name_id,
    n.buchautor_name
  FROM
    buchautor_name n,
    buchautor_join j
  WHERE
    n.buchautor_name_id = j.buchautor_name_id
SQL;

$result = mysqli_query($dblink, $sql);
while ($autor = mysqli_fetch_assoc($result)) {
    $books[$autor['buch_id']]['autor']['id'] = $autor['buchautor_name_id'];
    $books[$autor['buch_id']]['autor']['name'] = $autor['buchautor_name'];
}
$result->close();

 

[slimox]

Danke für dein Tipp. Das würde bei 500 "Bücher" jedoch bedeuten, dass pro Buch 3 Abfragen also Total 1501 Abfragen an die DB gesendet werden, was etwas viel ist?
Gibt es nicht eine Möglichkeit, z.B eine oder mehrere Views zu erstellen und so die Anzahl Abfragen an die DB zu reduzieren?

 

[Yaslaw]

Nö. Jede Abfrage ist für alle Bücher.
Eine Abfrage für die Bücher, eine für die Autoren, eine für die Verlage und eine für die Farbe = 4 Abfragen für ALLE Daten.

Wo siehst du in meinem Code, dass ich pro Buch eine Abfrage ausführe?

 

[slimox]

Hehe, ich habe den Code nicht getestet und es falsch verstanden Werde Ihn testen und wenn es funkt, dann schreibe ich den fertigen Code Danke dir!

 

[slimox]

Nun habe ich mir eine Lösung erstellt, in dem ich deine Idee etwas erweitert habe. Da ich mit der ersten Abfrage nicht alle Bücher ausgeben will, soll es auch nicht alle Autoren laden. Dazu habe ich den Array $bucherIDs erstellt. In der ersten foreach() wird jede buch_id in den Array gelegt. So kann ich bei der zweiten Abfrage für alle Autoren in der SQL Abfrage ein IN(buch_id, buch_id, ..) hinzufügen.
Die Lösung ist nicht so sexy wie ich gehofft habe, haber sie funktioniert.