Informationssysteme-MySQL
Der vorliegende Text gliedert sich in folgende Teile:
VI. Einbindung von MySQL in PHP
VII. Erweiterte Möglichkeiten in MySQL
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 Zusammenstellung 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-Sitzung folgendes Programm aufrufen
/usr/bin/mysql datenbankname
In unserem Fall heißt die Datenbank kurs. Datenbanken dürfen nur von einem Benutzer mit entsprechenden Rechten eingerichtet werden (/usr/bin/mysqladmin create kurs).
1.Create
Hiermit wird eine neue Datentabelle in einer vorhandenen Datenbank eingerichtet. Der Name der Datenbank 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 Reihenfolge angegeben werden. Will man nur einige Werte eingeben, so müssen die zugehörigen Spaltennamen angegeben sein.
Beispiel:
INSERT INTO telefonliste VALUES( 'Meier', 'Klaus', '4711' );
Texte müssen in Hochkommata eingeschlossen werden, Zahlen nicht. Die Telefonnummer ist im vorliegenden 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. Vergleichsoperatoren 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;
Weitere Informationen zu MySQL
https://www.mysql.com/de/ Heimat des ursprünglichen MySQL https://mariadb.org/de/ Heimat von MariaDB https://www.w3schools.com/sql/ Lernplattform mit "TryIT"
VI. Einbindung von MySQL in PHP
Will man eine Datenbank ansprechen, so muss zuerst eine Verbindung zum MySQL-Server aufgebaut 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>
<?php
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 Ergebnis wird als Tabelle dargestellt, deren Reihen die Datensätze sind und deren Spalten die Attribute darstellen.
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>
<?php
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>
<?php
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>
<?php
$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>
<?php
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 Einträ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 unterschiedliche ID bekommen, da diese vom MySQL automatisch vergeben wird. In das Datumsfeld soll der Eintag automatisch vorgenommen werden und das Feld aktiv dient dazu in einem zweiten Schritt eine Moderation 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>
<?php
$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 notwendig 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 festgelegt 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 Stringverknüpfung. Es werden hier zwei Texte mit dem Ergebnis eines Funktionsaufrufes verknüpft. PHP konvertiert den Wert der Funktion dazu automatisch in einen String.
Falls ein Fehler aufgetreten ist, wird die Fehlernummer und die zugehörige Fehlerbeschreibung ausgegeben. 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>
<?php
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 einem 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>
<?php
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>   ";
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 Datensatzes 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
<?php
$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 Datenbank 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>
<?php
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', kommentar='$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>
<?php
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-Distributionen 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 Gedanken zu machen, wie man diese Datenbank einfach abfragen kann. Zur Erläuterung wird wird von einen 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 beinhaltet.
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ädte.
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 Ergebnis 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, wobei 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“ anfä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 tragen. Will man das nicht haben, so muss hinter UNION statt ALL nur DISTINCT stehen, dann werden 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 Datensatz 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 Bundeslandes 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 Tabelle 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 Tabelle 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 CASCADE 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 Anweisungen soll gewährleistet sein, dass entweder alle ausgeführt werden oder keine. Eine Beispielanwendung wäre ein Bestellsystem mit Waren, Kunden und Konten. Bestellt der Kunde Waren, so muss die Warenbestände verringert werden, stellt sich abschließend heraus, dass das Konto keine genügende Deckung aufweist, die Bestellung also nicht ausgeführt werden kann, so müssen alle Änderungen 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 verbraucht.
Inzwischen gibt es diese Funktion aber. Das folgende einfache Beispiel stammt aus der MySQL-Dokumentation.
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 ursprü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 Ansprechpartnern 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-Mailadresse 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 dabei 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 Diskussionssträngen gruppiert sind. Weiter sollen nur angemeldete Benutzer eine Schreibberechtigung haben. Zusätzlich muss es Moderatoren 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ätzlich ü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 sollen die Bundesländer, die Städte, die Flüsse/Seen, Autobahnen, Eisenbahn-Strecken, etc. aufgenommen 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 Bestellung. Sowohl Anbieter, als auch Kunden benötigen einen Account bei dem Bestellsystem.
