Gegevens in twee tabellen tegelijk inserten en id van tabel 1 doorgeven aan tabel 2
Kan dit in één query?
Ik heb al geprobeerd met BEGIN en COMMIT twee keer een INSERT query uit te voeren met één verbinding maar loop tegen een syntax error aan.
Wat ik nu probeer:
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
$addcompany = "
BEGIN;
INSERT INTO company (name, street, zip, place, phone, contact, email, logo)
VALUES('testnaam', 'teststraat', 'testzip', 'testplaats', 'testphone', 'testcontact', 'testmail', 'testbestand');
INSERT INTO users (username, password)
VALUES('testuser', 'geheim123');
COMMIT;";
$addcompany = $conn->query($addcompany);
BEGIN;
INSERT INTO company (name, street, zip, place, phone, contact, email, logo)
VALUES('testnaam', 'teststraat', 'testzip', 'testplaats', 'testphone', 'testcontact', 'testmail', 'testbestand');
INSERT INTO users (username, password)
VALUES('testuser', 'geheim123');
COMMIT;";
$addcompany = $conn->query($addcompany);
Bovenstaand geeft nog een foutmelding: You have an error in your SQL syntax; it seems the error is around: 'INSERT INTO company ( name, street, zip, place, phone, ' at line 2
users heeft een veld id die auto_increment is, hoe kan ik dit id tegelijk inserten in de rij `userid` van company? Als dat al kan.
Password wordt geencrypt opgeslagen dit is puur even om de query te laten zien.
Gewijzigd op 09/07/2018 09:57:26 door Snelle Jaap
Werkwijze, voorbeeld:
Een gebruiker logt in. De tabel users wordt daarvoor gebruikt waarbij gecheckt wordt op de sleutel. Als akkoord, dan hou je de usergegevens vast in de sessievariabelen. Dus ook user-id, die uniek is. Op het moment dat een transactie/mutatie plaats vindt, dan schrijf je de transactie/mutatie weg met de gegevens van de ingelogde gebruiker (id). Bij de inlogtabel, users, is de id dus uniek, bij een company hoeft dit niet altijd het geval te zijn, omdat een user meerdere companies kan hebben, of dat een company meerdere users kent. Dit moet je van te voren goed in een structuur, architectuur bepalen.
Kortom, vertel wat meer. Dan kunnen wij meer vertellen.
Snelle Jaap op 09/07/2018 10:34:00:
Wat bedoel je precies? Transacties doe je toch altijd in SQL?
Klopt, maar het is altijd handig als MySQLi daar wat handige objecten voor heeft, die je kan gebruiken. Let er wel op dat je InnoDB gebruikt als DB-engine voor je tabel.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
// Dit zorgt ervoor dat de autocommit uit staat. Dus vanaf nu moet je alle wijzigingen wel committen na je query.
$mysqli->autocommit(FALSE);
// Wat queries uitvoeren!
$mysqli->query('UPDATE `table` SET `col`=2');
$mysqli->query('UPDATE `table1` SET `col1`=3;');
// Nu committen we de boel.
$mysqli->commit();
// Doe een rollback
$mysqli->rollback();
?>
// Dit zorgt ervoor dat de autocommit uit staat. Dus vanaf nu moet je alle wijzigingen wel committen na je query.
$mysqli->autocommit(FALSE);
// Wat queries uitvoeren!
$mysqli->query('UPDATE `table` SET `col`=2');
$mysqli->query('UPDATE `table1` SET `col1`=3;');
// Nu committen we de boel.
$mysqli->commit();
// Doe een rollback
$mysqli->rollback();
?>
Gewijzigd op 09/07/2018 12:56:54 door - Ariën -
De reden dat je een syntax error krijgt is omdat je meerdere queries in één SQL-statement probeert uit te voeren. Het gebruik van multiquery is overigens geen oplossing en die hele constructie zou ik ook vermijden omdat wat je daarin doet wel "onderbreekbaar" is, dat wil zeggen, als een multiquery halverwege spaak loopt, dan is de kans groot dat je troep in je database hebt zitten en dat wil je echt niet. Als een transactie halverwege vastloopt is het effect van de queries die binnen de transactie zitten dat dit hetzelfde was als helemaal geen queries uitvoeren. Ofwel, een transactie garandeert dat alle queries of wel, of in het geheel niet worden uitgevoerd.
Wat je wel van transacties moet begrijpen is dat het niet de "ondeelbaarheid" van de queries garandeert. Zo kan parallel eenzelfde transactie meerdere keren tegelijkertijd worden uitgevoerd. Dus als je bestaande data in zo'n transactie wijzigt dan zul je deze eerst moeten vergrendelen.
Tegelijkertijd werkt elke "thread" wel met zijn eigen stack van auto-increment id's, dus al wordt de bovenstaande riedel meerdere keren tegelijkertijd uitgevoerd dan zal het niet gebeuren dat een user ineens aan een verkeerde company wordt gekoppeld omdat ondertussen het auto-increment id is opgehoogd net voordat je deze opvraagt ofzo. (Misschien doe je er wel verstandig aan om dit nogmaals bevestigd te krijgen). Oftewel, in dit opzicht doet de transactie "niets". Wel doe je er dus verstandig aan om een transactie te gebruiken om meerdere partjes data in één keer in zijn geheel (of geheel niet, dus) toe te voegen zodat je de data in je database kloppend en volledig is en houdt.
Het bovenstaande fragment van @Ariën laat zien hoe je transacties gebruikt in MySQLi, met als enige kanttekening dat die laatste rollback volgens mij niets doet, omdat je al alles hebt gecommit :). Hierbij ook nog de kanttekening dat het -naarmate je meer van dit soort voorzieningen van MySQLi gaat gebruiken- steeds interessanter wordt om een wrapper om MySQLi in te zetten. Enerzijds om taken te automatiseren en te verkorten, maar ook om hard coding te vermijden.
Stel dat je straks de administratie rondom transacties wilt veranderen en dat je bij wilt houden of er reeds een transactie is gestart. Als je alles had ge-hard code dat zul je alle instanties in code hiervan aan moeten passen, terwijl als je hiervoor een methode in een wrapper class was geweest, dan hoef je enkel de implementatie van deze methode aan te passen, bijvoorbeeld als volgt:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
public function startTransaction() {
if ($this->_transactionStarted) { // property die bijhoudt of er een transactie loopt
throw new Exception('MySQLi: transaction already running');
} else {
$this->_connection->autocommit(false);
$this->_transactionStarted = true;
// From user comments:
// To prevent database from hanging after a script crashes during a transaction.
// Is this still an actual problem?
register_shutdown_function(array($this, 'shutdownCheck'));
}
}
?>
public function startTransaction() {
if ($this->_transactionStarted) { // property die bijhoudt of er een transactie loopt
throw new Exception('MySQLi: transaction already running');
} else {
$this->_connection->autocommit(false);
$this->_transactionStarted = true;
// From user comments:
// To prevent database from hanging after a script crashes during a transaction.
// Is this still an actual problem?
register_shutdown_function(array($this, 'shutdownCheck'));
}
}
?>
En je kunt hier dus nog meer aan ophangen zoals het registreren van een "shutdownCheck" die automatisch een rollback doet als het script crasht wat mogelijk tot gevolg zou kunnen hebben dat je database in een deadlock of soortgelijke toestand geraakt. Die rollback zorgt dat alles weer wordt teruggedraaid en dat je na afloop weer normale operatie hebt.
Het mooie hiervan is: in je code roep je simpelweg $db->startTransaction() aan en je kunt gaan. Dit laat ook meteen de toegevoegde waarde van een wrapper zien, je kunt hier een heleboel "heavy lifting" in proppen waar je in het normale gebruik geen weet van hebt.
EDIT: in plaats van mysqli->commit() zou je ook mysqli->autocommit(true) kunnen doen, dit commit tevens de queries die in de wachtrij van de transactie zitten en zet ook het auto-committen weer aan.
Gewijzigd op 09/07/2018 14:37:20 door Thomas van den Heuvel
Code (php)
1
2
3
2
3
public function inserted_id() {
return $this->db->insert_id;
}
return $this->db->insert_id;
}
Hiermee haal ik het laatst geinserte id op, dus ik voeg nu apart een user toe en later een company met als userid $conn->inserted_id()
Daarom is het belangrijk om je transacties met commit en rollback niet te mijden.
Een mooi voorbeeld van de werkwijze van transactions kan je hier zien:
http://php.net/manual/en/mysqli.rollback.php
Standaard doet MySQLi altijd een commit (uitvoeren) na je het uitvoeren van een query. Dit wil je bij transacties in eigen hand hebben. Dus ga je zelf alles committen op het moment dat het schikt, en als alles naar behoren klaar staat. Als dat fout gaat, kan je dat altijd detecteren met bijv. een $mysqli->affected_rows waarna je een rollback kan uitvoeren.
- Ariën - op 11/07/2018 12:23:50:
Als dat fout gaat, kan je dat altijd detecteren met bijv. een $mysqli->affected_rows waarna je een rollback kan uitvoeren.
Dit lijkt mij geen goede aanleiding om een transactie terug te draaien. Als de queries gewoon slagen is er geen reden om een transactie terug te draaien. Met affected_rows doe je aannames over wat het resultaat zou moeten zijn (of een effect wat deze queries zouden moeten hebben), terwijl het prima mogelijk is dat een reeks queries effectief "niets doen". Dit is niet per definitie fout. En je kunt je dan ook afvragen of je iets terug dient te draaien wat effectief toch niets deed :].
Dit is toch een beetje een "separation of concerns". Een andere manier om dit aan te pakken is de volgende: indien een query faalt (wanneer query() false retourneert) dan zou je een exception kunnen throwen. Tegelijkertijd registreer je een shutdown-functie die de transactie expliciet terugdraait (zie ook hierboven). Maar dat is (hiervoor) eigenlijk niet eens nodig. Met een exception verlaat je al de normale flow van het programma, dus wordt er nooit gecommit. Als het script eindigt volgt dan sowieso een impliciete rollback.