Informationssysteme-MySQL

Aus Debacher-Wiki
Wechseln zu:Navigation, Suche

Der vorliegende Text gliedert sich in folgende Teile:

I. Datenschutz

II. Projektmanagement

III. Modellbildung

IV. PHP

V. MySQL

VI. Einbindung von MySQL in PHP

VII. Erweiterte Möglichkeiten in MySQL

VIII. Beispielaufgaben

IX. Anhang


V.MySQL

SQL steht für Structured Query Language, was so viel wie strukturierte Abfragesprache bedeutet. Diese Sprache ist recht weit verbreitet und wird z.B. auch von Ms-Access unterstützt. Bevor wir uns mit der Einbindung von MySQL in das PHP-System kümmern, erst einmal eine Zu­sammenstellung der möglichen MySQL Befehle oder wie es in der Dokumentation heißt Clauses.

Alle Befehle lassen sich auch direkt an MySQL absenden. Dazu muss man in einer Telnet/SSH-Sit­zung folgendes Programm aufrufen

 /usr/bin/mysql datenbankname

In unserem Fall heißt die Datenbank kurs. Datenbanken dürfen nur von einem Benutzer mit entspre­chenden Rechten eingerichtet werden (/usr/bin/mysqladmin create kurs).

1.Create

Hiermit wird eine neue Datentabelle in einer vorhandenen Datenbank eingerichtet. Der Name der Da­tenbank wurde schon vorher festgelegt.

CREATE TABLE tabellen_name (
  feld_name    feld_typ   [feld_optionen]
  [,feld_name    feld_typ   [feld_optionen]]
  [,feld_name    feld_typ   [feld_optionen]] );

Der Feldname kann (fast) frei vergeben werden.

Als Feld Typen stehen u.a. zur Verfügung:

CHAR(Länge)	letztendlich Strings (max. 255 Zeichen)
INT			ganze Zahlen 
REAL		Real Zahlen
DATE		Datum in der Art „2000-04-03"
TEXT		Text, maximale Länge 65535 (2^16 - 1) Zeichen

Als Optionen sind möglich:

not null		das Feld darf nicht leer bleiben
default wert		Wird beim Speichern kein Wert angegeben, so wird diese Vorgabe genommen.
auto_increment		ideal für Primärschlüsselfelder, da MySQL den Wert selbstständig erhöht
primary key		Primärschlüssel (es kann nur einen geben).

Die Tabelle darf auch über mehrere Indexfelder verfügen, die folgendermaßen definiert werden:

CREATE [UNIQUE] INDEX index_name ON tabellen_name (

feld_name [,feld_name] ); Die optionale Angabe unique bewirkt, dass das entsprechende Feld nicht doppelt vorkommen darf.

Beispiel:

CREATE TABLE telefonliste (
 name CHAR(20),
 vorname CHAR(20),
 telefon CHAR(15)
);

2.Drop

Drop löscht Informationen. Eine Tabelle wird einfach gelöscht mit:

DROP TABLE tabellen_name

Ein Index wird entsprechend gelöscht mittels:

DROP INDEX index_name FROM tabellen_name

3.Alter

Ändert die Definition einer Tabelle.

ALTER TABLE tabellen_name DROP feld_name;

löscht ein Feld aus der Tabelle heraus.

ALTER TABLE tabellen_name ADD feld_name feld_typ feld_optionen

erweitert die Tabelle um ein Feld.

Der Befehl erlaubt sehr umfangreiche Änderungen an der Tabelle.

4.Insert

Insert dient zum Einfügen von Datensätzen in die Datenbank:

INSERT INTO tabellen_name 
  [(feld_name, feld_name, ..)]
  VALUES (wert1, wert2,..);

Werden die optionalen Spaltennamen weggelassen, so müssen alle Werte in der richtigen Reihenfol­ge angegeben werden. Will man nur einige Werte eingeben, so müssen die zugehörigen Spaltenna­men an­gegeben sein.

Beispiel:

INSERT INTO telefonliste VALUES(
 'Meier',
 'Klaus',
 '4711'
);	

Texte müssen in Hochkommata eingeschlossen werden, Zahlen nicht. Die Telefonnummer ist im vor­liegenden Beispiel ein Text.

5.Select

Dies ist sicherlich die meistbenutzte clause. Hiermit wird eine vorhandene Datenbank abgefragt. Die (vereinfachte) Syntax lautet:

SELECT feld_name [,feld_name] FROM tabellen_name
  [WHERE feld_name vergleichs_operator wert]
  [ORDER BY feld_name [DESC]];

Als feld_name nach SELECT ist auch * als Jokerzeichen für alle Felder zulässig. Vergleichsoperato­ren können sein:

<		kleiner als
> 		größer als
=		gleich
<=		kleiner oder gleich
>=		größer oder gleich
<>		ungleich
LIKE	Operator für Mustervergleich mit den Jokern _ und %
       _    	steht für ein beliebiges Zeichen
       %   	steht für beliebig viele (auch 0) Zeichen 
IS 		auf NULL-Werte kann man nur mit IS NULL und nicht mit = vergleichen

Beispiele:

  SELECT * FROM telefonliste
  liefert als Ergebnis die vollständige Tabelle
  SELECT * FROM telefonliste ORDER BY telefon
  liefert die vollständige Telefonliste sortiert nach Telefonnummern
  SELECT name FROM telefonliste
  liefert alle Namen aus der Tabelle	
  SELECT name FROM telefonliste WHERE name LIKE ‘Meier%’
  liefert die Namen alle Einträge, die mit Meier beginnen.
  SELECT name FROM telefonliste WHERE telefon IS NULL
  liefert die Namen alle Einträge, bei denen keine Telefonnumer vorhanden ist.

6.Delete

Hiermit werden Datensätze einer Tabelle gelöscht:

DELETE FROM tabellen_name 
WHERE spalten_name vergleichs_operator wert

Beispiel:

DELETE FROM telefonliste WHERE telefon=’4711’

7.Update

