mySQL

Einleitung

siehe PHP Manual
Um mittels PHP auf eine Datenbank zuzugreifen gibt es verschiedene Möglichkeiten. PDO (PHP Data Objects) oder Mysqli.

PDO ist unabhängig von der Art des Datenbankservers, es kann somit auch für MSQL genutzt werden.

In diesem Tutorial beschäftige ich mich mit Mysqli. Mysqli nutzt die Mysqli Schnittstelle und man kann damit sowohl procedural oder objektorientiert programmieren.

prozedural

Prozedural bedeutet man nutzt vordefinierte Funktionen.

$mysqli = mysqli_connect("example.com", "user", "password", "database");

objektorientiert

Objektorientiert bedeutet, man erstellt Objekte aus vordefinierten Klassen und nutzt dann die Methoden und Eigenschaften dieser Klassen.

$mysqli = new mysqli("example.com", "user", "password", "database");

Die alte Mysql Schnittstelle funktioiert seit PHP 7 nicht mehr. Achtung Man erkennt die alten Funktionen daran, dass sie nicht mit mysqli beginnen sondern mit mysql.
Das i steht für improved.

Datenbank anlegen / phpMyAdmin

siehe phpMyAdmin Legen Sie mit phpMyAdmin eine Datenbank an.

In einem standartmäßigen Paket bei einem Provider wie Strato sind eine oder mehrere Datenbanken eingerichtet. Es ist nicht möglich eigene Datenbanken anzulegen. Man kann jedoch innerhalb einer Datenbank mehrere Tabellen anlegen.

mysqli Klasse

In der objetkorientierten Programmierung erstellt man ein Objekt der Klasse MySqli. Alle Methoden und Eigenschaften werden im PHP Manual Klasse MySqli angezeigt. Die wichtigsten Methoden und Eigenschaften sieht man hier.

1mysqli Klasse
Methode Beschreibung
set_charset() Zeichensatz für Verbindung
query() SQL Befehl ausführen
close() Verbindung schließen
Eigenschaft Beschreibung
connect_error Fehler bei der Verbindung
affected_rows Anzahl betroffener Datensätze
insert_id zuletzt vergebene Id

Verbindung zur mySQL Datenbank

siehe PHP Manual mysqli()
Eine Verbindung zu einer mySQL Datenbank kann man erzeugen indem man ein neues Objekt der Klasse mysqli() erzeugt. In der Konstruktorfunktion werden folgende Argumente erwartet:

mysqli(Adresse, Anmeldung, Passwort, Datenbankname)

Es folgt ein Beispiel für eine lokale Datenbank mit dem Namen beispiel, die man in PHP-MyAdmin zuvor erzeugt hat.

    $mysqli = new mysqli('localhost', 'root', '', 'beispiel');
    /* mysqli->connect_error liefert einen Fehler, wenn es nicht geklappt hat */
    if ($mysqli->connect_error) {
        die('Fehler bei der Verbindung: ' . mysqli_connect_error());
    }
    echo 'Verbindung hat geklappt';

Einbinden der Datenbanken Server und lokal.

In einer PHP Seite programmiert man die Verbinung zur Datenbank. Nun muss man aber lokal auf eine andere Datenbank zugreifen als im Web. Die übliche und praktische Lösung ist, man schreibt eine Programmierung für die Verbindung zur Datenbank in einer Extra Datei und bindet diese per include in den PHP Seiten ein. Diese Datei ist auf der Webseite etwas anders als auf der lokalen Seite, weil auf Providerseite die Verbinung zu einer Datenbank beim Provider erzeugt wird, während auf der Rechnerseite eine Verbinung zur lokalen Datenbank erstellt wird. Wenn die Tabellen auf beiden Seiten gleich sind, sollte es keine Probleme geben. Es folgen 2 Beispiele für die Verbinungsdateien, welche per include eingebunden werden.

Geben wir beiden Dateien den gleichen Namen beispielsweise dbConnect.inc.php. Dann kann man sie folgendermaßen einbinden. 

include "dbConnect.inc.php"

Connect Server

Beim Provider kann man in der Regel keine eigenen Datenbanken erstellen, sondern es werden bestimmte Datenbanken bereit gestellt, die mit Anmeldenamen und Passwort und Datenbanknamen angesprochen werden. Anmeldung und Passwort kann man meist selbst bestimmen. Adresse und Datenbankname bekommt man vom Provider.

mysqli(Adresse, Anmeldung, Passwort, Datenbankname)

<?php
   $con = new mysqli("dbxyz.meinprovider.de",
      "meineAnmeldung", "meinPasswort", "db2345890");
?>

Connect Lokal

<?php
   $con = new mysqli("localhost", "root", "", "firma");
?>

SQL

Um mit MySQL Datenbanken zu arbeiten wird die Programmiersprache SQL eingesetzt. Es ist die meist verwendetet Datenbanksprache der Welt.

siehe auch PHP-Einfach die wichtigsten SQL-Befehle

Die wichtigsten SQL-Anweisungen
Funktion Erklärung
create table Erzeugt eine Tabelle
insert Datensatz anlegen
select Datensatz auswählen
update Datensatz ändern
delete Datensatz löschen
drop database Datenbank löschen
drop table Tabelle löschen
optimize table Tabelle optmieren (Freigabe von nicht mehr genutztem Speicherplatz)
alter table Ändert die Struktur einer Tabelle

 

