mitmario.dev

Zwei Tabellen

PHP Sandbox 5 Min Lesezeit 4 BeispieleLektion 7 von 9

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

Ein Name in jeder Zeile
<?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

Zwei Tabellen, ein JOIN
<?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

Was der Fremdschlüssel abweist
<?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.

Die Verbundtabelle als Seite
<?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, 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.