Menu
I am aware that there are quite a few Q&As for this topic but I am facing quite an individual problem, which is why I raised a new question.
I am using PHP7 with mariadb 10.x and the PHP templating engine Twig 2.x.
My goal is to insert data into 2 related tables (adresse, person).
For those of you who are unfamiliar with Twig, it is basically a tool which helps separating html and php code by using templates. My .html files are therefore called .twig.
If I use the following code, the INSERT INTO only fills adresse with values but person remains completely empty. There is no error message in error.log.
What am I missing? What did I do wrong?
I also added BEGIN; --preparestmt--; COMMIT; to make sure that this statement belongs together and should be treated as one transaction.
I typed SELECT last_insert_id(); and it gave me the result 0. Why?
adresse.adresse_id did increment after all? Why does last_insert_id() not take the incremented value but 0?
My table structure:
{% include "headerKundendienst.twig" %}
<body class="maincontent">
<div class="main-content">
<h2>Bauleiter hinzufügen</h2>
<form method="post" action="neuerBauleiter">
<p>
<label class="editSupplierLabel">Nachname</label>
<input class="editSupplier" type="text" name="txtBlName" required>
</p>
<p>
<label class="editSupplierLabel">Vorname</label>
<input class="editSupplier" type="text" name="txtBlVorname" required>
</p>
<p>
<label class="editSupplierLabel">Anschrift</label>
<input class="editSupplier" type="text" name="txtBlAnschrift" required>
</p>
<p>
<label class="editSupplierLabel">PLZ</label>
<input class="editSupplier" type="text" name="txtBlPlz" required>
</p>
<p>
<label class="editSupplierLabel">Ort</label>
<input class="editSupplier" type="text" name="txtBlOrt" required>
</p>
<p>
<label class="editSupplierLabel">Telefon</label>
<input class="editSupplier" type="tel" name="txtBlTel" required>
</p>
<p>
<label class="editSupplierLabel">Telefon2</label>
<input class="editSupplier" type="tel" name="txtBlTel2">
</p>
<p>
<label class="editSupplierLabel">Telefon3</label>
<input class="editSupplier" type="tel" name="txtBlTel3">
</p>
<p>
<label class="editSupplierLabel">E-Mail</label>
<input class="editSupplier" type="email" name="txtBlMail" required>
</p>
<p>
<label class="editSupplierLabel">Fax</label>
<input class="editSupplier" type="tel" name="txtBlFax">
</p>
<p>
<label class="editSupplierLabel">Sachbearbeiter</label>
<input class="editSupplier" type="text" name="txtBlSb" value="{{ loggedUser }}" readonly required>
</p>
<p>
<label class="editSupplierLabel">Adressgruppe-ID</label>
<input class="editSupplier" type="text" name="BlAdrgruppeid" value="1272" readonly required>
</p>
<p>
<label class="editSupplierLabel">Adressstatus-ID</label>
<input class="editSupplier" type="text" name="BlAdrstatusid" value="62" readonly required>
</p>
<p>
<label class="editSupplierLabel">Funktion-ID</label>
<input class="editSupplier" type="text" name="BlFunktionid" value="2" readonly required>
</p>
<p>
<label class="editSupplierLabel">Funktion</label>
<input class="editSupplier" type="text" name="BlFunktion" value="Bauleiter" readonly required>
</p>
<p>
<label class="editSupplierLabel">Abteilung</label>
<input class="editSupplier" type="text" name="BlAbteilung" value="Bauleitung" readonly required>
</p>
<p>
<input class="button_save" type="submit" name="btnSaveBl" value="Speichern">
<a href="listeBauleiter" class="button_delete" style="margin-bottom: 40px; margin-left:150px;">Zurück</a>
</p>
</form>
</div>
</div>
</body>
{% include "footer.twig" %}
<?php
require_once 'vendor/autoload.php'; // keine pfad anpassung mehr nötig, immer so lassen
require_once "utils/Database.class.php"; // include database configuration
/* Daten für neuen Bauleiter speichern */
if (isset($_POST["btnSaveBl"]))
{
DatabaseLink::getInstance()->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$stmt = DatabaseLink::getInstance()->prepare("INSERT INTO adresse(name1, strasse, plz, ort)
VALUES (:blNachname, :blAnschrift, :blPlz, :blOrt);
INSERT INTO intranet.person(adresse_id, name, vorname,
telefon, telefon2, telefon4, mailadresse, fax, adrgruppe_id, adrstatus_id, funktion_id, funktion, abteilung, sb_kuerzel)
VALUES (last_insert_id(), :blNachname, :blVorname,
:blTel, :blTel2, :blTel4, :blMail, :blFax, :bladrgruppeid, :bladrstatusid, :blfunktionid, :blfunktion,
:blabteilung, :blSachbearbeiter;)");
$result = $stmt->execute(array(
":blNachname" => $_POST["txtBlName"],
":blVorname" => $_POST["txtBlVorname"],
":blAnschrift" => $_POST["txtBlAnschrift"],
":blPlz" => $_POST["txtBlPlz"],
":blOrt" => $_POST["txtBlOrt"],
":blTel" => $_POST["txtBlTel"],
":blTel2" => $_POST["txtBlTel2"],
":blTel4" => $_POST["txtBlTel3"],
":blMail" => $_POST["txtBlMail"],
":blFax" => $_POST["txtBlFax"],
":blSachbearbeiter" => $_POST["txtBlSb"],
":bladrgruppeid" => $_POST["BlAdrgruppeid"],
":bladrstatusid" => $_POST["BlAdrstatusid"],
":blfunktionid" => $_POST["BlFunktionid"],
":blfunktion" => $_POST["BlFunktion"],
":blabteilung" => $_POST["BlAbteilung"]
));
header("Location: listeBauleiter");
}
/* Twig initialisieren */
$loader = new Twig_Loader_Filesystem('template/'); // keine pfad anpassung mehr nötig, immer so lassen
$twig = new Twig_Environment($loader, array(
"debug" => "true",
));
include "utils/injector.php";
$twig->addExtension(new Twig_Extension_Debug());
//templatewerte
$templateName = "neuerBauleiter.twig";
$data = array(
);
//display
echo $twig->render($templateName, $data);
584
You cannot put multiple statements in a prepared query. From the manual:
SQL syntax for prepared statements does not support multi-statements
So you will need to split your prepare and execute into 2 as below. You should find this then means that LAST_INSERT_ID() starts working as you expect.
$stmt = DatabaseLink::getInstance()->prepare("INSERT INTO adresse(name1, strasse, plz, ort)
VALUES (:blNachname, :blAnschrift, :blPlz, :blOrt)");
$stmt2 = DatabaseLink::getInstance()->prepare("INSERT INTO intranet.person(adresse_id, name, vorname,
telefon, telefon2, telefon4, mailadresse, fax, adrgruppe_id, adrstatus_id, funktion_id, funktion, abteilung, sb_kuerzel)
VALUES (last_insert_id(), :blNachname, :blVorname, :blTel, :blTel2, :blTel4, :blMail, :blFax, :bladrgruppeid, :bladrstatusid, :blfunktionid, :blfunktion,
:blabteilung, :blSachbearbeiter;)");
$result = $stmt->execute(array(
":blNachname" => $_POST["txtBlName"],
":blVorname" => $_POST["txtBlVorname"],
":blAnschrift" => $_POST["txtBlAnschrift"],
":blPlz" => $_POST["txtBlPlz"],
":blOrt" => $_POST["txtBlOrt"]));
$result2 = $stmt2->execute(array(
":blTel" => $_POST["txtBlTel"],
":blTel2" => $_POST["txtBlTel2"],
":blTel4" => $_POST["txtBlTel3"],
":blMail" => $_POST["txtBlMail"],
":blFax" => $_POST["txtBlFax"],
":blSachbearbeiter" => $_POST["txtBlSb"],
":bladrgruppeid" => $_POST["BlAdrgruppeid"],
":bladrstatusid" => $_POST["BlAdrstatusid"],
":blfunktionid" => $_POST["BlFunktionid"],
":blfunktion" => $_POST["BlFunktion"],
":blabteilung" => $_POST["BlAbteilung"]
));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