Bei SQL Anweisungen ist folgendes zu beachten

Datenbank Tabelle anlegen

Hier ein Beispiel für das Anlegen einer Tabelle in der Datenbank beispiel.
Die Verbindung zur Datenbank wird erstellt

Der Zeichensatz wird vorgegeben.

Dann folgt die sql Anweisung welche eine Tabelle namens gerichte erzeugt.
Darin die Felder id, name, beschreibung kategorie_id

Mittels query wird die Anweisung ausgeführt. Wird die SQL-Anweisung erfolgreich ausgeführt, übergibt query() den wird der boolsche Wert true, andernfalls false.

 

    $mysqli = new mysqli('localhost', 'root', '', 'beispiel');
    /*Überprüfung der Verbindung*/
    if ($mysqli->connect_error) {
        die('Fehler bei der Verbindung: ' . mysqli_connect_error());
    }
    echo 'Verbindung hat geklappt';


    if (!$mysqli->set_charset('utf8mb4')) {
        echo 'Fehler beim Zeichensatz: ' . $mysqli->error;
    }
    
    $sql = 'CREATE TABLE IF NOT EXISTS gerichte(
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    beschreibung TEXT,
    PRIMARY KEY (id)
    )';

    if ($mysqli->query($sql)) {
        echo 'SQL-Befehl ausgeführt, Tabelle erstellt: ' . $sql;
    } else {
        echo 'Fehler Tabelle nicht erstellt';
    }
    $mysqli->close();

Datensatz einfügen INSERT

Es folgt ein Beispiel, bei dem ein Datensatz in die Tabelle namens gerichte eingefügt wird.

Nach Verbindung und Zeichensatz folgt die SQL Anweisung INSERT INTO. In die Felder name und beschreibung werden die unter VALUES eingetragenen Werte der Reihenfolge nach eingefügt.

query() führt die Anweisung aus. affected_rows liefert die Anzahl der betroffenen Datensätze und insert_id liefert die betroffene id.

    $sql = "INSERT INTO gerichte
            (name, beschreibung)
            VALUES ('Apfelauflauf','4 Äpfel, 4 Zwieblen, 1 Dose Pilze im Ofen')";
    $mysqli->query($sql);
    echo 'Zeilen betroffen:' . $mysqli->affected_rows .'<br>';
    echo 'Vergebene ID: ' . $mysqli->insert_id;

Man kann die Werte zuvor in Variablen hinterlegen und diese dann unter VALUES () in den Klammern einfügen.

Hierbei müssen die Variablen in einfache Hochkommata eingefügt werden, während der gesamte SQL-String in doppelten Anführungszeichen stehen muss.

Desweiteren kann man unerlaubte Sonderzeichen mit real_escpae_string() vermeiden.


$gericht = $mysqli->real_escape_string("Apple'0 Macintosh");
$sql = "INSERT INTO gerichte (gericht, id)
         VALUES ('$gericht',4)";

Regeln der SQL Anweisung

In der SQL Anweisung ist generell folgendes zu beachten

Update einen Datensatzes

Vorhandene Felder kann man updaten mit der SQL-Anweisung UPDATE. Nach der Verbindung und Einstellung des Zeichensatzes, welches in diesem Beispiel fehlt, wird mit UPDATE in die Tabelle gerichte im Feld beschreibung ein String gesetzt. Wichtig ist die WHERE Klausel.

    $sql = 'UPDATE gerichte
            SET beschreibung = "Im Ofen gebackene Kartoffeln am besten mit frischem Rosmarin"
            WHERE id=1';
    $mysqli->query($sql);
    echo 'Zeilen betroffen:' . $mysqli->affected_rows ;

Update per Formulareingabe

Im folgendem Beispiel werden die Werte zweier Formularfelder zugesandt. Die Namen der Felder sind: beschreibung, id

Die abgeholten Werte werden in den Variablen $beschreibung und $id gespeichert, um es übersichtlich zu halten. Diese Variablen werden dann mit Punktsyntax verknüpft. Beachte die doppelten Anführungszeichen, in welchen die Variablen liegen.

'UPDATE gerichte SET beschreibung = " ' . $beschreibung . ' " WHERE id=2'

Würde der gesamte SQL-String in doppelten Anführungszeichen liegen, würden sie in einfachen Anführungzeichen liegen.

 

if(!isset($_POST["beschreibung"]))
exit("Fehler bei der Datenübertragung");

$beschreibung = htmlspecialchars($_POST["beschreibung"]);
$id = intval($_POST["id"]);

    $sql = 'UPDATE gerichte
            SET beschreibung = "'.$beschreibung.'"
            WHERE id="'.$id.'"';
    $mysqli->query($sql);
    echo 'Zeilen betroffen:' . $mysqli->affected_rows ;

Datensatz löschen

Mittels DELETE kann man einen Datensatz löschen. Auch hier muss selbstverständlich angegeben werden, welcher Datensatz gelöscht werden soll. WHERE id=2

    $sql = 'DELETE FROM gerichte
            WHERE id=2';
    $mysqli->query($sql);
    echo 'Zeilen betroffen:' . $mysqli->affected_rows . '<br>';

 

mySQL Datenbank anzeigen

SQL Abfrage mit SELECT

$sql = "SELECT * FROM personen";
Eine SQL Befehl wird erstellt.
siehe unten gezielte Abfrage

