PHP/Tutorials/Datenbanken mit PHP

Aus SELFHTML-Wiki
< PHP‎ | Tutorials
Wechseln zu: Navigation, Suche

Dieser Kurs soll einen Einstieg in Datenbanken mit PHP bieten. Dabei wird abseits der Erklärung einzelner SQL-Abfragen keine grundlegende Einführung in SQL geboten, diese befindet sich bereits an anderer Stelle.

Wahl einer Datenbank

Die wohl am weitesten verbreiteten Relationalen Datenbankmanagementsystem (RDBMS) sind MySQL bzw. MariaDB – es wird wohl keinen Webhoster mit PHP-Unterstützung geben, der nicht auch MySQL oder MariaDB bereitstellt – PostgreSQL und MSSQL. MySQL, MariaDB und PostgreSQL sind freie Software, während MSSQL proprietär ist. Alle haben gemeinsam, dass sie nach dem Client-Server-Modell arbeiten: Die Datenbank wird von einem Server verwaltet und die Client-Anwendung – in unserem Fall ein PHP-Skript – greift auf den Server zu. Dieses Vorgehen birgt Komplexität in sich, schließlich muss der Datenbank-Server administriert und Zugriffsberechtigungen auf diesen gesichert werden. Der Vorteil liegt jedoch darin, dass die Datenbank auf einem anderen Server liegen kann und somit über das Netzwerk angesprochen werden kann.
In diesem Artikel beschränken wir uns auf MySQL / MariaDB.

Abseits der klassischen RDBMS ist SQLite verbreitet. SQLite speichert seine Datenbank in einer Datei, die bei jedem Ausführen des Programms von SQLite geladen wird – es gibt keinen dauerhaft laufenden Server, der konfiguriert werden muss, es müssen keine Zugangsdaten in der PHP-Anwendung hinterlegt werden, sondern lediglich ein Pfad zum Speichern der Datenbank. Die Vorteile sind klar: Die Datei mit der Datenbank, kann leicht gespeichert, kopiert und gelöscht werden und somit ideal zum Experimentieren. SQLite gilt als ressourcenschonend und bietet dabei dennoch die wesentlichen Funktionen einer relationalen Datenbank. Allerdings sollte die Verwendung von SQLite überdacht werden, wenn viele Schreibzugriffe verschiedener Prozesse gleichzeitig auf die Datenbank erfolgen, da zwar mehrere Prozesse gleichzeitig die Datenbank lesen öffnen können, jedoch nur einer schreibend. In diesem Fall ist eine nach dem Client-Server-Prinzip arbeitende Datenbank besser geeignet. Auf die meisten Anwendungsfälle trifft dies jedoch nicht zu und der Einsatz von SQLite zu erwägen.

PDO als Datenbankschnittstelle

PHP bietet mehrere Schnittstellen an, um auf Datenbanken zugreifen zu können. Für MySQL / MariaDB, PostgreSQL, MSSQL, SQLite und andere Datenbanken[1] gibt es eine einheitliche API namens PHP Data Objects, die es ermöglicht, diese Datenbanken auf eine einheitliche Weise anzusprechen. Allerdings obliegt es dem Programmierer, seine SQL-Abfragen so zu formulieren, dass alle zu unterstützenden Datenbanken sie verarbeiten können. Dennoch lohnt es sich, PDO zu benutzen, um zu vermeiden, große Teile des Programms umschreiben zu müssen, falls doch mal eine andere Datenbank angesprochen werden soll. Zudem ist PDO objektorientiert.

Neben PDO können die genannten Datenbanken auch mit für diese Datenbanken spezifische Schnittstellen angesprochen werden. Im Fall von MySQL / MariaDB heißt diese mysqli und kann sowohl prozedural als auch objektorientiert angesprochen werden. In älteren PHP-Versionen vor einschließlich PHP 5.6 gab es zusätzlich noch mysql, dessen Nachfolger mysqli ist.
Für SQLite existiert ebenfalls eine für diese Datenbank spezifische Erweiterung.

Eine Datenbank-Verbindung aufbauen

Der Aufbau der Verbindung mit PDO ist Datenbank-spezifisch, daher ist er jeweils für MySQL und SQLite separat beschrieben.

Um eine Verbindung zu Datenbank aufzubauen, muss dem Konstruktor der PDO-Klasse ein sogenannte DSN-String (Data Source Name) übergeben werden. Wichtig ist das Fangen der Exception beim Erstellen der Instanz der PDO-Klasse mittels try-catch, um zu verhindern, dass bei Auftreten eines Fehlers und im Falle aktiviertem Anzeigen der Fehlermeldungen in der Ausgabe ungewollt die Datenbank-Zugangsdaten ausgegeben werden[2]. Allerdings sollten im Produktivbetrieb eines Systems Fehlermeldungen nicht ausgegeben werden, sondern in ein Error-Log geschrieben werden.

