PHP: MySQL-Datenbanktabellen verwalten

Die Datensätze einer Datenbank werden in Datenbanktabellen gespeichert. Eine Datenbank kann nahezu beliebig viele Tabellen mit unterschiedlicher Struktur enthalten. Die Spalten (columns) dieser Tabellen enthalten die einzelnen Datenfelder, die Reihen (rows) die einzelnen Datensätze. Um eindeutig identifiziert werden zu können, sollte jede Tabelle ein Datenfeld besitzen, in dem die laufende Nummer der einzelnen Datensätze gespeichert wird. In diesem Tutorial hat dieses Feld den Namen id und trägt den Primärschlüssel (PRIMARY KEY) der Tabelle.

Normalerweise sollte die Struktur einer Tabelle bei dessen Erzeugung bereits feststehen. Im Laufe der Verwendung einer Tabelle kann es aber durchaus vorkommen, dass ihre Struktur angepasst werden muss. Daher wird in diesem Tutorial außerdem beschrieben, wie man einer Datenbanktabelle weitere Datenfelder hinzufügen, diese verändern und bei Bedarf wieder löschen kann.

Die Möglichkeiten, eine Datenbanktabelle zu erstellen und zu ändern, sind wesentlich vielfältiger als hier beschrieben. Dieses Tutorial beschränkt sich auf häufige Anwendungsfälle.

Datentypen

► MySQL-Dokumentation: Data Types

Bei der Erzeugung einer Datenbanktabelle muss für jedes Datenfeld der Datentyp angegeben werden. Die Angabe des Datentyps ist unter anderem für eine ökonomische Speicherbenutzung sinnvoll, daher muss man sich hier zunächst überlegen, wieviel Speicher die größten Einträge benötigen.

Hier nur eine Übersicht der wichtigsten Datentypen:

Numerische Typen

Bei ganzzahligen Typen (TINYINT bis BIGINT) kann der Wertebereich durch Angabe von UNSIGNED in den positiven Bereich verschoben werden (z. B. bei TINYINT nach 0 bis 255).

Alphanumerische Typen

BLOB-Typen (Dateien)

Datenbanktabelle anlegen

► MySQL-Dokumentation: CREATE TABLE

Um in einer Datenbank eine neue Tabelle anzulegen, wird der SQL-Befehl CREATE TABLE verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen";
  $sql = "CREATE TABLE IF NOT EXISTS `".$dbtname."` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `vorname` VARCHAR(30),
    `nachname` VARCHAR(30),
    `strasse` VARCHAR(50),
    `hausnummer` SMALLINT UNSIGNED,
    `plz` MEDIUMINT UNSIGNED,
    `ort` VARCHAR(50),
    `geburtstag` VARCHAR(10),
    `telefon` VARCHAR(30),
    `kommentar` TEXT
  ) CHARSET=utf8";
  if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$dbtname."</samp> wurde angelegt.<br>";
  else echo "<mark>Tabelle <samp>".$dbtname."</samp> konnte nicht angelegt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Innerhalb der Klammer des Befehls werden nun die einzelnen Spalten der Tabelle definiert, wobei für jede Spalte der Name und Datentyp angegeben werden muss.

Jede Tabelle benötigt neben den eigentlichen Datenspalten noch einen Index, sozusagen eine laufende Nummer. Die Werte dieser Spalte werden automatisch bei jedem neuen Datenelement eingetragen und sollten niemals manipuliert werden. Benötigt man z. B. für die Elemente ein individuelles numerisches Sortiermerkmal, sollte dafür eine zusätzliche Spalte eingerichtet werden. Die weiteren Attribute dieser Spalte haben folgende Bedeutung:

NOT NULL
Bewirkt bei allen numerischen Spalten, für die dieses Attribut angegeben wurde, dass Nullwerte in diesem Datenfeld nicht erlaubt sind (nicht zu verwechseln mit der Zahl 0).

AUTO_INCREMENT
Bewirkt das automatische Erhöhen des Wertes um 1 beim Einfügen neuer Datensätze in die Tabelle.

PRIMARY KEY
Diese Spalte trägt den Primärschlüssel der Tabelle.

Schließlich wird noch die Zeichenkodierung CHARSET (utf8) für die Tabelle angegeben.