Dann wird dieser SQL-Befehl der Methode query() der Klassen mysqli zugewiesen.

Im Erfolgsfall liefert die Methode query() ein mysqli_result Objekt.

Über dieses Objekt kann man die Methode fetch_assoc() aufrufen.

Diese liefert den ersten Datensatz und bei jedem weiteren Aufruf den jeweils nächsten Datensatz.


Die wichtigsten SQL Befehle siehe PHP Einfach

Vorab 2 Beispiele zum kopieren und einfügen.

Beispiel 1

    $con = @new mysqli("", "root", "", "firma");
    $con->set_charset("utf8");

    $sql = "SELECT * FROM personen";
    foreach ($con->query($sql) as $dsatz) {
        echo $dsatz["name"] . " , "
            . $dsatz["vorname"] . " , "
            . $dsatz["id"] . " , "
            .  "<br>";
    }
    $con->close();

Beispiel 2

    $con = @new mysqli("", "root", "", "firma");

    if ($con->connect_error)
        exit("Fehler bei der Verbindung");
    $con->set_charset("utf8");

    $sql = "SELECT * FROM personen";

    if (!$res = $con->query($sql))
        exit("Fehler bei der Abfrage");

    if ($res->num_rows == 0)
        exit("kein Eintrag in der Datenbank.");

    while ($dsatz = $res->fetch_assoc()) {
        echo $dsatz["name"] . " , "
            . $dsatz["vorname"] . " , "
            . $dsatz["personalnummer"] . " , "
            . $bDay . "<br>";
    }

    $res->close();
    $con->close();

In diesen Beispielen muss zuvor eine Tabelle in einer Datenbank angelegt werden. Das kann man mit PHP-MyAdmin machen. Die Datenbank heißt firma die Tabelle heißt personen und hat die
Felder: name, vorname, id, geburtstag , gehalt

Die folgenden Erklärung zur Darstellung einer Datenbanktablle beziehen sich auf das Beispiel etwas weiter unten.

mysqli 

$con = @new mysqli("", "root","","team");

Man beginnt damit, ein neues Objekt $con der Klasse mysqli zu erstellen @new mysqli(). Das @Zeichen, der Silence-Operator vor dem Namen einer Funktion, sorgt dafür, dass Fehlerausgaben unterdrückt werden. Fehlerausgaben könnten Informationen enthalten, die für böswillige Zwecke genutzt werden könnten.
Die Parameter der Konstruktorfunktion von mysqli() sind:
Adresse zur Datenbank, Anmeldename, Passwort, Datenbankname
Damit wird eine Verbindung zur Datenbank erzeugt.
In diesem Beispiel geht es um einen lokalen Datenbankserver wie beispielsweise Wamp oder Xampp. Siehe auch Verbindung Lokal und Extern

Nun können im weiteren Verlauf Methoden und Eigenschaften der Klasse mysqli aufgerufen werden.

mysqli Methoden und Eigenschaften

mysqli->connect_error

if($con->connect_error)
exit("Fehler bei der Verbindung");
Dann wird überprüft, ob die Verbindung fehlgeschlagen ist. In dem Fall wird mit exit eine Fehlerausgabe erzeugt und alle weiteren Befehle abgebrochen.
Die Eigenschaft connect_error der Klasse mysqli beinhaltet den Wert null bei erfolgreicher Verbindung ansonsten eine Fehlermeldung, wie beispielsweise
Unknown database 'pirsonen'

$con->set_charset("utf8");
So wird der Zeichensatz utf-8 angegeben, damit auch Umlaute und korrekt angezeigt werden.
Um auch die Zeichen aus höheren Unicode-Regionen verwenden zu können, muss „utf8mb4“ statt „utf8“ verwendet werden.
set_charset("utf8mb4") mehr zu unicode BMP

query()

$res = $con->query($sql);
query() Der query Befehl der Klasse mysqli führt den mySQL Code aus. query() liefert entweder einen boolschen Wert oder es wird eine Referenz auf die Klasse mysqli_result geliefert, welche in diesem Beispiel der Variablen $res zugewiesen wird. Dieses Objekt enthält dann alle Datensätze der Abfrage.

Die Methode query() liefert false, wenn es einen Fehler in der Abfrage gab. in der if-Bedingung wird bei einem Fehler das Verbindungsobjekt $con mit close() geschlossen und das Programm mit exit() beendet.

mysqli_result()

siehe PHP Manual .

Wenn man mit query() einen SQL Befehl erfolgreich ausführt erhält man eine Referenz auf ein Objekt der Klasse mysqli_result,

Mit den Methoden dieser Klasse kann man sich das Ergebnis anzeigen lassen und Eigenschaften des Ergebnisses abfragen.

mysqli_result liefert folgende Eigenschaften und Methoden.

fetch_array(MYSQLI_ASSOC) liefert ein assoziatives Array, genau wie fetch_assoc()

num_rows

num_rows eine Eigenschaft von mysqli_result liefert die Anzahl der Datensätze.

fetch_assoc()

siehe PHP Manual
Die Methode fetch_assoc() der Klasse mysqli_result greift auf die einzelnen Datensätze zu. Wird sie das erste mal aufgerufen, wird der erste Datensatz in einem assoziativen Array übergeben. Beim zweiten mal der nächste und so weiter. Wenn kein Datensatz mehr da ist, wird null übergeben.