Aktualisiert einen vorhandenen Datensatz:

UPDATE tabellen_name SET spalten_name=wert
	WHERE spalten_name vergleichs_operator wert

Beispiel:

UPDATE telefonliste SET telefon=’4711' WHERE telefon=’0815'

8.Show

Dieser Befehl ist recht nützlich um sich einen Überblick über die vorhandenen Datenbanken bzw. Tabellen zu verschaffen. Die Liste aller Datenbanken bekommt man mit:

SHOW DATABASES;

Die Liste der Tabellen in der aktuellen Datenbank erhält man mit:

SHOW TABLES;


VI. Einbindung von MySQL in PHP

Will man eine Datenbank ansprechen, so muss zuerst eine Verbindung zum MySQL-Server aufge­baut werden. Dazu dient der Befehl:

 mysql_connect(hostname, username, password)

Solange die Datenbank auf dem gleichen Rechner liegt wie der Webserver, kann man hier einfach „localhost” angeben. Also

 mysql_connect("localhost", "userkurs", "passkurs");

Wenn kein Benutzername angegeben wird, dann wird der aufrufende Benutzer genommen, also „wwwrun“, die Benutzerkennung des Apache. Ein Passwort darf dann nicht gesetzt sein. Die meisten mysql-Befehle von PHP lassen eine interessante Variation zu, stellt man ihnen ein „@“-Zeichen voran, so werden eventuelle Fehlermeldungen unterdrückt (müssen dann aber abgefragt werden s.u.).

 @mysql_connect("localhost", "userkurs", "passkurs");

Nach Abschluss der Datenbankbenutzung sollte das Programm die Verbindung auch wieder korrekt beenden.

 mysql_close(hostname)

Gibt man hier keinen Hostnamen an, so werden alle Verbindungen beendet:

 mysql_close()


1.Datenbankbefehle mittels PHP

PHP stellt eine Funktion zur Verfügung, mit deren Hilfe clauses direkt an mySQL weitergegeben werden können.

Dazu dient:

 ergebnisvariable=mysql_db_query(Datenbankname, Abfrage)

Konkret könnte das folgendermaßen aussehen:

 $result=mysql_db_query("kurs","SELECT * FROM telefonliste");

Bei komplexeren Anfragen kann es sinnvoll sein die beiden Strings für Datenbankname und Abfrage in Variablen auszulagern.

 $db="kurs";
 $query="SELECT * FROM telefonliste";
 $result=mysql_db_query($db, $query);

Die Ergebnisvariable hat den Wert False, wenn ein Fehler auftrat. Der Fehler kann dann mittels mysql_errno() bzw. mysql_error() abgefragt werden.

 if (mysql_errno() != 0):
   echo "Fehler ".mysql_errno().": ".mysql_error();
 endif;

Falls die Datenbankabfrage erfolgreich war und Ergebnisse zurückliefert, ist die Ergebnisvariable ein wichtiger Schlüssel zu Abfrage der Ergebnisse.

Die Zahl der gefundenen Datensätze kann mit

 zahl=mysql_NumRows(ergebnisvariable)

Beispiel:

 $anzahl=mysql_NumRows($result)


Das folgende Beispiel gibt die Anzahl der Datensätze in unserer Tabelle aus:

<html><head><title>Datenbank-Beispiel 1</title></head><body>
 <?
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="select * from telefonliste";
  $result=mysql_db_query($db, $query);
  $anzahl=mysql_numrows($result);
  echo "Anzahl $anzahl Datensaetze";
 ?>
 </body></html>

Etwas aufwändiger wird es, wenn man an das eigentlich Ergebnis der Abfrage heran will. Das Ergeb­nis wird als Tabelle dargestellt, deren Reihen die Datensätze sind und deren Spalten die Attribu­te dar­stellen.

Man kann man die Datenfelder einzeln abfragen:

$daten=mysql_result(ergebnisvariable, datensatznummer, feld_name)

Beispiel:

$wert=mysql_result($result, 0, ”telefon”);

Hiermit wird die Telefonnummer des ersten Datensatzes zurückgeliefert.

Ein vollständiges Beispiel hierzu:

<html><head><title>Datenbank-Beispiel 2</title></head><body>
 <?										
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="select * from telefonliste";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() != 0):
    echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
  $anzahl=mysql_numrows($result);
  echo "Anzahl $anzahl Datensaetze<p> ";
  $tel=mysql_result($result, 0, "telefon");
  echo "Datensatz 1:<br>Telefon: $tel<p>";
 ?>
 </body></html></nowikiY

Will man alle Datensätze und alle Felder ausgeben, so muss das Beispiel um ein Schleifenkonstrukt erweitert werden.
 <nowiki><html><head><title>Datenbank-Beispiel 3</title></head><body>
<?
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="select * from telefonliste";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() != 0):
    echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
  $anzahl=mysql_numrows($result);
  echo "Anzahl $anzahl Datensaetze<p> ";
  for ($i=0;$i<$anzahl;$i++):
   $name=mysql_result($result, $i, "name");
   $vorn=mysql_result($result, $i, "vorname");
   $tel=mysql_result($result, $i, "telefon");
   echo "Datensatz $i:<br>Name: $name<br>Vorname: $vorn<br>Telefon $tel<p>";
  endfor;
 ?>
 </body></html>

2.Das Programmpaket Telefonliste

Zusammengefasst ergeben sich drei Listings:

Eingabe-Formular
Auswertung der Formulardaten
Anzeige der vorhandenen Datensätze

Die Listings der Reihe nach.

Das Eingabe-Formular de_form.html

<html><head><title>Telefonliste</title></head><body>
 <H1>Telefonliste-Eingabeformular</H1>
 <form action="de_ein.php" method="get">
 Name<br><input type="text" name="name" size="30" maxlength="60">
 <p>Vorname<br><input type="text" name="vorname" size="30" maxlength="60">
 <p>Telefon<br><input type="text" name="telefon" size="30" maxlength="60">
 <p><input type="submit" value="Absenden">
 <input type="reset" value="Verwerfen">
 </form>
 </body></html>

