Abschnitt 14 · Lektion 7
Zwei Tabellen
Steht der Name des Verfassers in jeder Notiz, liest sich die Tabelle gut. Das hält bis zu dem Tag, an dem jemand heiratet.
Warum derselbe Name nicht in jeder Zeile stehen sollte
<?php
declare(strict_types=1);
// 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 heißt jetzt anders. Das kostet ein UPDATE über mehrere Zeilen:\n";
$aendern = $db->prepare("UPDATE notizen SET verfasser = :neu WHERE verfasser = :alt");
$aendern->execute([":neu" => "Mia Falk", ":alt" => "Mia Berger"]);
echo " geändert: ", $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. In einer Zeile hat sich ein
Tippfehler eingeschlichen, Mia Bergr, und für die Datenbank sind das zwei verschiedene Menschen.
Gezählt kommen deshalb drei Notizen für Mia Berger und eine für Mia Bergr heraus statt vier.
GROUP BY verfasser fasst dabei alle Zeilen mit demselben Namen zu einer zusammen, und count(*)
zählt, wie viele es waren.
Die Umbenennung kostet außerdem ein UPDATE über mehrere Zeilen, und das erwischt die Zeilen nicht,
in denen der Name anders geschrieben ist. Danach gibt es Mia Falk und Mia Bergr. Dass die beiden
zusammengehören, steht nirgends mehr.
Beides kommt daher, dass Mias Name in vier der fünf Zeilen steht. Vier Stellen können auseinanderlaufen, und im Beispiel sind sie es schon.
Aufteilen und wieder zusammenfügen
<?php
declare(strict_types=1);
// Zwei Tabellen: drei Verfasser und drei Notizen, die auf sie zeigen.
// Bei jedem Lauf neu aufgebaut, an dieser Datei aenderst du nichts.
//
// Die Fremdschluessel schaltet sie mit Absicht nicht ein.
$db = new PDO("sqlite:" . __DIR__ . "/daten.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("DROP TABLE IF EXISTS verfasser");
$db->exec("CREATE TABLE verfasser (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
)");
$db->exec("CREATE TABLE notizen (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
verfasser_id INTEGER NOT NULL REFERENCES verfasser(id)
)");
$neuerVerfasser = $db->prepare("INSERT INTO verfasser (name) VALUES (?)");
foreach (["Mia", "Ben", "Lea"] as $name) {
$neuerVerfasser->execute([$name]);
}
$neueNotiz = $db->prepare("INSERT INTO notizen (titel, verfasser_id) VALUES (?, ?)");
foreach ([["Milch", 1], ["Brot", 2], ["Kaffee", 1]] as $notiz) {
$neueNotiz->execute($notiz);
} <?php
declare(strict_types=1);
// Dieselben Daten, auf zwei Tabellen verteilt und wieder zusammengefuegt.
require __DIR__ . "/vorbereiten.php";
echo "Die zwei Tabellen für 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 fügt 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 behält 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 gehört 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()); In der Tabelle verfasser steht jeder Name ein einziges Mal, mit einer id. In der Tabelle
notizen steht statt des Namens nur noch diese Nummer, in einer Spalte verfasser_id, und sie
zeigt damit auf den Verfasser. So eine Spalte heißt Fremdschlüssel (englisch foreign key). Sie
enthält den Primärschlüssel einer anderen Tabelle.
Den Namen gibt es jetzt nur noch einmal. Eine Umbenennung ist ein UPDATE auf einer einzigen Zeile,
und ein Tippfehler kann keine zweite Person erzeugen. An den Notizen ändert sich dabei gar nichts,
denn dort steht nur die Nummer.
Dafür muss die Anzeige die beiden Tabellen wieder zusammenführen, und das macht JOIN. Die Abfrage
aus dem Beispiel liest sich von oben nach unten. FROM notizen n ist die Tabelle, von der aus es
losgeht, 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 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. Ein LEFT JOIN behält dagegen alles von der linken Tabelle
und füllt die rechte Seite mit NULL, wo nichts passt. Gruppiert wird diesmal nach v.id, und
gezählt wird count(n.id) statt count(*), weil NULL dort nicht mitzählt. Deshalb steht bei Lea
eine 0 und keine 1.
Bei SELECT * über einen JOIN haben beide Tabellen eine Spalte id, und im Array, das
zurückkommt, bleibt nur eine davon übrig. Im Beispiel gehört Notiz 3 zu Verfasser 1, und unter id
steht die 1, also die des Verfassers. Brauchst du beide Nummern, schreibst du die Spalten aus und
gibst einer davon mit AS einen eigenen Namen, etwa n.id AS notiz_id.
Der Fremdschlüssel ist bei SQLite eine Bitte, bis du ihn einschaltest
<?php
declare(strict_types=1);
// Zwei Tabellen: drei Verfasser und drei Notizen, die auf sie zeigen.
// Bei jedem Lauf neu aufgebaut, an dieser Datei aenderst du nichts.
//
// Die Fremdschluessel schaltet sie mit Absicht nicht ein.
$db = new PDO("sqlite:" . __DIR__ . "/daten.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("DROP TABLE IF EXISTS verfasser");
$db->exec("CREATE TABLE verfasser (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
)");
$db->exec("CREATE TABLE notizen (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
verfasser_id INTEGER NOT NULL REFERENCES verfasser(id)
)");
$neuerVerfasser = $db->prepare("INSERT INTO verfasser (name) VALUES (?)");
foreach (["Mia", "Ben", "Lea"] as $name) {
$neuerVerfasser->execute([$name]);
}
$neueNotiz = $db->prepare("INSERT INTO notizen (titel, verfasser_id) VALUES (?, ?)");
foreach ([["Milch", 1], ["Brot", 2], ["Kaffee", 1]] as $notiz) {
$neueNotiz->execute($notiz);
} <?php
declare(strict_types=1);
// Derselbe Fehler, zweimal versucht. Dazwischen wird eine Zeile
// eingeschaltet.
require __DIR__ . "/vorbereiten.php";
echo "Der Standardwert: 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 $fehler) {
echo $fehler->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 $fehler) {
echo $fehler->getMessage(), "\n";
}
echo "\nUnd andersherum: Verfasser 1 löschen, der noch zwei Notizen hat:\n";
echo " ";
try {
$db->exec("DELETE FROM verfasser WHERE id = 1");
echo "angenommen\n";
} catch (PDOException $fehler) {
echo $fehler->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"; Von sich aus ist REFERENCES verfasser(id) bei SQLite eine reine Beschreibung. Geprüft werden
Fremdschlüssel nur, wenn man es ausdrücklich einschaltet, und der Standardwert ist aus. In der
ersten Zeile des Beispiels steht dafür 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, und niemand merkt, dass es sie überhaupt gibt.
Eingeschaltet wird mit $db->exec("PRAGMA foreign_keys = ON"), direkt hinter der Verbindung. Dieses
PRAGMA gilt für die Verbindung und nicht für die Datei. Jedes Skript, das diese Datenbank öffnet,
muss es selbst setzen. Ein Skript, das es vergisst, darf weiter Unsinn hineinschreiben. Fragst du
nach dem Beispiel in der Befehlsleiste mit einer neuen Verbindung PRAGMA foreign_keys ab, kommt
wieder 0 zurück.
Mit der Prüfung 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, sonst
zeigten die Notizen ins Leere. Beides meldet sich als FOREIGN KEY constraint failed.
SQLite kannte Fremdschlüssel lange nicht, und als sie dazukamen, hätte das Einschalten ältere Programme kaputtgemacht. Bei PostgreSQL ist die Prüfung immer an. Bei MySQL ist sie von Haus aus auch an, solange die Tabellen die übliche Speicherart InnoDB haben.
Wann Aufteilen zu weit geht
Nicht jede Wiederholung ist ein Fehler. Aufteilen lohnt sich, wenn dieselbe Sache mehrfach vorkommt und sich ändern kann, und der Verfassername erfüllt beides.
Ein Datum, an dem etwas passiert ist, erfüllt es nicht. Es wiederholt sich zwar, ändert sich aber nie. Eine Statusbezeichnung wie „offen“ oder „erledigt“ in eine eigene Tabelle zu legen, ist meistens Aufwand ohne Gewinn, denn dafür gibt es ein Enum aus dem Abschnitt über Objekte.
<?php
declare(strict_types=1);
// Zwei Tabellen: drei Verfasser und drei Notizen, die auf sie zeigen.
// Bei jedem Lauf neu aufgebaut, an dieser Datei aenderst du nichts.
//
// Die Fremdschluessel schaltet sie mit Absicht nicht ein.
$db = new PDO("sqlite:" . __DIR__ . "/daten.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("DROP TABLE IF EXISTS verfasser");
$db->exec("CREATE TABLE verfasser (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
)");
$db->exec("CREATE TABLE notizen (
id INTEGER PRIMARY KEY,
titel TEXT NOT NULL,
verfasser_id INTEGER NOT NULL REFERENCES verfasser(id)
)");
$neuerVerfasser = $db->prepare("INSERT INTO verfasser (name) VALUES (?)");
foreach (["Mia", "Ben", "Lea"] as $name) {
$neuerVerfasser->execute([$name]);
}
$neueNotiz = $db->prepare("INSERT INTO notizen (titel, verfasser_id) VALUES (?, ?)");
foreach ([["Milch", 1], ["Brot", 2], ["Kaffee", 1]] as $notiz) {
$neueNotiz->execute($notiz);
} <?php
declare(strict_types=1);
// Dieselbe Verbundabfrage, diesmal als Seite im Reiter Browser.
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();
?>
<!doctype html>
<html lang="de">
<head>
<meta charset="utf-8">
<title>Notizen je Verfasser</title>
<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>
</head>
<body>
<h1>Notizen je Verfasser</h1>
<table>
<tr><th>Verfasser</th><th>Notizen</th></tr>
<?php foreach ($zeilen as $zeile) { ?>
<tr>
<td><?= htmlspecialchars($zeile["name"], ENT_QUOTES, "UTF-8") ?></td>
<td class="zahl"><?= $zeile["anzahl"] ?></td>
</tr>
<?php } ?>
</table>
</body>
</html> Im letzten Beispiel steht dieselbe Abfrage mit dem LEFT JOIN, nur kommt diesmal eine kleine
HTML-Seite heraus. Im Reiter Browser siehst du das Ergebnis als Tabelle. Ein Verbund ist eine
Tabelle, und als Tabelle liest man ihn in zwei Sekunden. In den Lektionen davor läuft kein Server,
weil die Ausgabe dort Text ist und kein Markup. In der nächsten Lektion wird die Notizliste wieder
eine Seite.
Weiteres SQL wie HAVING, Unterabfragen, Indizes, Sichten oder mehrere JOIN hintereinander gehört
in einen eigenen Kurs über SQL. Die Notizliste kommt ohne aus.
In der Aufgabe kommen Notizen und Verfasser über einen JOIN zusammen, und die Fremdschlüssel
werden eingeschaltet.
Zum Mitnehmen
Was öfter vorkommt und sich ändern kann, bekommt eine eigene Tabelle, und ein JOIN mit ON führt beides wieder zusammen. Fremdschlüssel prüft SQLite erst nach PRAGMA foreign_keys = ON, und das gilt je Verbindung.
Jetzt du
Basis Konto, kostenlosZu 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.