In einer while Schleife durchläuft man die einzelnen Datensätze bis null geliefert wird, was zur Beendigung der while Schleife führt. 

$dsatz = $res->fetch_assoc()

Nach jedem Durchlauf findet eine Zuweisung an $dSatz statt, in den Klammern der while-Schleife. So werden alle Datensätze durchlaufen. Die Feldelemente beinhalten die Datenbankfeldnamen als Schlüssel und die Datenbankfeldwerte als Wert.
In diesem Fall werden sie mit echo ausgegeben, stattdessen könnte man die Felder auch in einem Array speichern.

Mit strtotime() kann man sich eine amerikanische Datums-/Zeitangabe (time) in einen UNIX-Timestamp umwandeln lassen. Diesen kann man als zweiten Parameter in date() nutzen. Mit date() wird das Datum formatiert.

Zum Schluß wird $res und $con geschlossen.

      
    $con = @new mysqli("", "root", "", "firma");

    if ($con->connect_error)
        exit("Fehler bei der Verbindung");

    $con->set_charset("utf8");

    $sql = "SELECT * FROM personen";

     /*
	mysqli->query liefert eine Referenz auf mysqli_result 
	mysqli_result enthält die Datensätze bei Erfolg.
	mysqli->query liefert false bei einem Fehler.
     */

    $res = $con->query($sql);

    if (!$res){
    $con->close();
    exit("keine gültige Abfrage");
    }
	 

//fetch_assoc durchläuft die Datensätze, am Ende wird null gliefert

    while ($dsatz = $res->fetch_assoc()) {
        $bDay = date("d.m.Y", strtotime($dsatz["geburtstag"]));
        echo $dsatz["name"] . " , "
            . $dsatz["vorname"] . " , "
            . $dsatz["personalnummer"] . " , "
            . $dsatz["gehalt"] . " , "
            . $bDay . "<br>";
    }
    $res->close();
    $con->close();

Datensätze aus 2 Tabellen abfragen

Im folgendem Beispiel gibt es 2 Tabellen mit folgenden Feldern

Das thema_id von fragen enthält eine Ganzzahl. Diese entspricht der id von fragenkategorie. Das thema von fragenkategorie, ist ein String mit einem Thema. Es geht darum, dass zu jeder frage, antwort, das entsprechende thema angezeigt wird. Das wird mit folgendern SELECT Anweisung erstellt.

    $sql = 'SELECT frage,  antwort, thema  FROM fragen , fragenkategorie 
    WHERE fragenkategorie.id = fragen.thema_id';
    $ergebnis = $mysqli->query($sql);

Die SELECT Anweisung greift auf 2 Tabellen zu, weil nach FROM die beiden Tabellen mit Komma getrennt aufgeführt sind. Die Verknüpfung beider Tabellen wird über die WHERE KLausel erreicht. In der WHERE Klausel werden generell Bedingungen definiert, um eine Tabelle zu filtern, beispielsweise alle Felder wo Feld gehalt < 2000 oder etwas in der Art. In diesem Fall Alle Felder, wo fragenkategorie.id der thema_id in fragen entspricht werden angesprochen. Das sind zwar alle Felder, aber hier werden dadurch die richgtigen kategorie.thema Felder zugwiesen

In diesem beiden Tabellen gibt es außer id, keine Felder mit gleichen Namen. Man kann die Felder auch folgendermaßen aufrufen und man kann ihnen sogar einen Alias zuweisen, über den sie dann im Ergebnis angesprochen werden.

fragen.frage, fragen.antwort, fragenkategorie.thema as rubrik

Zeichensatz

Der Zeichensatz muss an verschiedenen Stellen auf utf-8 gesetzt sein.

HTML

Im Headbereich der HTML-Seite <meta charset="utf-8">

Server

Der Server sollte die Dokumente in utf-8 ausliefern. Siehe php-info unter default_charset

Mysqli Klasse 
if (!$mysqli->set_charset('utf8mb4')) {
echo 'Fehler beim Zeichensatz: ' . $mysqli->error;
}
phpMyAdmin / Home

Zeichensatz / Kollation Verbindung zum Server utf8mb4_unicode_ci

phpMyAdmin String Datensätze

utf8mb4_general_ci

Gezielte mySQL Abfrage

Siehe auch die wichtigsten SQL Befehle bei PHP Einfach

Siehe auch Datum und Zeitwert SQL

Über phpMyAmin kann man sich SQL Befehle anzeigen lassen, beispielsweise bei einer Suche oder auch bei anderen Befehlen gibt es unten den Button . Klicke nach jedem Befehl unten auf den Button SQL Vorschau , um die SQL Befehle kennen zu lernen.

Im folgendem ein Beispiel gibt es eine Datenbank namens team und eine Tabelle namens mitglieder. Es werden die Felder name und lohn ausgegeben, aber nur die Felder, wo lohn größer gleich 2000 und kleiner gleich 3600 ist. Die Tabelle wird absteigend nach dem Feld id geordnet.

Der Aufbau dieses und des vorigen Beispiels ist sehr ähnlich. Lediglich die Namen der Datenbank, der Tabelle und der Felder sowie die SQL Abfrage sind anders.

$con = @new mysqli("", "root", "", "team");
if ($con->connect_error)
exit("Fehler bei der Verbindung");
$con->set_charset("utf8");
$sql = "SELECT name, lohn FROM mitglieder WHERE lohn >= 2000 AND lohn <= 3600 ORDER BY id DESC";