Das Programm zur Auswertung der Formulardaten: de_ein.php

<html><head><title>Telefonliste-Formular-Auswertung</title>
 </head><body><h1>Telefonliste-Formular-Auswertung
 </h1>
 <?
  $name=   $_REQUEST['name'];
  $vorname=$_REQUEST['vorname'];
  $telefon=$_REQUEST['telefon'];
  echo "Nachname: $name<p>";
  echo "Vorname: $vorname<p>";
  echo "Telefon: $telefon<p>";
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="insert into telefonliste values ('$name','$vorname','$telefon')";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() != 0):
    echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
  mysql_close();
 ?>
 <p>Zur <a href="de_aus.php">Liste der Datensätze</a>
 </body></html>

Anzeige der vorhandenen Datensätze: de_aus.php

<html><head><title>Telefonliste-Ausgabe</title></head><body>
 <h1>Telefonliste Datensatzanzeige</h1>
 <?
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="select * from telefonliste";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() != 0):
    echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
  $anzahl=mysql_numrows($result);
  echo "Anzahl $anzahl Datensaetze<p> ";
  for ($i=0;$i<$anzahl;$i++):
   $name=mysql_result($result, $i, "name");
   $vorn=mysql_result($result, $i, "vorname");
   $tel=mysql_result($result, $i, "telefon");
   echo "Datensatz $i:<br>Name: $name<br>Vorname: $vorn<br>Telefon $tel<p>";
  endfor;
 ?>
 Zum <a href="de_form.html">Eingabeformular</a>
 </body></html>

3.Ein vollständiges Beispiel: Gästebuch

Im folgenden Abschnitt soll eine vollständige Anwendung realisiert werden. Dazu nehmen wir als Beispiel ein Gästebuch. Dabei handelt es sich um eine Online-Anwendung bei der jeder Benutzer Ein­träge machen kann, die dann allgemein zugänglich sind.

Die Datenstruktur Für das Gästebuch benutzen wir folgende Datenstruktur:

id	int		Laufende Nummer für den Eintrag
datum	char(10)	Datum des Eintrages
aktiv	char(1)		Für eine eventuelle Moderation
name	char(60)	Name des Absender
email	char(60)	Mail-Adresse des Absenders
kommentar  text	Die Nachricht im Gästebuch

Die Tabelle richten wir im einfachsten Fall mit einem SQL-Editor ein, wobei die Datenbank schon eingerichtet sein muss.

CREATE TABLE gaesteb (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
datum CHAR(10),
aktiv CHAR(1),
name CHAR(60),
email CHAR(60),
kommentar TEXT  
);

Damit ist die Tabelle angelegt. Die Definition stellt sicher, dass die Einträge alle eine unterschiedli­che ID bekommen, da diese vom MySQL automatisch vergeben wird. In das Datumsfeld soll der Ein­tag automatisch vorgenommen werden und das Feld aktiv dient dazu in einem zweiten Schritt eine Mode­ration zu realisieren.

Interessant sind hier die zusätzlichen Angaben im Feld id

NOT NULL		verhindert eine leere Eingabe
AUTO_INCREMENT		erhöht den Wert automatisch
PRIMARY KEY		definiert das Feld als Primärschlüssel


Das Formular

Zur Eingabe dient eine einfache HTML-Seite, die ein Formular definiert. Die ersten drei Felder der Definition tauchen hier nicht auf, das sie automatisch generiert werden sollen.

gaesteb_ein.htm

<html><head><title>Gästebuch</title></head><body>
 <center><h1>Gästebuch-Eingabeformular</h1></center>
 <form action="gaesteb_ein.php" method="get">
 Name<br> 
 <input type="text" name="name" size="30" maxlength="60">
 <p>E-Mail Adresse<br>
 <input type="text" name="email" size="30" maxlength="60">
 <p>Kommentar<br>
 <textarea name="kommentar" rows="6" cols="40"></textarea>
 <p><input type="submit" value="Absenden">
 <input type="reset"  value="Verwerfen">
 </form>
 </body></html>

Das Script zur Auswertung

Zum Eintragen der Daten in die Datenbank wird ein PHP-Script benötigt, das recht kurz ausfallen kann, wenn man darauf verzichtet fehlerhafte Eingaben abzufangen.

gaesteb_ein.php

<html><head>
 <title>Gästebuch Neueintrag in Datenbank</title>
 </head><body>
 <h1 align=center>Neueintrag in das Gästebuch</h1>
 <?
  $aktiv="1";
  $datum=date("d.m.Y");
  $name=$_REQUEST['name'];
  $email=$_REQUEST['email'];  
  $kommentar=$_REQUEST['kommentar'];
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="INSERT INTO gaesteb  VALUES(NULL, '$datum', '$aktiv', '$name', '$email', '$kommentar')";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() == 0):
   echo "Datensatz ". mysql_insert_id(). " erfolgreich eingetragen";
  else:
   echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
 ?>
 <br>
 </body></html>

Das Feld aktiv setzen wir hier auf den Wert 1 für aktiv, so dass erst einmal keine Moderation notwen­dig ist. Das aktuelle Datum lassen wir vom System ermitteln mit dem Befehl date. Der Vorteil dieses Befehls besteht darin, dass man das Datumsformat frei definieren kann. Im Listing wird das Datum in der Form 23.05.2000 definiert. Das d steht für Tag, das m für Monat und das Y für Jahr in vierstelliger Form. Statt einer id übergeben wir NULL (also keinen Wert) an MySQL, damit der Wert automatisch fest­gelegt werden kann.

Über die bedingte Anweisung wird zurückgemeldet, ob das Anlegen des Datensatzes erfolgreich war oder nicht. Die Funktion

 mysql_errno()	

liefert eines eventuelle Fehlermeldung von MySQL als Zahl zurück. Der Wert 0 steht für fehlerfreie Ausführung. Wenn die Ausführung fehlerfrei war, dann wird ein entsprechender Text und die id des Datensatzes zurückgeliefert. Die id müssen wir mit

 mysql_insert_id()

