Inhalt

Implementieren des CRUD-Prinzips in PHP und MySQL

Das MySQL Datenbank-Management-System

MySQL ist ein relationales Datenbank-Management-System (RDBMS), d. h. es verwendet SQL (Structured Query Language), um Daten in tabellarischer Form zu speichern und zu verwalten.

Es gibt eine Vielzahl an unterschiedlichen Datenbanken. MySQL (bzw. der Ableger MariaDB) ist eine Open-Source-Datenbank, die häufig mit PHP verwendet wird, um Daten auf Websites zu speichern und abzurufen.

Verbindung mit PDO

PHP bietet mit PDO (PHP Data Objects) eine einheitliche Schnittstelle für Datenbanken. Der Zugriff funktioniert für MySQL, SQLite, PostgreSQL usw. gleich, nur die Verbindungszeichenkette (DSN) unterscheidet sich.

Um mit MySQL zu arbeiten, benötigt man zunächst eine Verbindung zur Datenbank:

<?php
  $host = "localhost";
  $dbname = "database_name";
  $username = "username";
  $password = "password";

  // DSN (Data Source Name): Datenbanktyp, Host, Datenbankname und Zeichensatz
  $dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

  try {
    // Verbindung herstellen
    $pdo = new PDO($dsn, $username, $password, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,        // Fehler als Exceptions melden
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,   // Ergebnisse als assoziative Arrays
    ]);
    echo "Verbindung hergestellt";
  } catch (PDOException $e) {
    echo "Verbindung fehlgeschlagen: " . $e->getMessage();
  }
?>

Aufbauen der Verbindung mit PDO

Die Verbindung wird üblicherweise in eine eigene Datei db.php ausgelagert und in jeder Seite mit require "db.php"; eingebunden. So stehen die Zugangsdaten nur an einer Stelle.

Zugangsdaten auf coding-space.at

Wir arbeiten auf coding-space.at. Dort stehen PHP und eine MySQL-Datenbank bereit. Host, Datenbankname, Benutzer und Passwort werden im Unterricht bekanntgegeben.

Aufbau einer Datenbank-Tabelle

Eine Datenbank liegt auf einem Datenbank-Server. Auf einem solchen Server können mehrere Datenbanken gehostet werden. Eine Datenbank besteht aus einer oder mehreren Tabellen.

Eine Tabelle in einer MySQL-Datenbank ist eine Struktur, die bestimmte Daten speichert. Eine Tabelle besteht aus Zeilen und Spalten, wobei jede Spalte einen bestimmten Datentyp aufweist und jede Zeile einen Eintrag enthält.

Um eine Tabellenstruktur in MySQL anzuzeigen, kann die DESCRIBE-Anweisung verwendet werden. Hier ist ein Beispiel, um die Struktur der users-Tabelle anzuzeigen:

DESCRIBE users;

Die Ausgabe könnte wie folgt aussehen:

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)  | NO   |     | NULL    |                |
| email    | varchar(50)  | NO   |     | NULL    |                |
| password | varchar(255) | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

Diese Ausgabe zeigt die Namen der Spalten, ihre Datentypen und weitere Informationen wie NULL-Werte, Schlüssel und Standardwerte an. Jede Zeile in der Ausgabe stellt eine Spalte der Tabelle dar und enthält folgende Informationen:

SQL-Statements

SQL-Statements sind Anweisungen, die an eine MySQL-Datenbank gesendet werden, um bestimmte Operationen auszuführen. Mit der SELECT-Anweisung werden Einträge aus einer Tabelle abgerufen, mit INSERT neue Einträge hinzugefügt, mit UPDATE bestehende Einträge aktualisiert und mit DELETE Einträge gelöscht.

Prepared Statements

Werte, die von Benutzern kommen, dürfen nie direkt in ein SQL-Statement eingebaut werden. Sonst kann über ein Formularfeld eigener SQL-Code eingeschleust werden (SQL-Injection):

// GEFÄHRLICH: Eingabe  ' OR 1=1 --  liefert alle Benutzer
$sql = "SELECT * FROM users WHERE name = '" . $_GET["name"] . "'";