Existenz einer Datenbanktabelle prüfen

► MySQL-Dokumentation: SHOW TABLES

Eine Methode, um die Existenz einer Datenbanktabelle zu prüfen, verwendet den Befehl SHOW TABLES.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen";
  $res = mysqli_query($my, "SHOW TABLES LIKE '".$dbtname."'");
  if (mysqli_num_rows($res)) echo "Die Tabelle <samp>".$dbtname."</samp> existiert.";
  else echo "Die Tabelle <samp>".$dbtname."</samp> existiert nicht.";
?>

Eigenschaften von Datenbanktabellen ermitteln

► MySQL-Dokumentation: SHOW TABLE STATUS

Um spezielle Informationen über sämtliche Tabellen der Datenbank, bei der man angemeldet ist, zu erhalten, wird der Befehl SHOW TABLE STATUS verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbname = "usr_web007_1";
  $res = mysqli_query($my, "SHOW TABLE STATUS");
  if (mysqli_num_rows($res)) {
    while ($ds = mysqli_fetch_assoc($res)) {
      echo "<pre>";
      print_r($ds);
      echo "</pre>";
    }
  } else echo "<mark>Die Datenbank <samp>".$dbname."</samp> enthält keine Tabellen.</mark>";
?>

Das Ergebnis dieser Abfrage enthält folgende Daten:

00 Name – der Name der Tabelle
01 Engine – die Speicher-Engine der Tabelle
02 Version – die Versionsnummer der frm-Datei-Datei der Tabelle
03 Row_format – das Speicherformat für die Datensätze
04 Rows – Anzahl der Datensätze in der Tabelle (nur eingeschränkt zuverlässig!)
05 Avg_row_length – Durchschnittliche Größe der Datensätze
06 Data_length – Gesamtgröße der Datensätze
07 Max_data_length – Maximale Größe der Datensätze
08 Index_length – Größe der Indexdatei
09 Data_free – Größe des freien Speicherplatzes
10 Auto_increment – der nächste Wert für AUTO_INCREMENT
11 Create_time – Zeitstempel der Erstellung der Tabelle
12 Update_time – Zeitstempel der letzten Änderung der Tabelle
13 Check_time – Zeitstempel der letzten Überprüfung der Tabelle
14 Collation – Zeichenkodierung und Kollation der Tabelle
15 Checksum – Prüfsumme
16 Create_options – Zusätzliche Optionen bei der Erstellung der Tabelle
17 Comment – Kommentar zur Tabelle

Um die Tabelleneigenschaften einer bestimmten Datenbank anzeigen zu lassen, wird der Befehl SHOW TABLE STATUS IN verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbname = "usr_web007_1";
  $dbtname = "adressen";
  $res = mysqli_query($my, "SHOW TABLE STATUS IN `".$dbname."`");
  if (mysqli_num_rows($res)) {
    while ($ds = mysqli_fetch_assoc($res)) {
      echo "<pre>";
      print_r($ds);
      echo "</pre>";
    }
  } else echo "<mark>Die Datenbank <samp>".$dbname."</samp> enthält keine Tabellen.</mark>";
?>

Um die Eigenschaften einer bestimmten Tabelle anzeigen zu lassen, wird der Zusatz LIKE bzw. WHERE `Name` = verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbname = "usr_web007_1";
  $dbtname = "adressen";
  $res = mysqli_query($my, "SHOW TABLE STATUS FROM `".$dbname."` LIKE '".$dbtname."'");
  # alternative Schreibweise:
  # $res = mysqli_query($my, "SHOW TABLE STATUS FROM `".$dbname."` WHERE `Name` = '".$dbtname."'");
  if (mysqli_num_rows($res)) {
    while ($ds = mysqli_fetch_assoc($res)) {
      echo "<pre>";
      print_r($ds);
      echo "</pre>";
    }
  } else echo "<mark>Die Tabelle <samp>".$dbname.".".$dbtname."</samp> wurde nicht gefunden.</mark>";
?>

Struktur einer Datenbanktabelle ermitteln

► MySQL-Dokumentation: SHOW TABLES SHOW COLUMNS