ermitteln, da sie von MySQL festgelegt wurde. Die Punkte in der Ausgabezeile dienen der Stringver­knüpfung. Es werden hier zwei Texte mit dem Ergebnis eines Funktionsaufrufes verknüpft. PHP kon­vertiert den Wert der Funktion dazu automatisch in einen String.

Falls ein Fehler aufgetreten ist, wird die Fehlernummer und die zugehörige Fehlerbeschreibung aus­gegeben. Das ist für die Fehlersuche ganz hilfreich.


Das Script zur Anzeige der Datensätze

Will man alle vorhandenen und aktivierten Datensätze anzeigen, so kann das mit dem folgenden PHP-Script geschehen:

gaesteb_aus.php

<html><head>
 <title>Gästebuch, vorhandene Einträge</title>
 </head><body>
 <h1 align=center>Vorhandene Einträge im Gästebuch</h1>
 <?
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="SELECT * FROM gaesteb";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() != 0):
    echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
  $anzahl=mysql_numrows($result);
  if ($anzahl == 0):
   echo "Es liegen bisher keine Einträge vor";
  else:
   for ($i=0;$i<$anzahl;$i++):
     $id=mysql_result($result, $i, "id");
     $datum=mysql_result($result, $i, "datum");
     $aktiv=mysql_result($result, $i, "aktiv");
     $name=mysql_result($result, $i, "name");
     $email=mysql_result($result, $i, "email");
     $kommentar=mysql_result($result, $i, "kommentar");
     if ($aktiv==1):
       echo "Datensatz $id: ";
       echo "<a href=\"mailto:$email\">$name</a> meinte am $datum<p>";
       echo "$kommentar<p><hr><p>";
     endif;
   endfor;
  endif;
 ?>
 <br>
 </body></html>

Dieses Script wertet zuerst aus, ob überhaupt Datensätze vorhanden sind. Wenn ja, dann werden alle Datensätze ausgegeben, die aktiviert wurden.


Moderation des Gästebuches

Der Eigentümer des Gästebuches muss die Möglichkeit haben Datensätze zu bearbeiten, zu löschen und vor allem auch zu aktivieren bzw. deaktivieren. Im Einfachsten Fall bekommt er dazu eine Sammlung von Scripten, die eventuell auch noch mit ei­nem Passwort gegen unbefugten Zugriff gesichert werden könnten.

Zuerst ein Script, das die Datensätze auflistet und jeweils auf das Bearbeitungsformular verlinkt.

gaestb_mod1.php

<html><head>
 <title>Gästebuch Moderation Liste vorhandener Einträge</title>
 </head><body>
 <h1 align=center>Moderation: Liste vorhandener Einträge</h1>
 <?
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="SELECT * FROM gaesteb";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() != 0):
    echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
  $anzahl=mysql_numrows($result);
  if ($anzahl == 0):
    echo "Es liegen bisher keine Einträge vor";
  else:
    for ($i=0;$i<$anzahl;$i++) {
     $id=mysql_result($result, $i, "id");
     $datum=mysql_result($result, $i, "datum");
     $aktiv=mysql_result($result, $i, "aktiv");
     $name=mysql_result($result, $i, "name");
     $email=mysql_result($result, $i, "email");
     $kommentar=mysql_result($result, $i, "kommentar");
     echo "<a href=\"mailto:$email\">$name</a> am $datum";
     if ($aktiv==1):
       echo " (aktiviert)";
     endif;
     echo "<p>$kommentar<p>";
     echo "<a href=\"gaesteb_mod2.php?id=$id\">bearbeiten</a> &nbsp ";
     echo "<a href=\"gaesteb_mod4.php?id=$id\"> löschen</a>";  
     echo "<hr>";
   }
  endif;
 ?>
 <br>
 </body></html>

Für jeden vorhandenen Eintrag wird ein Link zum Bearbeiten erzeugt, der das Moderationsformular aufruft und die ID des Datensatzes als Parameter übergibt. Entsprechend wird ein Link zum Löschen eingefügt,


Der zweite Teil des Programms zum Bearbeiten erwartet als Übergabeparameter die ID des Datensat­zes und holt damit alle Angaben aus der Datenbank und stellt sie in einem Formular dar.

gaestb_mod2.php

<html><head>
 <title>Gästebuch Moderation vorhandener Einträge Teil 2</title>
 </head><body>
 <h1 align=center>Moderation vorhandener Einträge Datensatz
 <?
  $id=$_REQUEST['id'];
  echo " $id</h1>";
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="SELECT * FROM gaesteb where id=$id";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() != 0):
    echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
  $anzahl=mysql_numrows($result);
  if ($anzahl == 0):
   echo "Es liegt  kein Eintrag vor";
  else:
   $id=mysql_result($result, 0, "id");
   $datum=mysql_result($result, 0, "datum");
   $aktiv=mysql_result($result, 0, "aktiv");
   $name=mysql_result($result, 0, "name");
   $email=mysql_result($result, 0, "email");
   $kommentar=mysql_result($result, 0, "kommentar");
   echo "<form action=\"gaesteb_mod3.php\" method=\"get\">";
   echo "ID: $id";
   echo "<input type=\"HIDDEN\" name=\"id\"  value=\"$id\">";
   echo "<p>Datum: $datum";
   echo "<input type=\"HIDDEN\" name=\"datum\"  value=\"$datum\">";
   echo "<p>Aktiviert <input type=\"CHECKBOX\" name=\"aktiv\"";
   if ($aktiv=="1"):
     echo "CHECKED";
   endif;
   echo ">";
   echo "<p>Name<br><input type=\"text\" name=\"name\" size=\"30\" maxlength=\"60\" value=\"$name\">";
   echo "<p>E-Mail Adresse<br><input type=\"text\" name=\"email\" size=\"30\" maxlength=\"60\" value=\"$email\">";
   echo "<p>Kommentar<br><textarea name=\"kommentar\" rows=\"6\" cols=\"40\">$kommentar</textarea>";
   echo "<p><input type=\"submit\" value=\"Absenden\">";
   echo "<input type=\"reset\"  value=\"Verwerfen\">";
   echo "</form>";
  endif;
 ?>
 <br>
 </body></html>