Mit Prepared Statements werden SQL-Befehl und Daten getrennt an die Datenbank geschickt. Im Statement stehen Platzhalter (:name oder ?), die Werte werden erst beim Ausführen eingesetzt und können den Befehl nicht mehr verändern:

// Statement mit Platzhaltern vorbereiten
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name");

// Statement mit den tatsächlichen Werten ausführen
$stmt->execute(["name" => $_GET["name"]]);

// Ergebnis abholen
$users = $stmt->fetchAll();

Hier ist ein Beispiel für die vier Grundoperationen:

// Neuen Benutzer hinzufügen
$stmt = $pdo->prepare("INSERT INTO users (name, email, password) VALUES (?, ?, ?)");
$stmt->execute(["John", "john@example.com", password_hash("geheim", PASSWORD_DEFAULT)]);

// Benutzer abrufen
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?");
$stmt->execute(["John"]);
$user = $stmt->fetch();

// Benutzer aktualisieren
$stmt = $pdo->prepare("UPDATE users SET name = ? WHERE name = ?");
$stmt->execute(["John Doe", "John"]);

// Benutzer löschen
$stmt = $pdo->prepare("DELETE FROM users WHERE name = ?");
$stmt->execute(["John Doe"]);

In diesem Beispiel wird ein neuer Benutzer in der users-Tabelle hinzugefügt, anschließend wird der Benutzer abgerufen, aktualisiert und schließlich gelöscht.

Statements ohne Benutzereingaben können direkt mit $pdo->query() ausgeführt werden:

$users = $pdo->query("SELECT * FROM users ORDER BY name")->fetchAll();

Das CRUD-Prinzip

CRUD steht für Create, Read, Update und Delete und ist ein Akronym für die vier Grundoperationen, die auf Datenbanken ausgeführt werden können. Diese Operationen sind:

Das CRUD-Prinzip ist ein grundlegender Ansatz zum Verwalten von Daten in einer Datenbank. Hier sind einige Beispiele dafür, wie das CRUD-Prinzip mit PHP und MySQL umgesetzt werden kann. In allen Beispielen wird die Verbindung aus db.php eingebunden.

CREATE

Um einen neuen Eintrag in einer MySQL-Datenbank hinzuzufügen, verwenden wir die INSERT-Anweisung. Hier ist ein Beispiel, um einen neuen Benutzer aus einem Formular in die Datenbank einzutragen:

<?php
  require "db.php";

  // Formulardaten abrufen
  $name = $_POST["name"];
  $email = $_POST["email"];
  $password = password_hash($_POST["password"], PASSWORD_DEFAULT);

  // Benutzer in Datenbank hinzufügen
  $stmt = $pdo->prepare("INSERT INTO users (name, email, password) VALUES (:name, :email, :password)");
  $stmt->execute([
    "name" => $name,
    "email" => $email,
    "password" => $password,
  ]);

  echo "Neuer Benutzer mit der ID " . $pdo->lastInsertId() . " angelegt.";
?>

READ

Um Einträge aus einer MySQL-Datenbank abzurufen, verwenden wir die SELECT-Anweisung. Hier ist ein Beispiel, um alle Benutzer aus der Datenbank abzurufen:

<?php
  require "db.php";

  // Benutzer abrufen
  $stmt = $pdo->query("SELECT * FROM users");

  // Benutzer durchlaufen
  foreach ($stmt as $row) {
    echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
    echo "E-Mail: " . htmlspecialchars($row["email"]) . "<br>";
  }
?>

Mit fetch() wird eine einzelne Zeile geholt, mit fetchAll() alle Zeilen als Array:

  $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
  $stmt->execute([$_GET["id"]]);
  $user = $stmt->fetch();   // eine Zeile als assoziatives Array oder false

  $users = $pdo->query("SELECT * FROM users")->fetchAll();   // Array aller Zeilen

UPDATE

Um Einträge in einer MySQL-Datenbank zu aktualisieren, verwenden wir die UPDATE-Anweisung. Hier ist ein Beispiel, um den Namen eines Benutzers zu aktualisieren:

<?php
  require "db.php";

  // Formulardaten abrufen
  $id = $_POST["id"];
  $name = $_POST["name"];

  // Benutzer in Datenbank aktualisieren
  $stmt = $pdo->prepare("UPDATE users SET name = :name WHERE id = :id");
  $stmt->execute(["name" => $name, "id" => $id]);

  echo $stmt->rowCount() . " Eintrag geändert.";
