mitmario.dev

Abschnitt 14 · Lektion 7

Zwei Tabellen

PHP Sandbox 5 Min Lesezeit 4 BeispieleLektion 7 von 9

Bisher stand in jeder Notiz der Name des Verfassers. Das liest sich gut und funktioniert bis zu dem Tag, an dem jemand heiratet.

Warum derselbe Name nicht in jeder Zeile stehen sollte

Ein Name in jeder Zeile 
<?php

// Eine einzige Tabelle, der Name des Verfassers in jeder Zeile.
// Diese Datei braucht kein vorbereiten.php, sie baut sich selbst.

$db = new PDO("sqlite:" . __DIR__ . "/flach.db", null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$db->exec("DROP TABLE IF EXISTS notizen");
$db->exec("CREATE TABLE notizen (id INTEGER PRIMARY KEY, titel TEXT NOT NULL, verfasser TEXT NOT NULL)");

$einfuegen = $db->prepare("INSERT INTO notizen (titel, verfasser) VALUES (?, ?)");

foreach ([
    ["Milch", "Mia Berger"],
    ["Brot", "Ben Kraus"],
    ["Kaffee", "Mia Berger"],
    ["Tee", "Mia Bergr"],
    ["Seife", "Mia Berger"],
] as $zeile) {
    $einfuegen->execute($zeile);
}

function zaehlen(PDO $db): void
{
    foreach ($db->query("SELECT verfasser, count(*) AS anzahl FROM notizen GROUP BY verfasser ORDER BY verfasser") as $z) {
        echo "  ", str_pad($z["verfasser"], 14), "Notizen: ", $z["anzahl"], "\n";
    }
}

echo "Wer hat wie viele Notizen?\n";
zaehlen($db);

echo "\nMia heisst jetzt anders. Das kostet ein UPDATE ueber mehrere Zeilen:\n";

$aendern = $db->prepare("UPDATE notizen SET verfasser = :neu WHERE verfasser = :alt");
$aendern->execute([":neu" => "Mia Falk", ":alt" => "Mia Berger"]);

echo "  geaendert: ", $aendern->rowCount(), "\n";

echo "\nUnd danach:\n";
zaehlen($db);

echo "\nEine Zeile ist nicht mitgekommen, und du siehst auch, welche.\n";

Im Beispiel steht der Name in jeder Zeile, und zwei Dinge gehen schief. Erstens hat sich in einer Zeile ein Tippfehler eingeschlichen, Mia Bergr, und für die Datenbank sind das zwei verschiedene Menschen. Die Zählung zeigt es sofort: drei plus eins statt vier.

Zweitens kostet die Umbenennung ein UPDATE über mehrere Zeilen, und es erwischt genau die Zeilen nicht, in denen der Name anders geschrieben ist. Nach dem UPDATE gibt es Mia Falk und Mia Bergr, und die gehören zusammen, ohne dass irgendetwas das noch wüsste.

Beides hat dieselbe Ursache: Wie Mia heißt, steht in vier der fünf Zeilen, und vier Stellen können auseinanderlaufen. Im Beispiel sind sie es schon. Die Lösung ist, die Tatsache an genau eine Stelle zu legen und überall sonst darauf zu zeigen.

Aufteilen und wieder zusammenfügen

Zwei Tabellen, ein JOIN 
<?php

// Dieselben Daten, auf zwei Tabellen verteilt und wieder zusammengefuegt.

require __DIR__ . "/vorbereiten.php";

echo "Die zwei Tabellen fuer sich:\n\n";

echo "  verfasser\n";
foreach ($db->query("SELECT * FROM verfasser ORDER BY id") as $z) {
    echo "    ", $z["id"], "  ", $z["name"], "\n";
}

echo "\n  notizen\n";
foreach ($db->query("SELECT * FROM notizen ORDER BY id") as $z) {
    echo "    ", $z["id"], "  ", str_pad($z["titel"], 8), "verfasser_id=", $z["verfasser_id"], "\n";
}

echo "\nEin JOIN fuegt sie wieder zusammen:\n";

$sql = "SELECT n.titel, v.name
    FROM notizen n
    JOIN verfasser v ON v.id = n.verfasser_id
    ORDER BY n.id";

foreach ($db->query($sql) as $z) {
    echo "  ", $z["titel"], " (", $z["name"], ")\n";
}

echo "\nEin LEFT JOIN von den Verfassern aus behaelt auch Lea, die keine hat:\n";

$links = "SELECT v.name, count(n.id) AS anzahl
    FROM verfasser v
    LEFT JOIN notizen n ON n.verfasser_id = v.id
    GROUP BY v.id
    ORDER BY v.id";

foreach ($db->query($links) as $z) {
    echo "  ", str_pad($z["name"], 6), "Notizen: ", $z["anzahl"], "\n";
}

echo "\nVorsicht mit SELECT *, wenn beide Tabellen eine Spalte id haben.\n";
echo "Notiz 3 gehoert Verfasser 1, und unter id steht die 1:\n";

print_r($db->query("SELECT * FROM notizen n JOIN verfasser v ON v.id = n.verfasser_id WHERE n.id = 3")->fetch());

Die Tabelle verfasser hält jeden Namen genau einmal, mit einer id. Die Tabelle notizen merkt sich statt des Namens nur noch diese Nummer, in einer Spalte verfasser_id. Eine solche Spalte heißt Fremdschlüssel: Sie enthält den Primärschlüssel einer anderen Tabelle.

Jetzt gibt es den Namen nur noch einmal. Eine Umbenennung ist ein UPDATE auf einer einzigen Zeile, ein Tippfehler kann nicht zwei Personen erzeugen, und die Notizen merken davon gar nichts, weil sie nur die Nummer kennen.

Der Preis ist, dass die Anzeige die beiden Tabellen wieder zusammenführen muss, und dafür gibt es JOIN. Lies die Abfrage aus dem Beispiel von oben nach unten: FROM notizen n ist die Tabelle, aus der du losgehst, und n ist ein Kurzname dafür. JOIN verfasser v ist die Tabelle, die dazukommt. ON v.id = n.verfasser_id ist die Bedingung, nach der eine Zeile links zu einer Zeile rechts passt. Danach kannst du Spalten aus beiden auswählen, als wären sie eine Tabelle.

Die ON-Bedingung ist der wichtige Teil, und sie darf nicht fehlen. Ohne sie kombiniert die Datenbank jede Zeile links mit jeder Zeile rechts. Bei drei und drei sind das neun Zeilen, bei tausend und tausend eine Million.

Ein JOIN behält nur, was auf beiden Seiten einen Partner hat. Lea taucht in der ersten Abfrage also nicht auf, sie hat ja keine Notiz. Willst du sie trotzdem sehen, nimmst du einen LEFT JOIN: Der behält alles von der linken Tabelle und füllt die rechte Seite mit NULL, wo nichts passt. Genau deshalb steht bei Lea eine 0.

Und eine Falle für später: Bei SELECT * über einen JOIN haben beide Tabellen eine Spalte id, und in dem Array, das du zurückbekommst, überlebt nur eine davon. Im Beispiel gehört Notiz 3 zu Verfasser 1, und unter id steht die 1, also die des Verfassers. Deshalb schreibst du bei einem JOIN die Spalten aus, die du wirklich brauchst, und gibst gleichnamigen mit AS einen eigenen Namen.

Der Fremdschlüssel ist bei SQLite eine Bitte, bis du ihn einschaltest

Was der Fremdschlüssel abweist 
<?php

// Derselbe Fehler, zweimal versucht. Dazwischen wird eine Zeile
// eingeschaltet.

require __DIR__ . "/vorbereiten.php";

echo "Die Vorgabe: PRAGMA foreign_keys ist ";
echo $db->query("PRAGMA foreign_keys")->fetchColumn(), "\n\n";

echo "Eine Notiz mit Verfasser 99, den es nicht gibt:\n";
echo "  ohne Pragma: ";

try {
    $db->exec("INSERT INTO notizen (titel, verfasser_id) VALUES ('Geist', 99)");
    echo "angenommen\n";
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}

$db->exec("DELETE FROM notizen WHERE titel = 'Geist'");

$db->exec("PRAGMA foreign_keys = ON");

echo "  mit Pragma:  ";

try {
    $db->exec("INSERT INTO notizen (titel, verfasser_id) VALUES ('Geist', 99)");
    echo "angenommen\n";
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}

echo "\nUnd andersherum: Verfasser 1 loeschen, der noch zwei Notizen hat:\n";
echo "  ";

try {
    $db->exec("DELETE FROM verfasser WHERE id = 1");
    echo "angenommen\n";
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}

echo "\nDie Notizen von Verfasser 1 sind also noch da: ";
echo $db->query("SELECT count(*) FROM notizen WHERE verfasser_id = 1")->fetchColumn(), "\n";

Bis hierher war REFERENCES verfasser(id) eine reine Beschreibung. SQLite prüft Fremdschlüssel nämlich nur, wenn man es ausdrücklich einschaltet, und die Vorgabe ist aus. Das siehst du in der ersten Zeile des Beispiels, dort steht eine 0.

Ohne die Prüfung geht eine Notiz mit verfasser_id = 99 glatt durch, obwohl es keinen Verfasser 99 gibt. Später fällt sie beim JOIN einfach heraus, ohne dass jemand merkt, dass sie überhaupt existiert.

Die Zeile, die das ändert, ist $db->exec("PRAGMA foreign_keys = ON"), und sie gehört direkt hinter die Verbindung. Wichtig dabei: Sie gilt je Verbindung und nicht für die Datei. Jedes Skript, das diese Datenbank öffnet, muss sie selbst setzen, und ein Skript, das sie vergisst, darf weiterhin Unsinn hineinschreiben. Du kannst das im Terminal nachprüfen, gleich nachdem das Beispiel gelaufen ist: php -r 'echo (new PDO("sqlite:daten.db"))->query("PRAGMA foreign_keys")->fetchColumn();' antwortet mit 0, denn das ist eine neue Verbindung.

Danach weist SQLite in beide Richtungen ab. Eine Notiz mit einem Verfasser, den es nicht gibt, kommt nicht hinein. Und ein Verfasser, der noch Notizen hat, lässt sich nicht löschen, denn sonst zeigten die Notizen ins Leere. Beides meldet sich als FOREIGN KEY constraint failed.

Der historische Grund für die Vorgabe ist übrigens schlicht Rücksicht: SQLite kannte Fremdschlüssel lange nicht, und als sie dazukamen, hätte das Einschalten ältere Programme kaputtgemacht. Bei MySQL und PostgreSQL stellt sich die Frage nicht, dort prüfen Fremdschlüssel immer.

Wann Aufteilen zu weit geht

Nicht jede Wiederholung ist ein Fehler. Aufteilen lohnt sich, wenn dieselbe Sache mehrfach vorkommt und sich ändern kann. Der Verfassername erfüllt beides.

Ein Datum, an dem etwas passiert ist, erfüllt es nicht: Es wiederholt sich zwar, ändert sich aber nie, weil es zu genau diesem Ereignis gehört. Und eine Statusbezeichnung wie „offen” oder „erledigt” in eine eigene Tabelle zu legen, ist meistens Aufwand ohne Gewinn. Dafür gibt es seit Lektion 13.2 ein Enum.

Die Verbundtabelle als Seite 
<?php

// Dieselbe Verbundabfrage, diesmal als Seite. Sieh im Reiter Browser hin.

require __DIR__ . "/vorbereiten.php";

$zeilen = $db->query("SELECT v.name, count(n.id) AS anzahl
    FROM verfasser v
    LEFT JOIN notizen n ON n.verfasser_id = v.id
    GROUP BY v.id
    ORDER BY v.name")->fetchAll();

?><style>
    body { font-family: system-ui, sans-serif; padding: 1.5rem; }
    table { border-collapse: collapse; }
    th, td { border: 1px solid #999; padding: 0.4rem 0.9rem; text-align: left; }
    th { background: #eee; }
    td.zahl { text-align: right; }
</style>

<h1>Notizen je Verfasser</h1>

<table>
    <tr><th>Verfasser</th><th>Notizen</th></tr>
<?php foreach ($zeilen as $zeile) { ?>
    <tr>
        <td><?= htmlspecialchars($zeile["name"]) ?></td>
        <td class="zahl"><?= $zeile["anzahl"] ?></td>
    </tr>
<?php } ?>
</table>

Das letzte Beispiel ist dieselbe Abfrage von eben, nur gibt sie eine kleine HTML-Seite aus. Im Reiter Browser siehst du das Ergebnis als Tabelle, und darum steht es hier: Ein Verbund ist eine Tabelle, und als Tabelle versteht man ihn in zwei Sekunden. In den Lektionen davor läuft kein Server, weil dort die Ausgabe Text ist und kein Markup; ab Lektion 14.8 ist die Notizliste wieder eine Seite.

Damit ist genug SQL für diesen Kurs beisammen. Was es sonst noch gibt, GROUP BY mit HAVING, Unterabfragen, Indizes, Sichten, mehrere JOIN hintereinander, gehört in einen eigenen Kurs über SQL. Für eine Anwendung wie die, die du hier baust, kommst du mit dem aus, was du jetzt kennst.

Zum Mitnehmen

Bisher lag alles in einer Tabelle. Sobald zwei Zeilen dasselbe über dieselbe Person sagen, ist das eine Zeile zu viel. Diese Lektion teilt auf und fügt wieder zusammen, und ihr letztes Beispiel ist die erste Seite in diesem Abschnitt.

Jetzt du

Basis Konto, kostenlos

Zu dieser Lektion gehört eine Aufgabe. Du schreibst den Code selbst, und nach jedem Lauf sagt dir eine Prüfliste, was schon stimmt.

Dafür brauchst du das Basis Konto. Es kostet nichts, und ein Passwort gibt es auch nicht.

In diesem Kurs läuft dein Code auf einem Server. Dafür hat das Basis Konto 1 Stunde im Monat, mehr Zeit gibt es mit dem Premium Konto.

Was in dieser Lektion steckt

  • Artikel mit 4 Beispielen zum Ausprobieren

    Steht hier, ohne Konto lesbar.

  • Aufgabe, dein Code läuft auf einem Server

    Öffnet sich mit dem Basis Konto.