if ($res = $con->query($sql)) {
if ($res->num_rows == 0)
echo "kein Eintrag in der Datenbank.";

while ($dsatz = $res->fetch_assoc())
echo $dsatz["name"] . " , "
. $dsatz["lohn"] . " , ";
$res->close();
} else
exit("Fehler bei der Abfrage");
$con->close();

SQL-Operatoren

WHERE

In der WHERE Klausel können verschiedene Operatoren eingesetzt werden.

SQL Operatoren
Operator Info
= gleich
<> ungleich
> größer als
>= größer gleich
< kleiner als
<= kleiner gleich
LIKE Vergleich von Zeichenketten

Vergleichsoperator like

Der LIKE Operator bietet die Möglichkeit Zeichenketten oder auch nur Teile von Zeichenketten einzugeben. Die Zeichenkette wird in Anführungsstrichen eingefügt. Groß und Kleinschreibung wird nicht unterschieden.

Hierbei können auch Platzhalter (Wildcards) eingesetzt werden. Sie werden innerhalb der Anführungstriche notiert.

"a%" Alles was mit "a" oder "A" beginnt.
"a_d%" Das erste Zeichen ist "a" oder "A" dann folgt ein beliebiges Zeichen, gefolgt von einem "d" oder "D". Es können weitere Zeichen folgen.
"%ol%" Alles in dem "ol" vorkommt. Es kann auch am Anfang oder Ende stehen.

Im folgendem Beispiel werden alle Felder mitglied die den Anfangsbuchstaben M oder m haben, angezeigt

"SELECT * FROM mitglied WHERE name LIKE 'M%' ORDER BY name";

mehrere Auswahlbedingungen

Mehrere Auswahlbedingungen lassen sich verknüpfen mit folgenden Operatoren

logische Operatoren
Operator Info
NOT Der Wahrheitswert wird umgekehrt aus true wird false
AND Alle Bedingungen müssen zutreffen
OR Mindestens eine der Bedingungen muss zutreffen

Beispiel:

  SELECT * FROM personen WHERE gehalt > 2000 AND gehalt < 3000

ORDER BY

Mit ORDER BY kann man die Reihenfolge bestimmen. Man gibt das Feld an welches die Reihenfolge bestimmen soll, oder man gibt mehrere Felder mit Komma getrennt an. Das erste bestimmt die Reihenfolge. Sollte es 2 gleiche Felder geben, bestimmt dann das zunächst angegebene die weitere Reihenfolge.

ASC ascendant bedeutet aufsteigend DESC descendant absteigend. Aufsteigend ist der Normalfall und muss nicht extra angegeben werden.

"SELECT * FROM mitglied WHERE name LIKE 'M%' ORDER BY name, vorname DESC";

MySQL prepared Statement

Datensatz einfügen

Wenn man eine SQL Abfrage aufgrund von Formulareingaben macht, kann man diese SQL Anweisungen auch als prepared Statements programmieren. Dadurch hat man einen Schutz vor SQL-Injections (schädlicher Code über Eingabefelder). Ein weiterer Vorteil ist, dass man diese Statements öfter nutzen kann.

$sql = 'INSERT INTO personen (name,vorname,id) VALUES (?,?,?)';
if($stmt = $mysqli->prepare($sql)){
    $stmt->bind_param('ssi',$name,$vorname,$id);
    $stmt->execute();
    $stmt->close();
}

Kurzinfo

Das Objekt der Klasse mysqli ruft die Methode prepare mit einem SQL-Befehl auf. Dort sind Fragezeichen anstatt der Werte eingefügt. Im Erfolgsfall wird eine Referenz der Klasse mysqli_stmt zurück gegeben. Über dieses Objekt wird die Methode bind_param() aufgerufen. Dort werden im ersten Parameter die Kürzel für die Datentypen der Werte definiert. Anschließend werden die Werte in der gleichen Reihenfolge eingegeben, wie sie zuvor im SQL-String definiert wurden.

Im folgendem Beispiel sollen aus den Formularfeldern name, vorname,id die Inhalte in die Datenbankfelder name, vorname,id eingefügt werden.

prepare()

$ps = $con->prepare("INSERT INTO personen(name, vorname, id) VALUES(?,?,?)");

Die Methode prepare() der Klasse mysqli bereitet das prepared Statement vor. Es gibt soviele Fragezeichen wie Werte als Platzhalter.

mysqli_stmt

Tritt kein Fehler auf, wird eine Referenz auf die Klasse mysqli_stmt geliefert. stmt steht für Statement. In folgendem Beispiel wird sie in der Variablen $ps gespeichert.

bind_param()

$ps->bind_param("ssds", $_POST["name"], $_POST["vorname"],$_POST["id"]);

Über das mysqli_stmt-Objekt wird die Methode bind_param() aufgerufen. Dort werden die Platzhalter mit den Feldern verbunden. Der erste Parameter von bind_param ist ein String. Er beinhaltet Buchstaben, die jeweils für einen Datentyp stehen. Diese werden in gleicher Reihenfolge den nachfolgenden Variablen zugewiesen.

Die weiteren Parameter von bind_param sind die übermittelten Werte des Formulars. Die Reihenfolge und Anzahl muss übereinstimmen mit den Buchstaben des ersten Parameters und mit der Reihenfolge und Anzahl Fragezeichen in der SQL-Anfrage.
bind_param liefert im Erfolgsfall true .

