phpBuddy

Mehrfach-Statements - Multi Query

Jetzt kommen wir zu meinem neuen Lieblingsspielzeug bei MySQLi. :) Jeder PHP Programmierer, vom Anfänger bis zum Vollprofi, stolpert früher oder später einmal über die Frage "Wie zum Geier kann ich in mehrere Tabellen gleichzeitig etwas eintragen oder updaten?". Dieser harmlosen Frage folgen dann in der Regel mehrere Stunden grübeln, Haare raufen und Verzweiflung. Bei MySQLi gibt es darauf eine ganz einfache Antwort: Multi Query!
Es ist nun endlich möglich mehrere Anweisungen mit nur einer Query an die Datenbank zu senden. Dabei können das mehrere lesende Zugriffe, mehrere schreibende Zugriffe oder auch gemischte Zugriffe (lesen und schreiben) sein. Man kann sogar z.B. schreibende Aktionen durchführen und beruhend auf dem Ergebnis eine lesende Aktion ausführen.

Als erstes möchte ich zeigen wie man eine Mehrfach-Abfrage durchführt und anschließend schreiben wir etwas in die Datenbank um gleichzeitig, quasi auf dem Rückweg, den neu hinzugefügten Datensatz wieder auszulesen.

<?php
 
// Neues Datenbank-Objekt erzeugen
$db = @new mysqli( 'localhost', 'root', '', 'tutorials' );
// Pruefen ob die Datenbankverbindung hergestellt werden konnte
if (mysqli_connect_errno() == 0)
{
    $sql  = 'SELECT `name`, `bereich` FROM `moderatoren` WHERE `id` IN (1,2);';
    $sql .= 'SELECT `name`, `bereich` FROM `moderatoren` WHERE `id` > 2 ORDER BY `name`;';
    if ($db->multi_query( $sql ))
    {
        do
        {
            // Erstes Abfrageergebnis ausgeben
            if ($ergebnis = $db->store_result())
            {
                echo $ergebnis->num_rows. " Ergebnisse gefunden<br />";
                // Abfrageergebnis ausgeben
                while ($zeile = $ergebnis->fetch_object())
                {
                    echo $zeile->name. " ist zust&auml;ndig f&uuml;r " .$zeile->bereich. "<br />";
                }
                $ergebnis->close();
            }
            // Trenner fuer Zweites Abfrageergebnis
            if ($db->more_results())
            {
                echo "<hr />";
            }
        } while ($db->next_result());
    }
}
else
{
    // Es konnte keine Datenbankverbindung aufgebaut werden
    echo 'Die Datenbank konnte nicht erreicht werden. Folgender Fehler trat auf: <span class="hinweis">' .mysqli_connect_errno(). ' : ' .mysqli_connect_error(). '</span>';
}
// Datenbankverbindung schliessen
$db->close();
 
?>

Beim arbeiten mit Multi Query sind einige Dinge zu beachten. Das wichtigste ist, dass einzelne Anweisungen mit einem Semikolon voneinander getrennt werden müssen! Bei Multi Query kann man keine Prepared Statements, wie wir sie oben kennen gelernt haben, verwenden. Man muss sich also selbst um das escapen kümmern. (Das erkläre ich im nächsten Beispiel) Kommen wir zur Erklärung des Beispiels.

Zuerst stellen wir die Anweisungen zusammen. Wie wir sehen wollen wir 2 Abfragen an die Datenbank schicken. In der ersten Abfrage möchten wir die Datensätzen mit der ID 1 und 2 auslesen, in der zweiten Abfrage alle Datensätze deren ID höher als 2 ist.
Mit ...

if ($db->multi_query( $sql ))

... prüfen wir ob wir eine gültige Multi Query Resource haben. Ist das der Fall, kommt eine seltener benutzte do...while-Schleife zum Einsatz um die einzelnen Abfrageergebnisse abzuarbeiten. An das erste Abfrageergebnis kommen wir mit ...

if ($ergebnis = $db->store_result())

Hier wird also die Rückgabe von der DB in $ergebnis abgelegt, ähnlich wie das bei query() der Fall ist. Dieses Ergebnis können wir mit den ganz normalen, uns bestens bekannten Funktionen verarbeiten.
Ganz wichtig:
Auch wenn es bei einfachen Abfragen nicht zwingend erforderlich ist die Resourcen/Speicher unmittelbar nach der Verarbeitung wieder frei zu geben, so ist das bei Multi Query Pflicht! Versäumt man das Freimachen der Resourcen, kann es zu unerwartete Probleme kommen. In unserem Beispiel übernimmt das die Methode $ergebnis->close();

Hat man das erste Abfrageergebnis verarbeitet, wird mit ...

if ($db->more_results())