Der letzte Teil des Moderations-Programmes muss dann die gemachten Änderungen in die Daten­bank eintragen. Dazu dient die Update-Funktion von SQL.

gaestb_mod3.php

<html><head>
 <title>Gästebuch Änderungseintrag in Datenbank</title>
 </head><body>
 <h1 align=center>Änderungseintrag in das Gästebuch</h1>
 <?
  if ($_REQUEST['aktiv']=="on"):
    $aktiv="1";
  else:
    $aktiv="0";
  endif;
  $name=$_REQUEST['name'];
  $email=$_REQUEST['email'];
  $kommentar=$_REQUEST['kommentar'];
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $query="UPDATE gaesteb SET aktiv='$aktiv', name='$name', email='$email', komment­ar='$kommentar' WHERE id=$id";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() == 0):
   echo "Datensatz $id erfolgreich aktualisiert";
  else:
    echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
 ?>
 <br>
 </body></html>


Nun fehlt nur noch das Script zum Löschen eines Datensatzes. Die Ähnlichkeit mit dem letzten Script dürfte nicht überraschen:

gaestb_mod4.php

<html><head>
 <title>Gästebuch L&oschen eines Eintrages</title>
 </head><body>
 <h1 align=center>Löschen eines Eintrages im Gästebuch</h1>
 <?
  mysql_connect("localhost", "userkurs", "passkurs");
  $db="kurs";
  $id=$_REQUEST['id'];
  $query="DELETE FROM gaesteb WHERE id=$id";
  $result=mysql_db_query($db, $query);
  if (mysql_errno() == 0):
   echo "Datensatz $id erfolgreich entfernt";
  else:
   echo "Fehler ".mysql_errno().": ".mysql_error();
  endif;
 ?>
 <br>
 </body></html>


Mit diesem Beispiel ist das Grundgerüst für ein Gästebuch erstellt. Es fehlen aber noch eine Reihe von Sicherheitsabfragen, z.B. vor dem Löschen eines Datensatzes.


VII. Erweiterte Möglichkeiten in MySQL

MySQL ist ein Datenbanksystem, welches in einem enormen Tempo weiterentwickelt wird. Dadurch kommen ständig neue Möglichkeiten hinzu, die man in den Büchern noch nicht beschrieben findet.

Seit einiger Zeit gibt es eine strategische Allianz zwischen MySQL und dem Softwarehaus SAP. Die bisherige SAP DB wird nun unter dem Namen MaxDB zur Verfügung gestellt. Bei vielen Linux-Dis­tributionen ist diese Datenbank als Ergänzung zum eigentlichen MySQL-Paket vorhanden.

Wenn es um Funktionen von MySQL geht, dann muss man sehr auf die Versionsnummer achten. Zur Zeit aktuell ist die Version 5.0, es sind aber noch viele ältere Installationen vorhanden, weit verbreitet ist die Version 4.1. Abfragen kann man die auf dem eigenen Rechner vorliegende Version, wenn man in einem Shell-Fenster mysql -V eingibt.

1.Verknüpfungen in MySQL: Joins

Sowie eine Datenbank über mehrere verknüpfte Tabellen verfügt macht es Sinn sich darüber Gedan­ken zu machen, wie man diese Datenbank einfach abfragen kann. Zur Erläuterung wird wird von ei­nen Auszug aus der DeutschlandDB ausgegangen, konkret den Tabellen Stadt und Land.

CREATE TABLE Land (
 ID tinyint(4) NOT NULL auto_increment,
 Name char(35) NOT NULL default ,
 PRIMARY KEY  (ID)
) TYPE=MyISAM;

--
-- Dumping data for table `Land`
--

INSERT INTO Land VALUES (1,'Bremen');
INSERT INTO Land VALUES (2,'Schleswig-Holstein');
INSERT INTO Land VALUES (3,'Niedersachsen');
INSERT INTO Land VALUES (4,'Hamburg');
INSERT INTO Land VALUES (5,'Hessen');
...

Die Tabelle Land verfügt über insgesamt 16 Einträge, die folgende Tabelle Stadt über 12 Einträge.

CREATE TABLE Stadt (
 ID int(11) NOT NULL auto_increment,
 Name char(50) NOT NULL default ,
 Bundesland int(11) NOT NULL default '0',
 PRIMARY KEY  (ID)
) TYPE=MyISAM;

--
-- Dumping data for table `Stadt`
--

INSERT INTO Stadt VALUES (1,'Berlin',10);
INSERT INTO Stadt VALUES (2,'Freie Hansestadt Bremen',1);
INSERT INTO Stadt VALUES (3,'Freie und Hansestadt Hamburg',4);
INSERT INTO Stadt VALUES (4,'Dresden',12);
INSERT INTO Stadt VALUES (5,'Erfurt',11);
INSERT INTO Stadt VALUES (6,'Hannover',3);
INSERT INTO Stadt VALUES (7,'Kiel',2);
INSERT INTO Stadt VALUES (8,'Magdeburg',13);
INSERT INTO Stadt VALUES (9,'Wiesbaden',0);
INSERT INTO Stadt VALUES (10,'Frankfurt am Main',5);
INSERT INTO Stadt VALUES (11,'Dortmund',6);
INSERT INTO Stadt VALUES (12,'Essen',6);

Bei der Stadt Wiesbaden liegt ein fehlerhafter Eintrag vor. Die Zuordnung der Städte zu den Ländern erfolgt über das dritte Datenfeld, das die ID des Landes be­inhaltet.

Cross Join

Zur Verknüpfung dieser beiden Tabellen hat man verschiedene Möglichkeiten. Im einfachsten Fall versucht man eine Anfrage wie:

select * from Stadt, Land

Als Antwort auf diese Anfrage erhält man ein Ergebnis mit 192 Datensätzen:

Die Abfrage liefert 192 Datensätze.
Name Tabelle Datentyp Länge Schalter
ID Stadt int 11 not_null primary_key auto_increment
Name Stadt string 50 not_null
Bundesland Stadt int 11 not_null
ID Land int 4 not_null primary_key auto_increment
Name Land string 35 not_null


Lfd# ID Name Bundesland ID Name
1 1 Berlin 10 1 Bremen
2 2 Freie Hansestadt Bremen 1 1 Bremen
3 3 Freie und Hansestadt Hamburg 4 1 Bremen
4 4 Dresden 12 1 Bremen
5 5 Erfurt 11 1 Bremen

MySQL hat hier das Kreuzprodukt (kartesisches Produkt) der beiden Tabellen gebildet und einfach alle Datensätze der ersten Tabelle mit jeweils allen Datensätzen der zweiten Tabelle verknüpft. Die Anzahl 192 ergibt sich aus dem Produkt 16x12 der Einzeldatensätze.

Es spielt hier also keine Rolle, ob die Tabellen in irgendeinem Zusammenhang miteinander stehen. Will man einen Zusammenhang zwischen den Tabellen definieren, so benötigt man die Joins.

Inner Join

Bei der Abfrage

select * from Stadt inner join Land on  Stadt.Bundesland = Land.ID

erhält man 11 Treffer. Das sind genau die Datensätze, bei denen zu Stadt.Bundesland ein passender Eintrag unter Land.ID zu finden ist. Durch den Fehler bei Wiesbaden sind dies eben nur noch 11 Städ­te.

Left Join

Die Abfrage

select * from Stadt left join Land on  Stadt.Bundesland = Land.ID

liefert das folgende Ergebnis: Die Abfrage liefert 12 Datensätze. Name Tabelle Datentyp Länge Schalter ID Stadt int 11 not_null primary_key auto_increment Name Stadt string 50 not_null Bundesland Stadt int 11 not_null ID Land int 4 primary_key auto_increment Name Land string 35


Lfd# ID Name Bundesland ID Name 1 1 Berlin 10 10 Berlin 2 2 Freie Hansestadt Bremen 1 1 Bremen 3 3 Freie und Hansestadt Hamburg 4 4 Hamburg 4 4 Dresden 12 12 Sachsen 5 5 Erfurt 11 11 Thueringen 6 6 Hannover 3 3 Niedersachsen 7 7 Kiel 2 2 Schleswig-Holstein 8 8 Magdeburg 13 13 Sachsen-Anhalt 9 9 Wiesbaden 0


10 10 Frankfurt am Main 5 5 Hessen ...

Hier taucht jeder Datensatz der Tabelle Stadt auf, auch der von Wiesbaden. Falls möglich wurde der Datensatz mit einem passenden Datensatz der Tabelle Land verknüpft.

Stadt ist bei dieser Anfrage die führende Tabelle und jeder Datensatz dieser Tabelle taucht im Ergeb­nis auf! Die Zahl der Ergebnisse kann aber höher werden, wenn es in der zweiten Tabelle mehrere passende Einträge gibt.

Right Join

Eine einfache Umstellung der Tabellen gegenüber dem left join stellt right join dar. select * from Stadt right join Land on Stadt.Bundesland = Land.ID

Jetzt ist Land die führende Tabelle und es muss daher mindestens 16 Treffer geben

Da aber für zwei Städte, nämlich Dortmund und Essen das Land Nordrhein-Westfalen eingetragen ist ergeben sich hier sogar 17 Treffer.

Die Abfrage liefert 17 Datensätze. Name Tabelle Datentyp Länge Schalter ID Stadt int 11 primary_key auto_increment Name Stadt string 50

Bundesland Stadt int 11

ID Land int 4 not_null primary_key auto_increment Name Land string 35 not_null

Lfd# ID Name Bundesland ID Name 1 2 Freie Hansestadt Bremen 1 1 Bremen 2 7 Kiel 2 2 Schleswig-Holstein 3 6 Hannover 3 3 Niedersachsen 4 3 Freie und Hansestadt Hamburg 4 4 Hamburg 5 10 Frankfurt am Main 5 5 Hessen 6 11 Dortmund 6 6 Nordrhein-Westfalen 7 12 Essen 6 6 Nordrhein-Westfalen 8


7 Rheinland-Pfalz 9


8 Mecklenburg-Vorpommern 10


9 Brandenburg 11 1 Berlin 10 10 Berlin

Man sieht hier deutlich, dass für einige Länder keine einzige Stadt eingetragen ist. Statt select * from Stadt right join Land on Stadt.Bundesland = Land.ID könnte man auch select * from Land left join Stadt on Stadt.Bundesland = Land.ID schreiben. Der Right-Join ist also nur ein Spezialfall des Left-Join.

Insgesamt liefern Left-Join und Right-Join, die zusammen auch als Outer-Joins bezeichnet werden, immer mindestens soviele Treffer, wie die führende Tabelle Elemente hat. Ein Inner-Join kann nie mehr Treffer liefern, als die kleinste Tabelle Elemente besitzt. Die meisten Treffer liefert immer der Cross-Join. Selbstverständlich lassen sich auch mehr als zwei Tabellen gleichzeitig miteinander verknüpfen, wo­bei man aber auf die Zahl der Treffer aufpassen muss. Zusätzlich lassen sich die Verknüpfungen noch ganz normal um Bedingungen ( WHERE ..) und Sortierungen (ORDER BY...) erweitern.


2.Subqueries

Beginnend mit der Version 4.1 beherrscht MySQL die Unterabfragen. Mit einer Unterabfrage kann man z.B. einfach ermitteln, welche Städte in einem Bundesland liegen, dessen Name mit „B“ an­fängt.

SELECT * FROM Stadt WHERE Bundesland IN (SELECT ID FROM Land WHERE Name LIKE 'B%');