In diesem Beispiel gibt es 3 Fragezeichen in prepare.
In bind_param gibt es als ersten Parameter einen Sting mit 3 Zeichen, und 3 Variablen, die in gleicher Reihenfolge den ? Fragezeichen zugewiesen werden müssen. In diesem Beispiel ist die erste und zweite Variable ein Sting ist und die dritte ein Integer, gemäß dem ersten Parameter "ssi".

execute()

Die Methode execute() führt das prepared Statement aus.

$ps = $con->prepare("INSERT INTO personen (name, vorname,id ) VALUES(?,?,?)");
$ps->bind_param("ssi", $_POST["name"][0], $_POST["vorname"][0], $_POST["id"][0]);
$ps->execute();
if ($ps->affected_rows > 0)
        echo "Datensätze hinzugekommen";
    else
        echo "Fehler kein Datensatz hinzugkommen.";
$ps->close();

affected_rows

Die Eigenschaft affected_rows gibt die Anzahl der Datensätze an.

Beispiel

HTML

<p class="center">Geben Sie bitte einen Datensatz ein.</p>
<form action="10_db_erzeugen.php" method="post">
<p><input type="text" name="name" required>Name</p>
<p><input type="text" name="vorname" required>Vorname</p>
<p><input type="text" name="gehalt" required>Gehalt</p>
<p><input type="date" name="geburtstag" required>Geburtstag</p>
<p><input type="submit" name="gesendet"> <input type="reset"></p>
</form>

PHP

if (isset($_POST["gesendet"])) {
  $con = new mysqli("", "root", "", "firma");
  $con->set_charset("utf8");

  $ps = $con->prepare("INSERT INTO personen(name, vorname,  gehalt, geburtstag) VALUES(?,?,?,?)");
  $ps->bind_param("ssds", $_POST["name"], $_POST["vorname"], $_POST["gehalt"], $_POST["geburtstag"]);
  $ps->execute();

  if ($ps->affected_rows > 0)
     echo "Datensatz hinzugekommen. <br>";
  else
     echo "Fehler kein Datensatz hinzugkommen. <br>";

  $ps->close();
  $con->close();
}

SELECT mit Prepared Statement

Im folgenden eine SELECT Anweisung mit prepared Statement. Extra ohne Überprüfung, um das Beispiel übersichtlich zu halten.

$id = 4;
$mysqli = new mysqli('localhost', 'root', '', 'beispiel');
$mysqli->set_charset('utf8mb4');
$sql = 'SELECT name, beschreibung FROM gerichte WHERE id = ?';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($name, $beschreibung);
$stmt->fetch();
$stmt->close();
$mysqli->close();

Wenn es sich um eine Anfrage handelt bei der mehrere Datensätze ausgegeben werden, kann man den fetch() Befehl in eine while Schleife einfügen.

Außerdem hat man dann auch die Möglichkeit, die Anzahl der Datensätze anzuzeigen: num_rows Diese Eigenschaft steht nur zur Verfügung, wenn man store_result() aufgerufen hat.

$stmt->store_result();
echo $stmt->num_rows . " Datensätze gefunden.<br>";
 while ($stmt->fetch()){
        echo $name . " : " . $beschreibung ;
}

Ausführliches Schritt für Schritt Beispiel

Im folgenden möchte ich eine Verbindung, ein perpared Statement und Anzeige des Ergebnisses, Schritt für Schritt aufführen. Es geht um eine Anfrage per Formular. Aus einer Datenbank mit Personen, denen ein Gehalt zugewiesen ist, sollen alle Personen angezeigt werden, deren Gehalt von einer Untergrenze bis zu einer Obergrenze reicht. Die Untergrenze wird per Eingabefeld namens ug und die Obergrenze per Eingabefeld namens og bestimmt.

Die Datenbank besteht aus

  1. name
  2. vorname
  3. gehalt
  4. geburtsdatum

Verbindung / mysqli

Es wird ein mysqli Objekt erzeugt

$con = @new mysqli("", "root","","firma");
if($con->connect_error)
exit("Fehler bei der Verbindung");
$con->set_charset("utf8");

