decocode decocode deco    

MySQL-Datenbanken verwalten #

Übersicht
Benutzer und Datenbank anlegen
Verbindung zur Datenbank herstellen
Datenbanken auflisten
Existenz einer Datenbank prüfen
Namen der Datenbanktabellen ermitteln
Datenbank ohne Datensätze kopieren
Datenbank mit Datensätzen kopieren
Datenbank umbenennen
Datenbank leeren
Datenbank löschen

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.

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?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
  $my = mysqli_connect("localhost", $sqlroot['user'], $sqlroot['password'], $sqlroot['dbname']) or die("Error: ".mysqli_connect_error());
  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):

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
<?php
  $sqldb = array(
    "user"     => "web007",       # Benutzername
    "password" => "moneypenny",   # Benutzerpasswort
    "dbname"   => "usr_web007_1"  # Name der Datenbank
  );
  $my = @mysqli_connect("localhost", $sqldb['user'], $sqldb['password'], $sqldb['dbname']) or die("Error: ".mysqli_connect_error());
  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_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.

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $sql = "SHOW DATABASES";
  $res = mysqli_query($my, $sql);
  while ($ds = mysqli_fetch_assoc($res)) {
    echo $ds['Database']."<br>";
  }
?>

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.

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
<?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_fetch_row($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
► PHP-Dokumentation: mysqli_error() mysqli_errno()

Um die Namen sämtlicher Tabellen einer Datenbank anzuzeigen, wird der Befehl SHOW TABLES verwendet.

Sollte diese Anfrage fehlschlagen, wird mit mysqli_error() eine Fehlermeldung ausgegeben. Die interne Nummer dieser Fehlermeldung kann mit mysqli_errno() angezeigt werden. Diese Funktionen sind besonders in der Entwicklungsphase sehr hilfreich, um sich bei Aktionen eventuelle Verarbeitungsfehler anzeigen zu lassen.

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbname = "usr_web007_1";
  $sql = "SHOW TABLES FROM `".$dbname."`";
  if ($res = mysqli_query($my, $sql)) {
    $count = 0;
    while ($ds = mysqli_fetch_row($res)) {
      echo $ds[0]."<br>";
      $count++;
    }
    if ($count == 0) echo "Die Datenbank <samp>".$dbname."</samp> enthält keine Tabellen.";
  } else echo "<mark>Die Tabellen der Datenbank <samp>".$dbname."</samp> konnten nicht gefunden werden.</mark><br>Fehler: #".mysqli_errno($my).": ".mysqli_error($my)."<br>";
?>

Datenbank ohne Datensätze kopieren #

► MySQL-Dokumentation: CREATE DATABASE SHOW TABLES CREATE TABLE

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.

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?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
  $sql = "SHOW TABLES FROM `".$source_db."`";
  if ($res = mysqli_query($my, $sql)) {
    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 Tabellen der Datenbank <samp>".$source_db."</samp> konnten nicht gefunden werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

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.

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?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
  $sql = "SHOW TABLES FROM `".$source_db."`";
  if ($res = mysqli_query($my, $sql)) {
    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 Tabellen der Datenbank <samp>".$source_db."</samp> konnten nicht gefunden werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

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.

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?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
  $sql = "SHOW TABLES FROM `".$source_db."`";
  if ($res = mysqli_query($my, $sql)) {
    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 Tabellen der Datenbank <samp>".$source_db."</samp> konnten nicht gefunden werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
  
  # 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.

Quelltext auswählen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbname = "usr_web008_3";
  $sql = "SHOW TABLES FROM `".$dbname."`";
  if ($res = mysqli_query($my, $sql)) {
    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 Tabellen der Datenbank <samp>".$dbname."</samp> konnten nicht gefunden werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

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:

Quelltext auswählen
1
2
3
4
5
6
7
8
9
<?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>";
?>