Sicherlich kann man das Ergebnis auch über eine Abfrage erhalten, die einen Join benutzt:

SELECT DISTINCT Stadt.* FROM Stadt,Land WHERE Stadt.Bundesland=Land.ID AND Land.Name LIKE 'B%';

die Subquery ist aber übersichtlicher, weil klarer gegliedert. Besonders deutlich wird dieser Effekt, wenn man mehrere Stufen von Unterabfragen benutzt, durch die Klammerung wird die Struktur immer deutlicher, als die entsprechende Möglichkeit mit Join. In der MySQL-Dokumentation werden auch Beispiele angegeben, die sich nicht ersetzen lassen, hier geht es meist um berechnete Felder.

3.Union

Seit Version 4.0 kann man die Ergebnisse mehrerer Select-Statements mit UNION zusammenfassen.

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...

Wichtig dabei ist, dass die Ergebnisse von den Datentypen her zusammen passen müssen, die Frage ob sie inhaltlich zusammenpassen spielt keine Rolle. Denkbar wäre z.B. die folgende Abfrage:

SELECT Name FROM Stadt
UNION ALL
SELECT NAME FROM Land 

Hier würden einfach die Namen aller Städte und Länder aufgeführt. In der Ergebnistabelle würden Berlin, Bremen und Hamburg jeweils doppelt auftauchen, da Land und Stadt den gleichen Namen tra­gen. Will man das nicht haben, so muss hinter UNION statt ALL nur DISTINCT stehen, dann wer­den doppelte Ergebniszeilen ausgeblendet.

4.Foreign Key

Bei Datenbanken hat man oft die Situation, dass Tabellen über Datenfelder logisch verknüpft sind. In der ersten Tabelle ist das zugehörige Datenfeld dann meist Primärschlüssel, der in der zweiten dann als Fremdschlüssel (foreign key) auftaucht. Für das Datenbankmanagement-System ist diese Situation immer dann interessant, wenn ein Daten­satz aus der ersten Tabelle gelöscht wird, der in der zweiten noch auftaucht. Ein Beispiel wieder aus der DeutschlandDB:

 Land(ID, Name, ...)
 Stadt(ID, Name, Bundesland, ...)

In der Tabelle Stadt taucht das numerische Feld Bundesland auf, dessen Wert gleich der ID des Bun­deslandes ist in dem die Stadt liegt.


Seit Version 3.23 kann MySQL mit Foreign Keys umgehen, aber nicht bei jeder Tabellenart. MySQL kennt sehr verschiedene Arten von Tabellen:

MyISAM			Der Standard-Typ von MySQL, sehr resourcenschonend.
BDB bzw. BerkeleyDB 	Berkley-Datenbanktabellen, bietet Transaktionen.
InnoDB			Dieser Typ unterstützt foreign keys und Transaktionen.

Normalerweise ist der Typ MyISAM bei der Tabellenerstellung vorgegeben. Will man eine Tabelle z.B. mit dem Typ InnoDB anlegen, so muss man den Typ zusätzlich angeben:

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;

Dieser Typ unterstützt also Fremdschlüssel. Die Definition könnte folgendermaßen aussehen:

CREATE TABLE Land (
   ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name CHAR(35) NOT NULL
) TYPE=INNODB;
CREATE TABLE Stadt (
   ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   Name CHAR(35) NOT NULL,
   Bundesland INT,
   INDEX Bundesland_index (Bundesland),
   FOREIGN KEY (Bundesland) REFERENCES Land2(ID) ON DELETE CASCADE
) TYPE=INNODB;

Versucht man jetzt eine Eingabe in die Tabelle Stadt, ohne dass es einen passenden Eintrag in der Ta­belle Land gibt, so erscheint eine Fehlermeldung.

mysql> INSERT INTO Stadt VALUES(null, "Reinbek", 1);
ERROR 1216: Cannot add or update a child row: a foreign key constraint fails

Wenn man aber ein passendes Land vorhanden ist, dann klappt auch diese Eingabe:

mysql> INSERT INTO Land VALUES (null,'Schleswig-Holstein');
mysql> INSERT INTO Stadt VALUES(null, "Reinbek", 1);

Beim Erzeugen der Tabelle Stadt wurde mit angegeben, was passieren soll, wenn in der ersten Tabell­e ein Land gelöscht wird (ON DELETE CASCADE). Durch die Angabe CASCADE wird auch dann jede Stadt gelöscht, bei der das Land eingetragen war. Wer etwas vorsichtiger ist gibt statt CASCA­DE besser SET NULL an, dann wird nur der Verweis entfernt oder gar RESTRICT, dann lässt sich das Land nicht löschen, solange eine Stadt dazu eingetragen ist.

Man kann nicht nur angeben, was beim Löschen eines Eintrages passieren soll, sondern auch was beim Ändern (UPDATE) geschehen soll. Insgesamt gibt es folgende Möglichkeiten

ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}
ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

MySQL ist sehr pingelig, was die Definition von Tabellen mit Fremdschlüsseln betrifft. Zuerst muss der Fremdschlüssel als Feld definiert werden, dann muss ein Index auf dieses Feld gesetzt werden, erst dann kann die Fremdschlüssel-Definition erfolgen.

5.Transaktionen

In vielen Büchern ist noch als Hauptmangel von MySQL erwähnt, dass es weder Transaktionen noch Fremdschlüssel kennt. Beide Einschränkungen sind aber schon lange überwunden.

Eine Transaktion ist eine logische Einheit von mehreren SQL-Anweisungen. Bei diesen Anweisun­gen soll gewährleistet sein, dass entweder alle ausgeführt werden oder keine. Eine Beispielanwen­dung wäre ein Bestellsystem mit Waren, Kunden und Konten. Bestellt der Kunde Waren, so muss die Wa­renbestände verringert werden, stellt sich abschließend heraus, dass das Konto keine genü­gende De­ckung aufweist, die Bestellung also nicht ausgeführt werden kann, so müssen alle Ände­rungen der Warenbestände rückgängig gemacht werden.