... geprüft ob weitere Resultate vorhanden sind. In diesem IF-Block kann man dann Anpassungen vornehmen um auf die neue Abfrage zu reagieren. So könnte es z.B. sein das die Erste und Zweite Abfrage eine unterschiedliche Anzahl Felder zurück liefert und die Ausgabeoptionen nicht mehr passen. In unserem Fall lassen wir nur eine Trennlinie ausgeben, damit wir sehen ob unser Script auch korrekt arbeitet. In der letzten Zeile der Schleife steht die while()...

while ($db->next_result());

... die das nächste Resultat an die innere Schleife übergibt.
Wenn man sich das ganze Konstrukt und die Methodennamen etwas näher anschaut wird man feststellen, dass sie große Ähnlichkeit mit denen der Iteratoren Klassen der SPL haben. Ein weiteres Indiz dafür, dass MySQLi eigentlich ausschließlich OOP und nicht prozedural angewendet werden sollte.

Kommen wir nun zum letzten Beispiel in diesem Tutorial. Ich hoffe das die Äuglein noch offen sind und noch niemand ins Koma gelangweilt wurde. ;)

Richtig interessant wird es, wenn man verschiedene Aufgaben mit nur einer Query erledigen kann. Wir werden nun einen Datensatz in die Datenbank schreiben und direkt im Anschluss den neuen Datensatz wieder auslesen. Damit wir auch den richtigen Datensatz auslesen, bedienen wir uns dabei einer MySQL-internen Methode. Zunächst der Quelltext des Scripts:

<?php
 
// Neues Datenbank-Objekt erzeugen
$db = @new mysqli( 'localhost', 'root', '', 'tutorials' );
// Pruefen ob die Datenbankverbindung hergestellt werden konnte
if (mysqli_connect_errno() == 0)
{
    $modname    = $db->real_escape_string( 'Thomas' );
    $modbereich = $db->real_escape_string( 'Webdesign & Co.' );
    // Multi-Query zusammenstellen
    $sql  = sprintf( "INSERT INTO `moderatoren` (`name`, `bereich`) VALUES ('%s', '%s');", $modname, $modbereich );
    $sql .= 'SELECT `name`, `bereich` FROM `moderatoren` WHERE `id` = LAST_INSERT_ID();';
    if ($db->multi_query( $sql ))
    {
        do
        {
            // Erstes Abfrageergebnis ausgeben
            if ($ergebnis = $db->store_result())
            {
                echo $ergebnis->num_rows. " Ergebnisse gefunden<br />";
                // Abfrageergebnis ausgeben
                while ($zeile = $ergebnis->fetch_object())
                {
                    echo $zeile->name. " ist zust&auml;ndig f&uuml;r " .$zeile->bereich. "<br />";
                }
                $ergebnis->close();
            }
        } while ($db->next_result());
    }
}
else
{
    // Es konnte keine Datenbankverbindung aufgebaut werden
    echo 'Die Datenbank konnte nicht erreicht werden. Folgender Fehler trat auf: <span class="hinweis">' .mysqli_connect_errno(). ' : ' .mysqli_connect_error(). '</span>';
}
// Datenbankverbindung schliessen
$db->close();
 
?>

In diesem Beispiel wurde Thomas dazu auserkoren als Versuchskaninchen, bzw. Anschauungsobjekt zu fungieren.

Da bei Multi Query keine Prepared Statements benutzt werden können, müssen wir uns selbst um das escapen kümmern. Dazu kennt MySQLi die Methode real_escape_string(). Jeder der vorher schon sauber programmiert hat kennt das bereits, da der name von MySQL übernommen wurde. Nachdem wir Thomas unschädlich gemacht haben (:D) stellen wir wieder unsere Anweisungen zusammen. Dazu benutze ich hier sprintf(), dass, genau wie bind_param(), mit Platzhalter arbeitet.
Die erste Anweisung schreibt also einen neuen Datensatz in unsere Tabelle, der eine neue auto_increment ID erhält. Diese ID wird von MySQL standardmässig in LAST_INSERT_ID() abgelegt und ist für die aktive Verbindung verfügbar. Die zweite Anweisung ...

$sql .= 'SELECT `name`, `bereich` FROM `moderatoren` WHERE `id` = LAST_INSERT_ID();';

... liest also einen Datensatz aus, dessen `id` den Wert der zuletzt eingefügten ID hat. Der Rest des Script wurde bereits weiter oben erklärt. Der einzige Unterschied hier ist, dass eine schreibende Aktion keinen Rückgabewert hat (abgesehen von affected_rows) und multi_query() deshalb direkt zur zweiten, lesenden Abfrage springt und diese ausgibt.

Auf diese Art kann man auch in mehrere Tabellen schreiben um Datensätze anzulegen oder zu aktualisieren. Man muß sich also keinen Knoten mehr in's Gehirn überlegen, wie denn der JOIN jetzt aussehen muß damit man über mehrere Tabellen hinweg einen Eintrag aktualisieren kann.

Dem Multi Query kann man quasi unbegrenzt Anweisungen mitgeben, die mit einer einzigen Anfrage abgearbeitet werden. Das ist wesentlich performanter als mehrere kleine Anweisungen an die DB zu schicken.