Gegevens in twee tabellen tegelijk inserten en id van tabel 1 doorgeven aan tabel 2

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Innovatieve, nauwkeurige Senior PHP Developer voor

Eind jaren ’90 is dit bedrijf opgericht. Ze hebben zich altijd beziggehouden met het ontwikkelen van software voor overheidsinstanties. Dit zijn instanties waaronder bijvoorbeeld ook onder andere : de belastingdienst, burgerszaken, verschillende bedrijven, notarissen, incassobureau’s en ook nog bewindvoerders. Momenteel tellen ze 20 medewerkers, waarvan 2/3 deel allemaal developers zijn. Doordat ze de enigste aanbieder van dit soort software op de markt zijn, groeien ze erg hard. Door de enorme groei, hebben ze dan ook concrete groeiplannen in het verschiet. Functie-omschrijving Het bedrijf wordt door allerlei instanties ingeschakeld. Tegenwoordig wordt alles digitaal geregeld en gezien de essentie van dit soort

Bekijk vacature »

Snelle Jaap

Snelle Jaap

09/07/2018 09:56:05
Quote Anchor link
Ik heb twee tabellen, namelijk users en company. Beide wil ik aan elkaar koppelen doormiddel van een id, de tabel company heeft een rij `userid` die ik wil koppelen aan het `id` van een user in de tabel users.

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)
PHP script in nieuw venster Selecteer het PHP script
1
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);


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
 
PHP hulp

PHP hulp

17/08/2018 01:55:44
 
- Ariën -
Beheerder

- Ariën -

09/07/2018 10:18:57
Quote Anchor link
Met de objecten uit de MySQLi-driver kan je toch ook prima transacties regelen?
 
Snelle Jaap

Snelle Jaap

09/07/2018 10:34:00
Quote Anchor link
Wat bedoel je precies? Transacties doe je toch altijd in SQL? Of bedoel je twee keer een connectie maken voor beide inserts?
 
Jan te Pas

Jan te Pas

09/07/2018 10:55:50
Quote Anchor link
Wellicht heb jij het niet helemaal goed uitgelegd. Kan een gebruiker inloggegevens aanmaken en direct een company-info ingeven? Of moet een gebruiker eerst inloggen? Is de volgorde wel goed?

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

Snelle Jaap

09/07/2018 10:59:47
Quote Anchor link
Er is een hoofdaccount, die kan gebruikers aanmaken en een bedrijf, deze zitten aan elkaar gekoppeld met het `userid`.
 
- Ariën -
Beheerder

- Ariën -

09/07/2018 12:54:48
Quote Anchor link
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)
PHP script in nieuw venster Selecteer het PHP script
1
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();
?>
Gewijzigd op 09/07/2018 12:56:54 door - Ariën -
 
Thomas van den Heuvel

Thomas van den Heuvel

09/07/2018 14:19:29
Quote Anchor link
Even voor het begrip, @Ariën heeft gelijk. Als je een echte relationele database wilt hebben (bijvoorbeeld een administratief systeem) is het verstandig -of misschien zelfs onvermijdelijk- om InnoDB te gebruiken. Daarbij zul je wel de samenhang tussen tabellen moeten definiëren. Zo zal dus het user id in de company tabel een foreign key naar de user tabel moeten bevatten.

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)
PHP script in nieuw venster Selecteer het PHP script
1
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'));
    }
}

?>

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
 
Snelle Jaap

Snelle Jaap

11/07/2018 11:51:09
Quote Anchor link
Ik hoor een hoop termen waar ik nooit van heb gehoord :) Wat om me is een keer in te verdiepen. Voorlopig heb ik mijn probleem anders opgelost, namelijk met de volgende functie in mijn connectie class:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
public function inserted_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()
 
- Ariën -
Beheerder

- Ariën -

11/07/2018 12:23:50
Quote Anchor link
Als één van die queries fout loopt, dan heb je halve data.
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.
 
Thomas van den Heuvel

Thomas van den Heuvel

11/07/2018 14:04:20
Quote Anchor link
- 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.
 



Overzicht Reageren

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.