Die MySQL-Leute haben sich lange gegen Transaktionen gewehrt, da man in fast allen Fällen auch mit anderen Lösungen arbeiten kann und ein System mit Transaktionen sehr viel Ressourcen ver­braucht.

Inzwischen gibt es diese Funktion aber. Das folgende einfache Beispiel stammt aus der MySQL-Do­kumentation.

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

In diesem Beispiel geht es primär darum, dass zwischen dem Start der Transaktion und dem Ende (COMMIT) keine Veränderung an den Tabellen erfolgt. Will man auf Fehler reagieren, so muss man in den Zeilen dazwischen mögliche Fehler abfragen und kann dann mittels ROLLBACK den ur­sprünglichen Zustand wieder herstellen.

Schematisch hat man also folgenden Ablauf:

START TRANSACTION;
  MySQL-Befehle
Wenn Fehler dann
  ROLLBACK;
sonst
  COMMIT;

In den neuen MySQL-Versionen kann man sich mit

SAVEPOINT <bezeichner>

Zustände der Datenbank speichern und dann mit

ROLLBACK TO SAVEPOINT <bezeichner>

diesen Zustand wiederherstellen


VIII. Beispielaufgaben

Die folgenden Beschreibungen sollen als Grundlage für eigene Projekte stehen. Bei diesen Projekten steht die Modellierung im Vordergrund, die Realisierung dient mehr dazu das Modell zu überprü­fen.

Im ersten Schritt soll eine Beschreibung im Sinne eines Pflichtenheftes erstellt werden.


1. Kontakte

Erstellt werden soll eine Datenbank-Anwendung für das Grosshandels-Unternehmen VerkaufAlles. Für dieses Unternehmen sind die Kontakte zu anderen Firmen und den dortigen Ansprechpartnern ein wichtiges Kapital.

Die Anwendung soll Name und Anschrift, sowie sämtliche Kontaktmöglichkeiten (Festnetztelefon, Mobiltelefon, E-Mail, ...) sowohl zu den Firmen selbst, als auch zu den einzelnen Ansprech­partnern innerhalb der Firma abbilden.

Dazu sind folgende Leistungsmerkmale erforderlich: Jede Firma kann beliebig viele direkte Kontaktmöglichkeiten (z.B. Telefonnummern) besitzen. Jede Firma kann beliebig viele Ansprechpartner haben. Jeder der Ansprechpartner arbeitet jeweils nur für eine Firma. Jeder Ansprechpartner kann beliebig viele Kontaktmöglichkeiten, auch private besitzen. Zu jedem Kontakt soll auch ein Kommentar, z.B. „nicht nach 18.00h anrufen“ abgelegt werden können. Die Liste der Kontaktarten (Festnetztelefon, Mobiltelefon, E-Mail, ...) soll flexibel um weitere Arten, wie z.B. ICQ erweiterbar sein.

2. Gästebuch

Realisiert werden soll ein webbasiertes Gästebuch. Ein Besucher kann frei Namen, seine E-Mail­adresse und einen Kommentar eingeben. Automatisch festgehalten wird das Eingabe-Datum. Für dieses Gästebuch gibt es Moderatoren, die Einträge aktivieren und deaktivieren können.

3. Sportverein RSV

Der Sportverein RSV will auf elektronische Datenverarbeitung umstellen. Folgende Fälle sollen da­bei berücksichtigt werden: - Der Verein hat nur einzelne zahlende Mitglieder (keine Familienmitgliedschaft etc.). - Es gibt viele Sportkurse (z.B. Fussball-Knaben, Fussball-Senioren, Bauchtanz, ...). - Ein Mitglied kann an mehreren Kursen teilnehmen. - Die Kurse werden von jeweils einem Trainer geleitet. - Ein Trainer kann mehrere Kurse leiten.

4. Vertretungsplan

In fast allen Schulen wird ein Vertretungsplan gepflegt, wobei es eher zwei sind. Ein Plan, sortiert nach Klassen hängt öffentlich aus. Ein Plan, sortiert nach Lehrern, liegt im Lehrerzimmer aus.

Der Vertretungsplan soll nun im Intranet, ggf. auch im Internet zur Verfügung stehen. Zugänglich sein sollen die Pläne jeweils nur für berechtigte Interessenten.

5. Webforum

Ein Webforum unterscheidet sich vom beschriebenen Gästebuch dadurch, dass die Beiträge nach Dis­kussionssträngen gruppiert sind. Weiter sollen nur angemeldete Benutzer eine Schreibberechtigung haben. Zusätzlich muss es Mode­ratoren geben, die auch Beiträge ändern bzw. löschen dürfen.

6. Deutschland-Datenbank

Für Datenbank-Kurse wird immer wieder eine hinreichend komplexe Datenbank benötigt, die zusätz­lich über eine größere Anzahl an Datensätzen verfügen sollte. Ziel wäre der Aufbau einer Deutschland-Datenbank, nach dem Muster der Terra-Datenbank. Hier sol­len die Bundesländer, die Städte, die Flüsse/Seen, Autobahnen, Eisenbahn-Strecken, etc. aufgenom­men werden. Jeweils mit weiteren Informationen wie Einwohnerzahl bzw. Entfernungen. Gerade die Verbindungen (Flüsse, Strasse, Bahn) sollten sorgfältig modelliert sein, damit man die DB auch als Grundlage für Optimierungsprobleme benutzen kann.

7. Bestell- und Reservierungssystem

Das System soll es ermöglichen Waren oder Veranstaltungen anzubieten. Dazu ist es notwendig, dass mehrere gleichartige Produkte (z.B. Eintrittskarten) eingestellt werden können und die Zahl der noch verfügbaren Produkte ermittelbar ist. Die Kunden können ihre Waren online bestellen und bekommen eine Bestätigung über ihre Bestel­lung. Sowohl Anbieter, als auch Kunden benötigen einen Account bei dem Bestellsystem.