Prepared Statement / prepare() 

   if (!@$ps = $con->prepare("SELECT name, vorname, gehalt FROM personen 
   WHERE gehalt >= ? AND gehalt <= ? ORDER BY gehalt"))
      exit("Fehler bei Vorbereitung");

Parameterbindung / bind-param

Wenn prepare() erfolgreich ist, wird dort die Parameterbindung eingefügt. 

   if (!@$ps->bind_param("dd", $_POST["ug"], $_POST["og"]))
      exit("Fehler bei Parameterbindung");

Ereignisbindung / bind_result

Bei erfolgreicher Parameterbindung wird bind_result() und store_result aufgerufen.

Für jedes Feld der Tabelle, welches abgerufen und angzeigt werden soll, muss man eine Variable vergeben. Die Reihenfolge bestimmt den Bezug
SELECT name, vorname, gehalt
bind_result($name, $vorname, $gehalt).
Ein Sternchen für die Ausgabe aller Felder ist nicht möglich. 

 if (!@$ps->bind_result("dd", $_POST["ug"], $_POST["og"]))
      exit("Fehler bei der Ereignisbindung");

execute()

Ausführen des prepared statmemts.

$ps->execute();

store_result()

Möchte man die Datensätze anzeigen lassen, kann man sie mit store_result() zwischenspeichern.

  $ps->store_result();

Anzahl der Datensätze / num_rows

Nach store_result() kann man sich mittels num_rows die Anzahl der ermittelten Datensätze ausgeben lassen.

if($ps->num_rows == 0)  
echo "keine Ergebnisse vorhanden";

Ausgabe / fetch()

Die Methode fetch() der Klasse msqli_stmt funktioniert ähnlich wie fetch_assoc()Bei jedem Aufruf liefert sie die Datensätze der gebunden Variablen, welche mit bind_result() erzeugt wurden. Das Gehalt wurde formatiert.

    if ($ps->num_rows == 0)
        echo "Keine Ergebnisse";

    while ($ps->fetch())
        echo "$name $vorname, " . number_format($gehalt, 2, ",", ".") . " € ";

Das HTML Formular 

    <form action="gehalt_prepared.php" method="post">
        <p><input name="ug"> Untergrenze</p>
        <p>und</p>
        <p><input name="og"> Obergrenze</p>
        <p><input type="submit"> <input type="reset"></p>
    </form>

Der komplette PHP Code 

  $con = @new mysqli("", "root", "", "firma");
   $con->set_charset("utf8");
   
   if ($con->connect_error)
      exit("Fehler bei Verbindung");

   if (!@$ps = $con->prepare("SELECT name, vorname, gehalt FROM personen 
 WHERE gehalt >= ? AND gehalt <= ? ORDER BY gehalt"))
      exit("Fehler bei Vorbereitung");

   if (!@$ps->bind_param("dd", $_POST["ug"], $_POST["og"]))
      exit("Fehler bei Parameterbindung");

   $ps->execute();

   if (!@$ps->bind_result($name, $vorname, $gehalt))
      exit("Fehler bei Ergebnisbindung");

   $ps->store_result();
   if ($ps->num_rows == 0)
      echo "Keine Ergebnisse";

   while ($ps->fetch())
      echo "$name, $vorname, $gehalt<br>";


   $ps->close();
   $con->close();

Select mit LIKE pepared Statement

Im folgenden ein einfaches Beispiel ohne Sicherheitsabfragen mit LIKE Operator. Bei Abfragen mit LIKE können Platzhalter eingegeben werden, wie in diesem Beispiel die % Zeichen. Diese stehen für ein beliebiges Zeichen oder gar kein Zeichen. Man beachte wie die beiden Prozentzeichen am Anfang und Ende der Suche plaziert sind, so dass der gesuchte Begriff irgendwo vorkommen kann.

    if (isset($_POST["suche"])) {
        $suchbegriff = "%" . htmlentities($_POST["suche"]) . "%";
        $mysqli = new mysqli('localhost', 'root', '', 'beispiel');
        $mysqli->set_charset('utf8mb4');
        $sql = 'SELECT name, beschreibung FROM gerichte WHERE beschreibung LIKE ?';
        $ps = $mysqli->prepare($sql);
        $ps->bind_param("s", $suchbegriff);
        $ps->execute();
        $ps->bind_result($name, $beschreibung);
        $ps->store_result();
        if ($ps->num_rows == 0)
            echo "keine Ergebnisse vorhanden";

        while ($ps->fetch())
            echo $name . " / " . $beschreibung . "≶br>";
    }

Datensatz anzeigen, auswählen, ändern.

Beispiel Auswahl

In diesem Beispiel wird ein Datensatz geändert. Dazu gibt es 3 PHP Seiten. highscoreAuswahl.php, highscoreAendern.php, highscoreUpdate.php.

highscoreAuswahl.php

Es wird ein Datensatz in einem Formular in einer Tabelle dargestellt. Der Index des jeweiligen Tabellenfeldes wird den Werten von Radiobuttons zugwiesen. Die Radibuttons haben in diesem Beispiel das Attribut name="auswahl" Der ausgwählte Radiobutton wird an eine neue PHP Seite übertragen. Anfang und Ende des Formulars inklusive Tabelle werden in HTML erstellt.

HTML

<form action="highscoreAendern.php" method="post">
<table>
<tr>
<th>Auswahl</th>
<th>Vorname</th>
<th>Nachname</th>
<th>Punkte</th>
</tr>

PHP

<?php
$con = new mysqli( "Adresse", "Anmeldung", "Passwort", "Datenbank" );
$con->set_charset( "utf8" );
$res = $con->query( "SELECT * FROM highscore" );

while ( $dsatz = $res->fetch_assoc() ) {
echo "<tr>";
echo "<td><label><input type='radio' name='auswahl' value='" . $dsatz[ "nr" ] . "'>";
echo $dsatz[ "nr" ] . "</label></td>";
echo "<td>" . $dsatz[ "vorname" ] . "</td>";
echo "<td>" . $dsatz[ "nachname" ] . "</td>";
echo "<td>" . $dsatz[ "punkte" ] . "</td></tr>";
}
$res->close();
$con->close();
?>

HTML

</table>
<p class="center">
<input type="submit" value="Datensatz anzeigen">
</p>
</form>

highscoreAendern.php

Hier wird ein Datensatz anhand des übermittelten Radiobuttons name="auswahl" angezeigt.
$sql = "SELECT * FROM highscore WHERE nr = " . intval($_POST["auswahl"]);
Die einzelnen Werte der Datenbankfelder werden den Werten von Formularfeldern zugewiesen.
<p><label><input type='text' name='vorname' value='" . $dsatz["vorname"] . "'> Vorname</label>
So kann man diese Formularfelder ändern und an die Seite highscoreUpdate.php schicken. Der Index, welcher in der Datentabelle und im Formularfeld nr heißt, wird mittels eines input type="hidden" übermittelt.

PHP

<?php
if (isset($_POST["auswahl"])) {
$con = new mysqli( "Adresse", "Anmeldung", "Passwort", "Datenbank" );
$con->set_charset("utf8");
$sql = "SELECT * FROM highscore WHERE nr = " . intval($_POST["auswahl"]);
$res = $con->query($sql);
$dsatz = $res->fetch_assoc();
echo "<p class='center'>Bitte neue Inhalte eintragen und speichern.</p>";
echo "<form action='highscoreUpdate.php' method='post'>";
echo "<p><label><input type='text' name='vorname' value='" . $dsatz["vorname"] . "'> Vorname</label></p>";
echo "<p><label><input type='text' name='nachname' value='" . $dsatz["nachname"] . "'> Nachname</label></p>";
echo "<p><label><input type='text' name='punkte' value='" . $dsatz["punkte"] . "'> Punkte</label></p>";
echo "<input type='hidden' name='nr' value='" . $_POST["auswahl"] . "'>";
echo "<p><input type='submit' value='speichern'></p></form>";
$res->close();
$con->close();
} else {
echo "<p>Keine Auswahl getroffen.</p>";
}
?>

highscoreUpdate.php

Anhand des Index nr wird der Datensatz mit einem prepared Statement aktualisiert.

<?php
$nr = $_POST["nr"];
echo $nr. $_POST["vorname"].$_POST["punkte"];
$con = new mysqli( "Adresse", "Anmeldung", "Passwort", "Datenbank" );
$con->set_charset("utf8");
$ps = $con->prepare("UPDATE highscore SET vorname = ?, nachname = ? , punkte = ? WHERE nr = $nr");
$ps->bind_param("ssi", $_POST["vorname"], $_POST["nachname"], $_POST["punkte"]);
$ps->execute();
if ($ps->affected_rows > 0)
echo "<p class='center'>Der Datensatz wurde geändert.</p>";
else
echo "Es wurden keine Änderung vorgenommen.";
$ps->close();
$con->close();
?>

Transaktion

Eine Transaktion wird gebraucht, wenn man mehrere Befehle hat, die entweder alle ausgeführt werden oder gar nicht. Beispielsweise bei einer Kontobuchung wird von einem Konto etwas abgebucht und auf ein anderes verbucht.

Das Prozedere funktioniert folgendermaßen:

$mysqli->begin_transaction()

wird aufgerufen. Dadurch werden die folgenden Befehle nicht wirklich ausgeführt, sondern nur probeweise. Man kann Befehle eingeben und in einer if-Struktur auf Erfolg testen. Gab es einen Fehler notiert man:

$mysqli->rollback()

Das führt dazu dass der Befehl rückgängig gemacht wird.
Im Erfolgsfall des ersten Befehls, führt man den nächsten aus und überprüft auch diesen auf Erfolg. Auch hier hat man die Möglichkeit bei Mißerfolg mit rollback() zu reagieren.

Sind alle Befehle erfolgreich ruft man auf.

$mysqli->commit()

Erst dann werden alle Befehle wirklich ausgeführt.

Es bietet sich an das Ganze Prozedere in einen try-catch Block einzufügen. Dann kann man bei Mißerfolg des Ganzen eine Fehlermeldung ausgeben, die man zuvor in einer Variablen gespeichert hat.

$mysqli = new mysqli('localhost', 'root', '', 'beispiel');
if ($mysqli->connect_error) {
    echo 'Fehler bei der Verbindung: ' . mysqli_connect_error();
    exit();
}
if (!$mysqli->set_charset('utf8mb4')) {
    echo 'Fehler beim Zeichensatz: ' . $mysqli->error;
}


try {
    $betrag = 20;
    $zahler = 'Rick Sanchez';
    $empfaenger = 'Morty Smith';
    $soll =  'UPDATE konto SET kontostand = kontostand - ? WHERE name = ?';
    $haben = 'UPDATE konto SET kontostand = kontostand + ? WHERE name = ?';

    $zahlung = $mysqli->prepare($soll);
    $zahlung->bind_param('is', $betrag, $zahler);

    $gutschrift = $mysqli->prepare($haben);
    $gutschrift->bind_param('is', $betrag, $empfaenger);

    $mysqli->begin_transaction();
    $zahlung->execute();
    if (!$mysqli->affected_rows) {
        $mysqli->rollback();
        $fehler = 'Abbuchung nicht erfolgreich.';
    } else {
        $gutschrift->execute();
        if (!$mysqli->affected_rows) {
            $mysqli->rollback();
            $fehler = 'Verbuchung nicht erfolgreich.';
        } else {
            $mysqli->commit();
        }
    }


    $kontostaende = $mysqli->query('SELECT name, kontostand FROM konto');
} catch (Exception $e) {
    $fehler = $e->getMessage();
}
if (isset($fehler)) {
    echo "$fehler";
}
?>

 

 

Webdesign / PHP / mySQL / Dreamweaver MX Tipps
Copyright © Michael Albers
www.pastorpixel.de