PHP: MySQL-Datenbanken verwalten
Mit Hilfe der MySQLi-Funktionen von PHP können serverseitige Datenbanken und deren Tabellen verwaltet werden. Die hier beschriebenen Funktionen beschränken sich auf die in der Praxis am häufigsten verwendeten Anforderungen. Üblicherweise ist serverseitig nur eine kleine Anzahl von Datenbanken (häufig nur eine oder zwei) eingerichtet, abhängig vom Umfang des Hosting-Paketes. Innerhalb einer Datenbank können aber beliebig viele Tabellen angelegt werden, wodurch dieses Limit in der Regel keine große Einschränkung bedeutet.
Die Möglichkeiten von PHP, auf eine Datenbank zuzugreifen und diese zu manipulieren, werden hier anhand von Praxisbeispielen erläutert. Sämtliche Aktionen können auch über die grafische Benutzeroberfläche phpMyAdmin durchgeführt bzw. verfolgt werden.
Benutzer und Datenbank anlegen
► MySQL-Dokumentation: CREATE USER GRANT CREATE DATABASE
► PHP-Dokumentation: mysqli_connect() mysqli_connect_error() mysqli_set_charset() mysqli_query()
Üblicherweise existiert auf einem entfernten Server als Teil eines Webhosting-Paketes bereits eine Datenbank, die einem bestimmten Benutzer zugeordnet ist. In einer lokalen Entwicklungsumgebung (z. B. LAMP) wird man dagegen den Benutzer und dessen Datenbank auf dem entfernten Server simulieren, um die erstellten Skripte dort verwenden zu können. Bei der Installation von LAMP existiert bereits der Benutzer root, als der man sich auch bei phpMyAdmin anmelden kann. Mit den Zugangsdaten von root kann nun eine Verbindung zum MySQL-Server aufgebaut werden, um einen neuen Benutzer und dessen Datenbank anzulegen. Das folgende Skript automatisiert diesen Vorgang. Es müssen lediglich die Zugangsdaten von root ($sqlroot) und die Daten des neuen Benutzers ($sqldb) angepasst werden.
<?php
header("Content-Type: text/html; charset=utf-8");
$sqlroot = array(
"user" => "root",
"password" => "MeinPasswort",
"dbname" => "usr_web001_1"
);
$sqldb = array(
"user" => "web007",
"password" => "moneypenny",
"dbname" => "usr_web007_1"
);
# Aufbau der Verbindung zum MySQL-Server als root
error_reporting(0);
mysqli_report(MYSQLI_REPORT_OFF);
$my = mysqli_connect("localhost", $sqlroot['user'], $sqlroot['password'], $sqlroot['dbname']);
if (mysqli_connect_errno()) die("<mark>• Keine Verbindung zum MySQL-Server: ".mysqli_connect_error()."</mark><br>");
mysqli_set_charset($my, "utf8");
# Benutzer web007 anlegen
$sql = "CREATE USER '".$sqldb['user']."'@'localhost' IDENTIFIED BY '".$sqldb['password']."'";
if (mysqli_query($my, $sql)) echo "Benutzer <samp>".$sqldb['user']."</samp> wurde angelegt.<br>";
else echo "<mark>Benutzer <samp>".$sqldb['user']."</samp> konnte nicht angelegt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
# Dem Benutzer web007 maximale Rechte gewähren
$sql = "GRANT ALL PRIVILEGES ON * . * TO '".$sqldb['user']."'@'localhost' IDENTIFIED BY '".$sqldb['password']."' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0";
if (mysqli_query($my, $sql)) echo "Dem Benutzer <samp>".$sqldb['user']."</samp> wurden alle Rechte gewährt.<br>";
else echo "<mark>Dem Benutzer <samp>".$sqldb['user']."</samp> konnten keine Rechte gewährt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
# Datenbank usr_web007_1 anlegen
$sql = "CREATE DATABASE `".$sqldb['dbname']."` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
if (mysqli_query($my, $sql)) echo "Datenbank <samp>".$sqldb['dbname']."</samp> wurde angelegt.<br>";
else echo "<mark>Datenbank <samp>".$sqldb['dbname']."</samp> konnte nicht angelegt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>
Dieses Skript erzeugt als erstes mit dem SQL-Befehl CREATE USER den Benutzer web007, der im weiteren Verlauf den Zugriff auf die Datenbank erhält. Mit der PHP-Funktion mysqli_query() wird der MySQL-Befehl an den Datenbankserver geschickt, um dort ausgeführt zu werden.
Als nächstes werden diesem Benutzer mit dem SQL-Befehl GRANT ALL PRIVILEGES sämtliche Rechte für den Zugriff auf die Datenbank gewährt.
Mit dem Befehl CREATE DATABASE wird schließlich eine neue Datenbank eingerichtet, sofern der Server das zulässt. Auf einem lokalen Server sollte das problemlos möglich sein. Der Parameter IF NOT EXISTS verhindert das Anlegen der Datenbank, wenn diese bereits existiert. Mit DEFAULT CHARACTER SET wird die gewünschte Zeichenkodierung für die Datenbank angegeben (hier: UTF-8). Mit COLLATE wird die Sortierregel für die Datenbank angegeben.
Verbindung zur Datenbank herstellen
► PHP-Dokumentation: mysqli_connect() mysqli_connect_error() mysqli_set_charset()
Vor jeder Aktion, bei der die Datenbank ausgelesen oder verändert wird, muss zunächst eine Verbindung zum MySQL-Server aufgebaut werden. Dazu benötigt man vier Daten. Das sind der Host, auf dem die Datenbank betrieben wird, der Benutzername, das dazugehörige Passwort sowie der Name der Datenbank. Diese Daten sind entweder vom Serverhost vorgegeben oder bei der Aktivierung der Datenbank beispielsweise über die Webspace-Konfiguration (z. B. Confixx) bzw. über phpMyAdmin vom Benutzer eingestellt worden. Um den Aufbau einer solchen Verbindung nicht jedesmal neu im PHP-Skript aufzuführen, ist es sinnvoll, eine separate Datei (hier: db.inc.php) für diese Verbindung zu erstellen, die später über include() bzw. require() in die Skripte eingebunden wird (die Zugangsdaten für die Datenbank sind hier natürlich wieder ein Beispiel und müssen im konkreten Fall angepasst werden):
<?php
$sqldb = array(
"user" => "web007", # Benutzername
"password" => "moneypenny", # Benutzerpasswort
"dbname" => "usr_web007_1" # Name der Datenbank
);
error_reporting(0);
mysqli_report(MYSQLI_REPORT_OFF);
$my = @mysqli_connect("localhost", $sqldb['user'], $sqldb['password'], $sqldb['dbname']);
if (mysqli_connect_errno()) die("<mark>• Keine Verbindung zum MySQL-Server: ".mysqli_connect_error()."</mark><br>");
mysqli_set_charset($my, "utf8");
function mes($my, $str) {
return mysqli_escape_string($my, $str);
}
?>
Über die Funktion mysqli_connect() wird nun die Verbindung zum Datenbankserver hergestellt. Falls die Verbindung nicht aufgebaut werden kann, wird mit mysqli_connect_error() eine Fehlermeldung ausgegeben.
Die Funktion mysqli_set_charset() in Zeile 8 schaltet die Zeichenkodierung der Datenbank auf Unicode UTF-8.
Zur Funktion mes() mehr auf der Seite zu SQL-Injection.
Datenbanken auflisten
► MySQL-Dokumentation: SHOW DATABASES
► PHP-Dokumentation: mysqli_num_rows() mysqli_fetch_assoc() mysqli_fetch_row() mysqli_fetch_array()
Um sich die auf dem Datenbankserver vorhandenen (bzw. erreichbaren) Datenbanken anzeigen zu lassen, wird der SQL-Befehl SHOW DATABASES verwendet. Die Informationen dieses Befehls werden dann mittels mysqli_fetch_assoc() in ein assoziatives Array geschrieben, das die Namen der Datenbanken enthält.
<?php
header("Content-Type: text/html; charset=utf-8");
require("db.inc.php");
$sql = "SHOW DATABASES";
$res = mysqli_query($my, $sql);
if (mysqli_num_rows($res)) {
while ($ds = mysqli_fetch_assoc($res)) {
echo $ds['Database']."<br>";
}
}
?>
Die Funktion mysqli_query() gibt ein Datenobjekt zurück, das erst verwertbar ist, wenn es in Arrays umgewandelt wird, die mit PHP ausgelesen werden können. Die Funktion mysqli_fetch_assoc() erzeugt assoziative Arrays mit alphanumerischen Indizes (Schlüssel/Wert-Paare). Die Funktion mysqli_fetch_row() erzeugt gweöhnliche Arrays mit numerischen Indizes (laufende Nummern). Die Funktion mysqli_fetch_array() erzeugt Arrays beider Arten.
Die Funktion mysqli_num_rows() gibt die Anzahl der Ergebniszeilen zurück, was verwendet werden kann, um zu prüfen, ob eine Abfrage überhaupt ein Ergebnis erzielt hat.
Existenz einer Datenbank prüfen
► MySQL-Dokumentation: SELECT
Um zu überprüfen, ob eine Datenbank überhaupt existiert, kann der Name der gesuchten Datenbank in der MySQL-internen Tabelle information_schema.schemata gesucht werden.
<?php
header("Content-Type: text/html; charset=utf-8");
require("db.inc.php");
$dbname = "usr_web007_1";
$sql = "SELECT `schema_name` FROM `information_schema`.`schemata` WHERE `schema_name` = '".$dbname."'";
$res = mysqli_query($my, $sql);
if (mysqli_num_rows($res)) echo "Datenbank <samp>".$dbname."</samp> existiert.<br>";
else echo "<mark>Datenbank <samp>".$dbname."</samp> existiert nicht.</mark><br>";
?>Namen der Datenbanktabellen ermitteln
► MySQL-Dokumentation: SHOW TABLES
Um die Namen sämtlicher Tabellen einer Datenbank anzuzeigen, wird der Befehl SHOW TABLES verwendet.
<?php
header("Content-Type: text/html; charset=utf-8");
require("db.inc.php");
$dbname = "usr_web007_1";
$res = mysqli_query($my, "SHOW TABLES FROM `".$dbname."`");
if (mysqli_num_rows($res)) {
while ($ds = mysqli_fetch_row($res)) echo $ds[0]."<br>";
echo "Insgesamt <samp>".mysqli_num_rows($res)."</samp> Tabellen.";
} else echo "Die Datenbank <samp>".$dbname."</samp> enthält keine Tabellen.";
?>Datenbank ohne Datensätze kopieren
► MySQL-Dokumentation: CREATE DATABASE SHOW TABLES CREATE TABLE
► PHP-Dokumentation: mysqli_error() mysqli_errno()
Um eine Kopie einer Datenbank ohne die enthaltenen Datensätze zu erzeugen, existiert kein eigener Befehl. Man kann aber eine neue leere Datenbank erzeugen und die Tabellen der ursprünglichen Datenbank in die neue kopieren.
<?php
header("Content-Type: text/html; charset=utf-8");
require("db.inc.php");
$source_db = "usr_web007_1";
$new_db = "usr_web007_2";
# Neue Datenbank anlegen
$sql = "CREATE DATABASE `".$new_db."` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
if (mysqli_query($my, $sql)) echo "Datenbank <samp>".$new_db."</samp> wurde angelegt.<br>";
else echo "<mark>Datenbank <samp>".$new_db."</samp> konnte nicht angelegt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
# Tabellen der alten Datenbank in die neue übertragen
$res = mysqli_query($my, "SHOW TABLES FROM `".$source_db."`");
if (mysqli_num_rows($res)) {
while ($ds = mysqli_fetch_row($res)) {
$sql = "CREATE TABLE IF NOT EXISTS `".$new_db."`.`".$ds[0]."` LIKE `".$source_db."`.`".$ds[0]."`";
if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$source_db.".".$ds[0]."</samp> wurde kopiert.<br>";
else echo "<mark>Tabelle <samp>".$source_db.".".$ds[0]."</samp> konnte nicht kopiert werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
}
} else echo "<mark>Die Datenbank <samp>".$source_db."</samp> enthält keine Tabellen.</mark>";
?>Datenbank mit Datensätzen kopieren
► MySQL-Dokumentation: CREATE DATABASE SHOW TABLES CREATE TABLE
Um eine Kopie einer Datenbank einschließlich der enthaltenen Datensätze zu erzeugen, existiert kein eigener Befehl. Man kann aber eine neue leere Datenbank erzeugen und die Tabellen der ursprünglichen Datenbank in die neue kopieren.
<?php
header("Content-Type: text/html; charset=utf-8");
require("db.inc.php");
$source_db = "usr_web007_1";
$new_db = "usr_web007_3";
# Neue Datenbank anlegen
$sql = "CREATE DATABASE `".$new_db."` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
if (mysqli_query($my, $sql)) echo "Datenbank <samp>".$new_db."</samp> wurde angelegt.<br>";
else echo "<mark>Datenbank <samp>".$new_db."</samp> konnte nicht angelegt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
# Tabellen der alten Datenbank in die neue übertragen
$res = mysqli_query($my, "SHOW TABLES FROM `".$source_db."`");
if (mysqli_num_rows($res)) {
while ($ds = mysqli_fetch_row($res)) {
$sql = "CREATE TABLE IF NOT EXISTS `".$new_db."`.`".$ds[0]."` (`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) AS SELECT * FROM `".$source_db."`.`".$ds[0]."`";
if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$source_db.".".$ds[0]."</samp> wurde kopiert.<br>";
else echo "<mark>Tabelle <samp>".$source_db.".".$ds[0]."</samp> konnte nicht kopiert werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
}
} else echo "<mark>Die Datenbank <samp>".$source_db."</samp> enthält keine Tabellen.</mark>";
?>Datenbank umbenennen
► MySQL-Dokumentation: CREATE DATABASE SHOW TABLES CREATE TABLE DROP DATABASE
Um eine Datenbank umzubenennen, existiert kein eigener Befehl. Man kann aber eine neue leere Datenbank erzeugen, die Tabellen der ursprünglichen Datenbank in die neue kopieren und die ursprüngliche Datenbank löschen.
<?php
header("Content-Type: text/html; charset=utf-8");
require("db.inc.php");
$source_db = "usr_web007_3";
$new_db = "usr_web008_3";
# Neue Datenbank anlegen
$sql = "CREATE DATABASE `".$new_db."` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
if (mysqli_query($my, $sql)) echo "Datenbank <samp>".$new_db."</samp> wurde angelegt.<br>";
else echo "<mark>Datenbank <samp>".$new_db."</samp> konnte nicht angelegt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
# Tabellen der alten Datenbank in die neue übertragen
$res = mysqli_query($my, "SHOW TABLES FROM `".$source_db."`");
if (mysqli_num_rows($res)) {
while ($ds = mysqli_fetch_row($res)) {
$sql = "CREATE TABLE IF NOT EXISTS `".$new_db."`.`".$ds[0]."` (`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) AS SELECT * FROM `".$source_db."`.`".$ds[0]."`";
if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$source_db.".".$ds[0]."</samp> wurde kopiert.<br>";
else echo "<mark>Tabelle <samp>".$source_db.".".$ds[0]."</samp> konnte nicht kopiert werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
}
} else echo "<mark>Die Datenbank <samp>".$source_db."</samp> enthält keine Tabellen.</mark>";
# Ursprüngliche Datenbank löschen
$sql = "DROP DATABASE IF EXISTS `".$source_db."`";
if (mysqli_query($my, $sql)) echo "Datenbank <samp>".$source_db."</samp> wurde gelöscht.<br>";
else echo "<mark>Datenbank <samp>".$source_db."</samp> konnte nicht gelöscht werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>Datenbank leeren
► MySQL-Dokumentation: SHOW TABLES DROP TABLE
Um sämtliche Tabellen einer Datenbank zu entfernen, ohne die Datenbank selbst zu löschen, existiert kein eigener Befehl. Stattdessen können mit DROP TABLE innerhalb einer Schleife alle Tabellen einzeln gelöscht werden.
Man kann die Datenbank natürlich auch ganz löschen und anschließend neu anlegen.
<?php
header("Content-Type: text/html; charset=utf-8");
require("db.inc.php");
$dbname = "usr_web008_3";
$res = mysqli_query($my, "SHOW TABLES FROM `".$dbname."`");
if (mysqli_num_rows($res)) {
while ($ds = mysqli_fetch_row($res)) {
$sql = "DROP TABLE `".$dbname."`.`".$ds[0]."`";
if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$dbname.".".$ds[0]."</samp> wurde gelöscht.<br>";
else echo "<mark>Tabelle <samp>".$dbname.".".$ds[0]."</samp> konnte nicht gelöscht werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
}
} else echo "<mark>Die Datenbank <samp>".$dbname."</samp> enthält keine Tabellen.</mark>";
?>Datenbank löschen
► MySQL-Dokumentation: DROP DATABASE
Um eine Datenbank mit allen Tabellen und Daten wieder zu löschen, wird der Befehl DROP DATABASE verwendet:
<?php
header("Content-Type: text/html; charset=utf-8");
require("db.inc.php");
$dbname = "usr_web008_3";
$sql = "DROP DATABASE IF EXISTS `".$dbname."`";
if (mysqli_query($my, $sql)) echo "Datenbank <samp>".$dbname."</samp> wurde gelöscht.<br>";
else echo "<mark>Datenbank <samp>".$dbname."</samp> konnte nicht gelöscht werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>