Um die Eigenschaften der Datenfelder einer Datenbanktabelle anzuzeigen, wird der Befehl SHOW COLUMNS verwendet. Bezieht sich die Abfrage auf die Datenbank, bei der man angemeldet ist, so kann man `".$dbname."`. weglassen.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbname = "usr_web007_1";
  $dbtname = "adressen";
  echo "<h3>".$dbtname."</h3>";
  $res = mysqli_query($my, "SHOW COLUMNS FROM `".$dbname."`.`".$dbtname."`");
  if (mysqli_num_rows($res)) {
    while ($ds = mysqli_fetch_assoc($res)) {
      echo "<pre>";
      print_r($ds);
      echo "</pre>";
    }
  }
?>

Das Ergebnis dieser Abfrage enthält folgende Daten:

00 Field – der Name des Datenfeldes
01 Type – der Datentyp des Datenfeldes
02 Null – die Einstellung, ob im Datenfeld Nullwerte zulässig sind
03 Key – Schlüsselstatus des Datenfeldes
04 Default – Vorgabewert des Datenfeldes
05 Extra – zusätzliche Eigenschaften wie AUTO_INCREMENT

Um die Eigenschaften der Datenfelder sämtlicher Tabellen einer Datenbank anzeigen zu lassen, kann man folgendermaßen vorgehen.

<?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 "<h3>".$ds[0]."</h3>";
      $res2 = mysqli_query($my, "SHOW COLUMNS FROM `".$dbname."`.`".$ds[0]."`");
      if (mysqli_num_rows($res2)) {
        while ($ds2 = mysqli_fetch_assoc($res2)) {
          echo "<pre>";
          print_r($ds2);
          echo "</pre>";
        }
      }
    }
  } else echo "<mark>Die Datenbank <samp>".$dbname."</samp> enthält keine Tabellen.</mark>";
?>

Um die Eigenschaften eines einzelnen Datenfeldes anzeigen zu lassen, verwendet man WHERE `Field` =.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbname = "usr_web007_1";
  $dbtname = "adressen";
  echo "<h3>".$dbtname."</h3>";
  $res = mysqli_query($my, "SHOW COLUMNS FROM `".$dbtname."` WHERE `Field` = 'id'");
  if (mysqli_num_rows($res)) {
    while ($ds = mysqli_fetch_assoc($res)) {
      echo "<pre>";
      print_r($ds);
      echo "</pre>";
    }
  }
?>

Indizes einer Datenbanktabelle ermitteln

► MySQL-Dokumentation: SHOW INDEX

Um die Eigenschaften der Indizes einer Tabelle zu ermitteln, wird der Befehl SHOW INDEX verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbname = "usr_web007_1";
  $dbtname = "adressen";
  echo "<h3>".$dbtname."</h3>";
  echo "<h3>".$dbtname."</h3>";
  $res = mysqli_query($my, "SHOW INDEX FROM `".$dbname."`.`".$dbtname."`");
  if (mysqli_num_rows($res)) {
    while ($ds = mysqli_fetch_assoc($res)) {
      echo "<pre>";
      print_r($ds);
      echo "</pre>";
    }
  } else echo "<mark>Der Index der Tabelle <samp>".$dbname.".".$dbtname."</samp> konnte nicht gefunden werden.</mark>";
?>

Das Ergebnis dieser Abfrage enthält folgende Daten:

00 Table – der Name der Tabelle
01 Non_unique – ein Wahrheitswert, ob der Index Duplikate enthalten darf
02 Key_name – der Name des Index
03 Seq_in_index – die Nummer der Spaltenreihenfolge des Index
04 Column_name – der Spaltenname
05 Collation – die Sortierregel des Index
06 Cardinality – ungefähre Anzahl eindeutiger Werte des Index
07 Sub_part – Anzahl der indizierten Zeichen bei teilweiser Indizierung der Spalte
08 Packed – Packstatus des Index
09 Null – Wahrheitswert, ob die Spalte einen Nullwert enthalten darf
10 Index_type – Indexmethode
11 Comment – Anmerkungen zum Index
12 Index_comment – Anmerkungen zum Index, die bei dessen Erzeugung angegeben wurden
13 Visible – gibt an, ob der Schlüssel für den Optimizer sichtbar ist
14 Expression – gibt an, ob es sich um einen funktionalen Schlüssel handelt