?>

DELETE

Um Einträge aus einer MySQL-Datenbank zu löschen, verwenden wir die DELETE-Anweisung. Hier ist ein Beispiel, um einen Benutzer aus der Datenbank zu löschen:

<?php
  require "db.php";

  // Formulardaten abrufen
  $id = $_POST["id"];

  // Benutzer aus Datenbank löschen
  $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
  $stmt->execute([$id]);
?>

Aufgaben

Erstelle eine Web-Applikation zur Verwaltung von Kontaktdaten.

1. Anlegen der Tabelle

Lege in deiner Datenbank auf coding-space.at eine Tabelle mit dem Namen user und folgenden Feldern an (mit phpMyAdmin oder direkt per SQL):

CREATE TABLE user (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  email VARCHAR(40) NOT NULL,
  phone VARCHAR(40)
);

Lege einige Einträge manuell an. Erstelle danach die Datei db.php mit der Verbindung.

2. Ausgeben aller Einträge

Erstelle eine Datei index.php, die eine Liste aller Einträge der Tabelle anzeigt. Verwende das SELECT-Statement, um die Einträge aus der Datenbank abzurufen und sie in einer HTML-Tabelle anzuzeigen.

Beispielhafte Liste als HTML-Tabelle

3. Hinzufügen von Einträgen

Erstelle die Datei insert.php, die ein Formular enthält, mit dem ein neuer Eintrag in der Datenbank angelegt werden kann. Verwende ein Prepared Statement mit INSERT, um den neuen Eintrag in der Datenbank zu speichern.

Die Übermittlung der Daten soll mit POST erfolgen. Leite nach dem Speichern mit header("Location: index.php") zurück auf die Liste.

Erweiterung um einen Link

Formular zur Erstellung eines neuen Kontakts

4. Löschen von Einträgen

Erweitere die index.php aus Aufgabe 2 (Ausgeben aller Einträge), um dem Benutzer das Löschen von Einträgen zu ermöglichen. Verwende das DELETE-Statement, um die Löschung in der Datenbank durchzuführen.

Füge einen Button Löschen neben den einzelnen Einträgen hinzu.

echo "<form method='post'>";
echo "<input type='hidden' name='id' value='" . $row["id"] . "'>";
echo "<input type='submit' name='delete' value='Löschen'>";
echo "</form>";

Klickt man auf den Löschen-Button neben einem Eintrag, soll der Eintrag gelöscht und die Liste neu ausgegeben werden.

Löschen-Button in einem Formular

5. Aktualisieren von Daten

Füge einen Button Ändern neben den einzelnen Einträgen hinzu. Implementiere das Formular zum Ändern der Daten in der Datei edit.php und verwende das UPDATE-Statement, um die Änderungen in der Datenbank zu speichern.

// Erweiterung der Tabelle in der index.php
echo "<input type='submit' name='update' value='Ändern'>";

Ändern-Button in einem Formular

Formular zum Ändern von Benutzerdaten

6. Zusatz: Sortierung und Paginierung

Erweitere die index.php, um Sortierung und Paginierung von Einträgen zu unterstützen. Verwende ORDER BY und LIMIT, um Einträge in einer bestimmten Reihenfolge anzuzeigen und nur einen Teil der Einträge auf einer Seite anzuzeigen.

Achtung: Spaltennamen für ORDER BY können nicht als Platzhalter übergeben werden. Prüfe daher den Parameter gegen eine Liste erlaubter Spalten (in_array()), bevor er ins Statement eingebaut wird.

Beispielhafte Umsetzung der Sortierung

7. Zusatz: Suchfunktionalität

Erstelle eine PHP-Seite, die es dem Benutzer ermöglicht, nach Einträgen in der Datenbank zu suchen. Verwende das SELECT-Statement und die LIKE-Klausel, um Einträge abzurufen, die bestimmte Suchkriterien erfüllen. Die Platzhalterzeichen % gehören dabei in den Wert, nicht ins Statement: $stmt->execute(["%" . $suche . "%"]).

Ressourcen

Grundlagen von PHP