MySQL

Im Falle von MySQL enthält der DSN den Host-Namen, den Namen der zu verwendenden Datenbank sowie optional den verwendeten Port und die Angabe einer Zeichenkodierung.

Den Port müssen Sie nur angeben, wenn Ihr MYSQL Server einen anderen als den Standardport 3306 verwendet.

Bei der Zeichencodierung passt sich PDO an die im Server eingestellte Default-Zeichencodierung an und hier müssen Sie aufpassen. Die Default-Zeichencodierung ist oft noch "latin1", was bedeutet, dass die Verwendung von Unicode generell zu Problemen führt, oder auch "utf8" - was ein Synonym für "utf8mb3" ist und bedeutet, dass UTF-8 Codes mit mehr als 3 Bytes abgewiesen werden. Mit anderen Worten: Sie können nur Zeichen der BMP verwenden und ein einziges Emoji der Plane 1 führt zu einem unerwarteten SQL Fehler 1366 😳. Um Abhilfe zu schaffen, setzen Sie die Einstellung character_set_server Ihrer Datenbank auf utf8mb4, oder geben Sie diesen Wert im DSN an, so wie im folgenden Beispiel:

"Verbindung zur MySQL-Datenbank aufbauen." 
<?php
try {
  $dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
  $username = 'username';
  $password = 'password';
  $dbh = new \PDO($dsn, $username, $password);
} catch(\Exception $e) {
  die('Interner Fehler: Die Datenbank-Verbindung konnte nicht aufgebaut werden.');
}

Die korrekte Zeichenkodierung ist auch deshalb wichtig, damit der Schutz gegen SQL-Injections zuverlässig funktioniert.

Empfehlung: Wenn Sie können, setzen Sie in der Konfiguration des MYSQL- oder MariaDB Servers im Abschnitt [mysqld]den Schalter character_set_server=utf8mb4. Damit setzen Sie den Defaultzeichensatz des Servers auf modernes Unicode. PHP folgt dieser Einstellung und Sie können die charset-Angabe im DSN weglassen.

SQLite

Bei SQLite lautet der DSN sqlite: gefolgt von dem Dateipfad, in dem die Datenbank abgelegt werden soll. Der DSN könnte beispielsweise sqlite:/tmp/tmpdb.db sein. Die Datenbank sollte in einem Verzeichnis außerhalb des Document-Root liegen – es sei denn, sie soll herunterladbar sein. Weitere Angaben zum DSN einer SQLite-Datenbank finden sich in der PHP-Doku.

Beispiel 
<?php
try {
  // die Datenbank-Datei heißt „db.sqlite3“ und
  // liegt im Beispiel im gleichen Verzeichnis wie das PHP-Skript
  $dbh = new PDO('sqlite:db.sqlite3');
} catch(Exception $e) {
  // den Fehler ins error_log schreiben:
  error_log($e->getMessage());
  // Falls eine „schöne“ Website für die Fehlermeldung zur Verfügung steht,
  // diese ausgeben und das die() weglassen
  die('Interner Fehler: Die Datenbank-Verbindung konnte nicht aufgebaut werden.');
}

Optionen setzen

Zudem können verschiedene Optionen für die Verbindung gesetzt werden. In diesem Tutorial sorgen wir dafür, dass die Ergebnisse als Array mit den Spaltennamen als Schlüssel zurückgegeben wird und dass beim Auftreten jeglicher Fehler eine Exception geworfen wird – letzteres wird in PHP 8 die Standard-Einstellung werden[3], es ist daher sinnvoll, dies explizit zu setzen, damit sich der Code unter verschiedenen PHP-Versionen einheitlich verhält. Das Werfen von Exception erschwert, dass sich still auftretende Fehler einschleichen, indem eine Behandlung der Exception erzwungen wird.

Sinnvolle Einstellungen für die Verbindung setzen 
// Ergebnisse einer Abfrage sind ausschließlich über den Spaltennamen zugänglich
$dbh->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE, \PDO::FETCH_ASSOC);

// wird in PHP 8 Standard-Einstellung, erzwingt Fehlerbehandlung bei Datenbankabfragen
$dbh->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
?>

SQL ausführen

Um Daten in der Datenbank ablegen zu können, muss als erstes eine Tabelle angelegt werden. In diesem Artikel anhand des Beispiels einer Adressverwaltung. Das dafür nötige SQL wird wegen unterschiedlicher Syntax zwischen MySQL und SQLite zwei mal angegeben:

SQL zum Erstellen einer Tabelle in MySQL 
CREATE TABLE IF NOT EXISTS `adressen` (
 `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
 `vorname` TEXT NOT NULL,
 `nachname` TEXT NOT NULL,
 `stadt` TEXT NOT NULL,
 `strasse` TEXT NOT NULL,
 `plz` TEXT NOT NULL,
 `zuletzt_geaendert` INTEGER,
 `notiz` TEXT
);

Es fällt auf, dass sich in diesem Statement lediglich die Schreibweise von AUTOINCREMENT unterscheidet.

SQL zum Erstellen einer Tabelle in SQLite 
CREATE TABLE IF NOT EXISTS `adressen` (
 `id` INTEGER PRIMARY KEY AUTOINCREMENT,
 `vorname` TEXT NOT NULL,
 `nachname` TEXT NOT NULL,
 `stadt` TEXT NOT NULL,
 `strasse` TEXT NOT NULL,
 `plz` TEXT NOT NULL,
 `zuletzt_geaendert` INTEGER,
 `notiz` TEXT
);

Obiges SQL-Statement kann sowohl über ein grafisches Werkzeug wie Adminer oder phpMyAdmin ausgeführt werden. Auch von PHP aus lässt sich das SQL mittels der exec-Methode des PDO-Objekts ausführen. Diese liefert die Anzahl der betroffenen Zeilen oder im Falle des Fehlschlagens FALSE zurück.

Tabelle erstellen 
$dbh->exec('CREATE TABLE […]');

Prepared Statements

Sollen Daten über ein SQL-Statement in die Datenbank eingefügt werden, kann der Kontextwechsel der Daten mittels quote behandelt und die Query anschließend aus den Strings mit den SQL-Befehlen und den gequoteten Daten zusammengebaut werden. Dieses Vorgehen ist unpraktisch, fehleranfällig und daher nicht empfehlenswert.

Besser ist hingegen die Verwendung sogenannter Prepared Statements. Hierbei wird der String mit dem SQL-Befehl getrennt von den Daten an die Datenbank übermittelt. Einerseits wird so SQL-Injections effektiv vorgebeugt, weil bei richtiger Anwendung – Daten nicht direkt in die SQL-Statements einbauen – Daten keinesfalls als SQL ausgeführt werden können. Außerdem kann sich die Datenbank auf Basis des übermittelten SQL-Statements einen sogenannten Ausführungsplan überlegen und diesen bei mehrmaliger Ausführung des Prepared Statements erneut verwenden – ein Geschwindigkeitsvorteil!

Prepared Statements anhand eines INSERT 
<?php
$query = 'INSERT INTO `adressen`
(`vorname`, `nachname`, `stadt`, `strasse`, `plz`, `zuletzt_geaendert`, `notiz`)
VALUES (?, ?, ?, ?, ?, ?, ?)';
// die Methode prepare() liefert ein PDOStatement-Objekt zurück
$stmt = $dbh->prepare($query);

// Ausführen des Prepared Statements und Einfügen der Werte in die Datenbank:
$stmt->execute(['Peter', 'Lustig', 'Bärstadt', 'Elchwinkel 3', '65388', time(), '']);
Falls Sie mehrere Datensätze einfügen wollen, können Sie execute() mehrmals ausführen, beispielsweise in einer Schleife. execute() gibt bei erfolgreicher Ausführung TRUE zurück.

Ob das Einfügen in die Datenbank von Erfolg gekrönt war, können Sie anschließend beispielsweise mittels Adminer oder phpMyAdmin überprüfen.

Gerade bei mehrfacher Ausführung eines Prepared Statements kann es zudem praktisch sein, statt execute() ein Array zu übergeben oder die Inhalte des von fetch() zurückgegebenen Arrays zuzugreifen, mittels bindValue() einen bestimmten Wert an einen Parameter, mittels bindParam() eine Variable an einen Parameter und mittels bindColumn() eine Variable an eine Spalte zu binden.

Eine HTML-Tabelle erzeugen

Nun geht es an das Ausgeben der eingefügten Werte, die mittels eines SELECT-Statements ausgewählt wurden. Im ersten Schritt können Sie sich mittels SELECT * FROM `adressen` alle Datensätze ausgeben lassen. Zum Erzeugen der Spalten-Überschriften ordnen wir uns diese den Spaltennamen in einem assoziativen Array zu.

Eine HTML-Tabelle auf Basis der Datenbank-Ausgabe erzeugen 
<?php
// Datenbankverbindung aufbauen, siehe oben...

$spalten = [
  'vorname' => 'Vorname',
  'nachname' => 'Nachname',
  'stadt' => 'Stadt',
  'strasse' => 'Straße',
  'plz' => 'PLZ',
  'notiz' => 'Anmerkung'
];

?>
<!doctype html>
<html lang="de">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Titel</title>
  </head>
  <body>
    <table>
      <thead>
        <tr>
<?php
foreach ($spalten as $name) {
  echo '<th>', htmlspecialchars($name), '</th>';
}
?>
        </tr>
      </thead>
      <tbody>
<?php
$results = $dbh->query('SELECT * FROM `adressen`');

foreach($results as $result) {
  echo '<tr>';

  foreach ($spalten as $schluessel => $name) {
    echo '<td>', htmlspecialchars($result[$schluessel]), '</td>';
  }
  
  echo '</tr>';
}
?>
      <tbody>
    <table>
  </body>
</html>

Die Ausgabe sortieren

Soll die Ausgabe nach einer Spalte sortiert werden, ist das SQL-Statement schnell erstellt: SELECT * FROM `adressen` ORDER BY nachname ASC. Problematisch wird es jedoch, wenn die Sortierreihenfolge vom Nutzer der Webapplikation bestimmbar sein soll, beispielsweise über URL-Parameter: ?by=nachname&order=ASC

Prepared Statements lassen sich hier nicht nutzen, weil die Sortierreihenfolge keine Daten, sondern Bestandteil des eigentlichen SQL-Befehls sind. Eine einfache und effiziente Lösung ist, die Parameter gegen eine Liste mit erlaubten Spalten und Reihenfolgen abzugleichen.

Beispiel 
$spalten = [
  'vorname' => 'Vorname',
  'nachname' => 'Nachname',
  'stadt' => 'Stadt',
  'strasse' => 'Straße',
  'plz' => 'PLZ',
  'notiz' => 'Anmerkung'
];

$reihenfolgen = [
  'ASC' => 'aufsteigend',
  'DESC' => 'absteigend'
];

// Sortierung bestimmen:
$order = array_key_exists($_GET['order']??'', $reihenfolgen);
$by = array_key_exists($_GET['by']??'', $spalten);

if($order && $by) {
  $order = ' ORDER BY '.$_GET['by'].' '.$_GET['order'];
} elseif($order xor $by) {
  // hier fummelt wer an den Parametern herum...
  header('Location: ./');
  die();
} else {
  $_GET['by'] = 'nachname';
  $_GET['order'] = 'ASC';
}

$order = ' ORDER BY '.$_GET['by'].' '.$_GET['order'];

$results = $dbh->query('SELECT * FROM `adressen`' . $order);

foreach($results as $result) {
  // Tabelle ausgeben, siehe oben
}

Pagination

ToDo (weitere ToDos)

Pagination mittels URL-Parametern startID o.ä. erreichen (aber ohne LIMIT mit Offset: https://use-the-index-luke.com/no-offset

Formular zum Editieren und Einfügen von Werten

ToDo (weitere ToDos)

Editieren ergänzen
"Einfügen von Einträgen" 
<?php
if(isset($_POST['neu'])) {
  $schluessel = [];
  $werte = [];
  $platzhalter = [];

  foreach ($spalten as $spalte => $name) {
      $schluessel[] = '`'.$spalte.'`';
      // Zu lange Eingaben kürzen
      if(mb_strlen($_POST[$spalte]) > 256) {
        $_POST[$spalte] = mb_substr($_POST[$spalte], 0, 256);
      }
      $werte[] = $_POST[$spalte]??'';
      $platzhalter[] = '?';
  }
  $werte[] = time();
  $query = 'INSERT INTO `adressen` ('.implode(', ', $schluessel).', `zuletzt_geaendert`)
            VALUES ('.implode(', ', $platzhalter).')';
  $stmt = $dbh->prepare($query);
  $stmt->execute($werte);
  header('Location: .');
  die();
}
?>
<form method="post">
  <fieldset>
    <legend>Neuer Eintrag</legend>
<?php
foreach($spalten as $schluessel => $name) {
  echo '<label><input type="text" name="',
    htmlspecialchars($schluessel, ENT_QUOTES | ENT_HTML5), '" maxlength="256"> ',
    htmlspecialchars($name),
    '</label><br>'; 
}
?>
    <button name="neu" value="1">Eintragen</button>
  </fieldset>
</form>

Siehe auch

Weblinks

Quellen

  1. ↑ PHP-Handbuch: Liste der PDO-Treiber
  2. ↑ golem.de: IT-Sicherheit: Wie ich mein Passwort im Stack Trace fand
  3. ↑ stitcher.io: New in PHP 8 → Default PDO error mode
Abgerufen von „http://wiki.selfhtml.org/index.php?title=PHP/Tutorials/Datenbanken_mit_PHP&oldid=98050“