Anzahl der Datensätze einer Datenbanktabelle ermitteln

► MySQL-Dokumentation: Counting Rows

Um die Anzahl der Datensätze einer Tabelle zu ermitteln, wird der Befehl SELECT COUNT verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen";
  echo "<h3>".$dbtname."</h3>";
  $res = mysqli_query($my, "SELECT COUNT(*) FROM `".$dbtname."`");
  if (mysqli_num_rows($res)) {
    echo "Die Tabelle <samp>".$dbtname."</samp> enthält ".mysqli_fetch_row($res)[0]." Datensätze.";
  } else "<mark>Die Tabelle <samp>".$dbtname."</samp> wurde nicht gefunden.</mark>";
?>

► PHP-Dokumentation: mysqli_num_rows()

Allerdings kann man für diesen Zweck auch die PHP-Funktion mysqli_num_rows() direkt verwenden. Die Frage ist, welche der beiden Methoden performanter ist bzw. weniger Overhead erzeugt.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen";
  echo "<h3>".$dbtname."</h3>";
  $res = mysqli_query($my, "SELECT `id` FROM `".$dbtname."`");
  echo "Die Tabelle <samp>".$dbtname."</samp> enthält ".mysqli_num_rows($res)." Datensätze.";
?>

Datenbanktabelle ohne Datensätze kopieren

► MySQL-Dokumentation: CREATE TABLE

Um eine Tabelle ohne die enthaltenen Datensätze zu kopieren wird der Befehl CREATE TABLE … LIKE … verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $source_dbt = "adressen";
  $new_dbt    = "adressen2";
  $sql = "CREATE TABLE IF NOT EXISTS `".$new_dbt."` LIKE `".$source_dbt."`";
  if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$source_dbt."</samp> wurde kopiert.<br>";
  else echo "<mark>Tabelle <samp>".$source_dbt."</samp> konnte nicht kopiert werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Datenbanktabelle mit Datensätzen kopieren

► MySQL-Dokumentation: CREATE TABLE

Um eine Tabelle einschließlich der enthaltenen Datensätze zu kopieren wird der Befehl CREATE TABLE … AS SELECT … verwendet.

Die besonderen Attribute der Spalte id wie Key, Default und Extra werden nur dann berücksichtigt, wenn diese in der CREATE-Deklaration gesondert angegeben werden:
(`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $source_dbt = "adressen";
  $new_dbt    = "adressen3";
  $sql = "CREATE TABLE IF NOT EXISTS `".$new_dbt."` (`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) AS SELECT * FROM `".$source_dbt."`";
  if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$source_dbt."</samp> wurde kopiert.<br>";
  else echo "<mark>Tabelle <samp>".$source_dbt."</samp> konnte nicht kopiert werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Datenbanktabelle umbenennen

► MySQL-Dokumentation: ALTER TABLE

Um Datenbanktabellen umzubenennen, wird der Befehl ALTER TABLE … RENAME TO verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $old_dbtname = "adressen";
  $new_dbtname = "kunden";
  $sql = "ALTER TABLE `".$old_dbtname."` RENAME TO `".$new_dbtname."`";
  if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$old_dbtname."</samp> wurde umbenannt.";
  else echo "<mark>Tabelle <samp>".$old_dbtname."</samp> konnte nicht umbenannt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Datenbanktabelle leeren

► MySQL-Dokumentation: TRUNCATE TABLE

Um eine Tabelle zu leeren, das heißt, nur die enthaltenen Datensätze zu löschen, wird der Befehl TRUNCATE TABLE verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen2";
  $sql = "TRUNCATE TABLE `".$dbtname."`";
  if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$dbtname."</samp> wurde geleert.";
  else echo "<mark>Tabelle <samp>".$dbtname."</samp> konnte nicht geleert werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Datenbanktabelle löschen

► MySQL-Dokumentation: DROP TABLE

Um die Tabelle wieder zu löschen, wird der Befehl DROP TABLE verwendet:

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen2";
  $sql = "DROP TABLE `".$dbtname."`";
  if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$dbtname."</samp> wurde gelöscht.<br>";
  else echo "<mark>Tabelle <samp>".$dbtname."</samp> konnte nicht gelöscht werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Datenfeld hinzufügen

► MySQL-Dokumentation: ALTER TABLE

Um ein neues Datenfeld in eine Datenbanktabelle einzufügen, wird der Befehl ALTER TABLE … ADD COLUMN verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen";
  $sql = "ALTER TABLE `".$dbtname."` ADD COLUMN `email` TEXT AFTER `telefon`";
  if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$dbtname."</samp> wurde um eine Spalte ergänzt.<br>";
  else echo "<mark>Tabelle <samp>".$dbtname."</samp> konnte nicht um eine Spalte ergänzt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Datenfeld kopieren

► MySQL-Dokumentation: ALTER TABLE UPDATE

Um eine Kopie eines Datenfeldes anzulegen, erzeugt man zunächst ein neues Datenfeld mit den Eigenschaften des Quellfeldes und kopiert die enthaltenen Daten mit UPDATE … SET … in das neue Feld.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen";
  $old_fieldname = "telefon";
  $new_fieldname = "telefon2";
  $sql = "ALTER TABLE `".$dbtname."` ADD COLUMN `".$new_fieldname."` VARCHAR(30) AFTER `".$old_fieldname."`";
  if (mysqli_query($my, $sql)) echo "Tabelle <samp>".$dbtname."</samp> wurde um eine Spalte ergänzt.<br>";
  else echo "<mark>Tabelle <samp>".$dbtname."</samp> konnte nicht um eine Spalte ergänzt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
  
  $sql = "UPDATE `".$dbtname."` SET `".$new_fieldname."` = `".$old_fieldname."`";
  if (mysqli_query($my, $sql)) echo "Datenfeld <samp>".$old_fieldname."</samp> wurde nach <samp>".$new_fieldname."</samp> kopiert.<br>";
  else echo "<mark>Datenfeld <samp>".$old_fieldname."</samp> konnte nicht nach <samp>".$new_fieldname."</samp> kopiert werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Datenfeld umbenennen

► MySQL-Dokumentation: ALTER TABLE

Um ein Datenfeld umzubenennen, wird der Befehl ALTER TABLE … CHANGE COLUMN verwendet. Die Eigenschaften können ebenfalls geändert werden, müssen aber in jedem Fall angegeben werden.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen";
  $old_fieldname = "geburtstag";
  $new_fieldname = "geburtsdatum";
  $sql = "ALTER TABLE `".$dbtname."` CHANGE COLUMN `".$old_fieldname."` `".$new_fieldname."` VARCHAR(10)";
  if (mysqli_query($my, $sql)) echo "Datenfeld <samp>".$old_fieldname."</samp> wurde umbenannt.<br>";
  else echo "<mark>Datenfeld <samp>".$old_fieldname."</samp> konnte nicht umbenannt werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Datenfeld ändern

► MySQL-Dokumentation: ALTER TABLE

Um die Eigenschaften eines Datenfeldes zu ändern, wird der Befehl ALTER TABLE … MODIFY COLUMN verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen";
  $fieldname = "email";
  $sql = "ALTER TABLE `".$dbtname."` MODIFY COLUMN `".$fieldname."` VARCHAR(30)";
  if (mysqli_query($my, $sql)) echo "Datenfeld <samp>".$fieldname."</samp> wurde geändert.<br>";
  else echo "<mark>Datenfeld <samp>".$fieldname."</samp> konnte nicht geändert werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>

Datenfeld löschen

► MySQL-Dokumentation: ALTER TABLE

Um ein Datenfeld zu löschen, wird der Befehl ALTER TABLE … DROP COLUMN verwendet.

<?php
  header("Content-Type: text/html; charset=utf-8");
  require("db.inc.php");
  
  $dbtname = "adressen";
  $fieldname = "telefon2";
  $sql = "ALTER TABLE `".$dbtname."` DROP COLUMN `".$fieldname."`";
  if (mysqli_query($my, $sql)) echo "Datenfeld <samp>".$fieldname."</samp> wurde gelöscht.<br>";
  else echo "<mark>Datenfeld <samp>".$fieldname."</samp> konnte nicht gelöscht werden.</mark><br>Fehler: ".mysqli_error($my)."<br>";
?>