Multi Query Insert

Overzicht Reageren

Sponsored by: Vacatures door Monsterboard

Danny von Gaal

Danny von Gaal

10/02/2017 22:05:27
Quote Anchor link
Hallo,

Ik probeer voor het eerst multi query maar ik krijg een Success terug wanneer de bovenste Insert wel werkt en de onderste niet.

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
<?php
if ($HasError == 0) {
        
        // Insert in database
        $SQLInsertUser = "INSERT INTO users
                         (email, password, reg_date, ipaddress, verified)
                         VALUES
                         ('"
. $FormEmail . "', '" . $FormPassword . "', '" . $FormDate . "', '" . $FormIpaddress . "', 'no');";
        
        $SQLInsertUser .= "INSERT INTO verify
                          (email, hash2)
                           VALUES
                          ('"
. $FormEmail . "', '" . $RandomHash . "')";

        if ($conn->multi_query($SQLInsertUser) === FALSE) {
            
            // Nice error if e-mail is duplicate
            if ($conn->errno == 1062) {
            
                echo $lang['formhandling-register-email-duplicate'];
                echo $lang['form-try-again'];
                exit;
                
            }
else {
            
                echo "Error: " . $conn->errno;
                
            }
            
        }
else {
            
            echo "Success";
            
        }

        
        $conn->close();
        exit;
        
    }

?>
 
PHP hulp

PHP hulp

05/07/2020 08:06:45
 
Thomas van den Heuvel

Thomas van den Heuvel

11/02/2017 10:08:28
Quote Anchor link
Er zitten een héleboel haken en ogen aan multiqueries, maar om het hier kort te houden: als het de bedoeling is dat een set queries in zijn geheel, of in zijn geheel niet moet worden uitgevoerd dan zou je gebruik moeten maken van transacties. Transacties stellen je in staat om een set queries als één ondeelbare actie (wel of niet) uit te voeren.

Multiqueries zijn hier simpelweg niet voor geschikt, vooral als de kans bestaat dat ergens onderweg een fout optreedt: alle queries tot dat punt zijn dan namelijk al verwerkt wat mogelijk voor rotzooi in je database zorgt.
 
Danny von Gaal

Danny von Gaal

11/02/2017 15:19:28
Quote Anchor link
Bedankt voor je reactie. w3schools doet voorkomen alsof het de normaalste zaak van de wereld is:
http://www.w3schools.com/php/php_mysql_insert_multiple.asp

Ik weet van transacties af alleen is het me nog niet gelukt. Ik ga wel een andere manier bedenken, bedankt.
 
- Ariën -
Beheerder

- Ariën -

11/02/2017 15:58:44
Quote Anchor link
Opsich zou dit moeten werken:
http://www.phptuts.nl/view/26/6/
 
Danny von Gaal

Danny von Gaal

11/02/2017 16:35:54
Quote Anchor link
- Ariën - op 11/02/2017 15:58:44:
Opsich zou dit moeten werken:
http://www.phptuts.nl/view/26/6/


Bedankt, dat ziet er niet erg moeilijk uit. :)
 
Thomas van den Heuvel

Thomas van den Heuvel

11/02/2017 20:40:46
Quote Anchor link
Wat lukt er niet aan transacties dan? Het enige wat echt nodig is dat je gebruik maakt van tabellen met de InnoDB engine, maar dat is sowieso wel een goed plan als je een administratieve database aan het bouwen bent, je hebt dan namelijk de beschikking over foreign key( constraint)s wat er verder toe bijdraagt dat je data referentieel integer is en blijft.

Ik denk dat als je wilt afdwingen dat een reeks queries als batches uitgevoerd worden (in het geheel wel, of in het geheel niet) dat transacties eigenlijk de enige (en enige juiste) gedoodverfde manier is.

Een aantal argumenten om geen multi_query() te gebruiken:
- detectie of iets fout gaat is soms lastig en nogal omslachtig
- als er iets fout is gegaan zit er waarschijnlijk onvolledige data in je database...
- ... die je weer op een of andere manier moet repareren, ongedaan moet maken
- prepared statements werken niet i.c.m. multi_query() (maar als je die per se wilt gebruiken is PDO wellicht een beter alternatief)
- het consequent escapen van DATA binnen de SQL is bij multi_query() extra belangrijk, je wilt namelijk niet dat iemand hier een DROP DATABASE hennie; aan vastfietst
 
- Ariën -
Beheerder

- Ariën -

11/02/2017 20:43:47
Quote Anchor link
De vraag is alleen, waar is multi_query() nou wel goed voor?
Ik zat zelf te denken aan een rits aan bijvoorbeeld INSERT-queries waarvan je zeker bent dat die werken, om bijvoorbeeld een tabel met data te vullen. Maar dan kan je net zo goed een foreach en een $mysqli->query() gebruiken.
 
Thomas van den Heuvel

Thomas van den Heuvel

11/02/2017 21:37:10
Quote Anchor link
- Ariën - op 11/02/2017 20:43:47:
Ik zat zelf te denken aan een rits aan bijvoorbeeld INSERT-queries waarvan je zeker bent dat die werken, om bijvoorbeeld een tabel met data te vullen.

Mja, maar soms wil je voor een correcte werking tabellen/kolommen kunnen locken zodat je race conditions kunt voorkomen. Transacties zijn daar bij uitstek geschikt voor.

Soms is het simpelweg niet voldoende om "enkele foutloze queries achter elkaar uit te voeren" omdat als dat proces meerdere keren tegelijkertijd wordt uitgevoerd, de queries van deze verschillende aanroepen mogelijk met elkaar verweven zijn wat soms (en zonder transacties / locking) mogelijk ongewenste situaties oplevert.

Een klassiek voorbeeld daarvan is een situatie waarin je geld overmaakt van rekening A naar rekening B. Je wilt dat de hele mutatie één ondeelbare actie vormt anders bestaat de kans dat er vreemde / ongewenste dingen gebeuren met saldo's.

Eigenlijk in alle systemen waarin meerdere gebruikers mogelijk dezelfde data bewerken is het inzetten van transacties handig / verstandig.
Gewijzigd op 11/02/2017 21:43:48 door Thomas van den Heuvel
 
Willem vp

Willem vp

12/02/2017 12:59:52
Quote Anchor link
- Ariën - op 11/02/2017 20:43:47:
De vraag is alleen, waar is multi_query() nou wel goed voor?
Ik zat zelf te denken aan een rits aan bijvoorbeeld INSERT-queries waarvan je zeker bent dat die werken, om bijvoorbeeld een tabel met data te vullen. Maar dan kan je net zo goed een foreach en een $mysqli->query() gebruiken.

En in dat geval zou ik zelfs nóg liever 1 insert-query gebruiken met een lijst values, omdat dat qua performance beter is dan allemaal losse inserts.
 
- Ariën -
Beheerder

- Ariën -

12/02/2017 13:09:32
Quote Anchor link
Dat is eigenlijk ook weer zo. Praktisch gezien wordt dat als één schrijfactie op de server gezien?
 
Willem vp

Willem vp

12/02/2017 14:02:34
Quote Anchor link
> Praktisch gezien wordt dat als één schrijfactie op de server gezien?

Voor de toeschouwer lijkt het wel zo, inderdaad. ;-) Waarschijnlijk zal de server evengoed losse schrijfacties gebruiken, maar de grootste winst is dat hij niet steeds het query execution plan hoeft te maken. Met prepared statements hoeft de server dat ook niet, maar daar moet je voor elk record een execute() uitvoeren, wat ook weer overhead geeft.
 
Thomas van den Heuvel

Thomas van den Heuvel

13/02/2017 16:10:10
Quote Anchor link
De database-oplossing en query-strategie hangt in ieder geval sterk af van de applicatie. Als je vaak queries hebt met inserts met meerdere (grote) sets van values dan wordt alles nogal traag volgens mij. Daarnaast zou je dit kunnen stresstesten om te zien wat beter werkt. Meten = weten.

Maar terug naar het oorspronkelijke onderwerp, bovenstaande aanpak gaat niet werken omdat je met verschillende tabellen te maken hebt.

Wacht eens, als ik nu alles in één tabel sto... :p
 
Willem vp

Willem vp

14/02/2017 08:43:43
Quote Anchor link
Op mijn werk worden er om verschillende redenen behoorlijk wat records rondgepompt. Daar heb ik allerlei processen dramatisch kunnen versnellen door losse inserts of inserts via prepared statements om te schrijven naar bulk inserts (insert met meerdere values of insert select). Ik praat dan over inserts die variëren van 1200 tot meer dan een half miljoen records per statement.

Overigens, zonder het achterliggend datamodel te kennen: als ik kijk naar de queries in de original post, vind ik het goed te verdedigen om alles in 1 tabel te stoppen.
Gewijzigd op 14/02/2017 08:44:58 door Willem vp
 
Ward van der Put
Moderator

Ward van der Put

14/02/2017 09:14:25
Quote Anchor link
Willem vp op 14/02/2017 08:43:43:
Overigens, zonder het achterliggend datamodel te kennen: als ik kijk naar de queries in de original post, vind ik het goed te verdedigen om alles in 1 tabel te stoppen.

Zo te zien hoort alles inderdaad in één tabel:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
<?php
        // Insert in database
        $SQLInsertUser = "INSERT INTO users
                         (email, password, reg_date, ipaddress, verified)
                         VALUES
                         ('"
. $FormEmail . "', '" . $FormPassword . "', '" . $FormDate . "', '" . $FormIpaddress . "', 'no');";
        
        $SQLInsertUser .= "INSERT INTO verify
                          (email, hash2)
                           VALUES
                          ('"
. $FormEmail . "', '" . $RandomHash . "')";
?>

Ik mis hier verschillende dingen:

• De tweede tabel 'verify' bevat alleen een "hash" (die eigenlijk geen hash is maar een random token) bij het e-mailadres dat ook voorkomt in de eerste tabel.

• Er wordt niet opgeslagen wanneer die "hash" voor de verificatie wordt gegenereerd, dus er kunnen kennelijk tot in alle eeuwigheid ongeverifieerde e-mailadressen circuleren.

• Er is geen primaire sleutel die de twee tabellen koppelt (tenzij dat het e-mailadres is, maar dat is om diverse redenen niet handig).

• In de eerste tabel staat de kolom 'verified' op de string 'no', maar daarvan kun je beter een flag maken van 1 bit, bijvoorbeeld een TINYINT(1), of een TIMESTAMP als je het verschil tussen de registratiedatum en de verificatiedatum wilt kunnen gebruiken. Als je security by design toepast, zou de veiligste toestand 'verified' == 'no' bovendien de standaardtoestand moeten zijn — en kun je die weglaten in de query.

• Als je toch in één keer twee queries wilt uitvoeren voor een INSERT van één gebruiker, kun je hier veel beter een transactie gebruiken dan een multiquery.
 
Danny von Gaal

Danny von Gaal

14/02/2017 09:46:25
Quote Anchor link
Ik wil liever niet alles in een tabel omdat ik de user tabel klein wil houden met alleen belangrijke gegevens. Wanneer iemand zijn emailadres heeft geverifieerd wordt de entry van de verify tabel ook weer netjes verwijderd.

In beide tabbelen staat email op UNIQUE dus ik krijg geen eeuwigheid aan ongeverifieerde emailadressen. Tevens heeft de tabel verify een TIMESTAMP kolom die update on entry. Ik kan dus altijd zien wanneer dit is gebeurd en kan dit eventueel na twee weken weer verwijderen. Ik geef ook in de email aan dat je binnen twee weken je email moet verifieren.

Beide tabbelen hebben ook een primary key "id".

Wat betreft verified = 'no' dat kan ik veranderen naar de tinyint. Bedankt voor de tip.

Ik ga de transacties tevens toevoegen waar Ariën een voorbeeld van stuurde.
 
Ward van der Put
Moderator

Ward van der Put

14/02/2017 11:59:09
Quote Anchor link
Danny von Gaal op 14/02/2017 09:46:25:
Ik wil liever niet alles in een tabel omdat ik de user tabel klein wil houden met alleen belangrijke gegevens. Wanneer iemand zijn emailadres heeft geverifieerd wordt de entry van de verify tabel ook weer netjes verwijderd.

Je kunt die kolom gerust naar de hoofdtabel users verplaatsen. Als je de kolom nullable maakt met DEFAULT NULL, ga je pas bij honderdduizenden gebruikers verschil merken. In ruil daarvoor krijg je in een veel eerder stadium al een performance-winst, omdat je niet meer twee queries hoeft uit te voeren.

Danny von Gaal op 14/02/2017 09:46:25:
In beide tabbelen staat email op UNIQUE dus ik krijg geen eeuwigheid aan ongeverifieerde emailadressen.

Daar moet je voorzichtig mee zijn, merkte ik afgelopen week weer toen een teamlid me met mijn Gmail-adres aan Slack had toegevoegd. Voor Google maakt het niet uit of je een e-mailadres schrijft als Foo.Bar@gmail.com met hoofdletters, foo.bar@gmail.com met kleine letters of FooBar@gmail.com en foobar@gmail.com zonder punt. Voor Google is dat één e-mailadres, maar maak je de kolom UNIQUE, dan zijn het verschillende e-mailadressen. Bij Slack hadden ze daarmee ook geen rekening gehouden, dus ik kon niet inloggen mét een geldig e-mailadres.
Gewijzigd op 14/02/2017 11:59:30 door Ward van der Put
 
Danny von Gaal

Danny von Gaal

14/02/2017 12:35:55
Quote Anchor link
Oké dank je daar kan ik wat mee.

En los van dat de kolom op Unique staat kan ik eerst alle letters lowercase maken voor ik het insert anders. Met en zonder punt zijn voor mij wel andere emailadressen.
Gewijzigd op 14/02/2017 12:36:41 door Danny von Gaal
 
Frank Nietbelangrijk

Frank Nietbelangrijk

14/02/2017 13:23:29
Quote Anchor link
@ward: Zo ver ik weet zijn ALLE emailadressen case INsensitive. Dus komt FoO.BaR@server.com gewoon aan bij foo.bar@server.com. Dus zou lowercase maken inderdaad een hoop problemen kunnen vermijden.


(Wéér een reden om OOP te programmeren, hoef je enkel maar je setter aan te passen)
Gewijzigd op 14/02/2017 13:24:41 door Frank Nietbelangrijk
 
Willem vp

Willem vp

14/02/2017 14:32:29
Quote Anchor link
Frank Nietbelangrijk op 14/02/2017 13:23:29:
@ward: Zo ver ik weet zijn ALLE emailadressen case INsensitive.

Het gedeelte vóór de @ mag volgens de RFC case-sensitive zijn. In de praktijk ben ik nog geen server tegengekomen die dat onderscheid maakt.

Edit:
Je moet overigens niet zoeken op de term 'case sensitive':
Quote:
the local-part MUST be interpreted and assigned semantics only by the host specified in the domain part of the address.

Wat hier eigenlijk staat, is dat software die email-adressen verwerkt niet kan weten hoe de ontvangende mailserver met het local-part van een adres omgaat (het gedeelte voor de @ dus). Zoals Ward al beschreef kan het zijn dat punten in het local-part niet relevant zijn. Maar ook hoofdlettergevoeligheid valt daaronder.

Edit:
Nog een reden waarom het niet heel veel nut heeft om een unique constraint op email-adressen te zetten is RFC 5233. Ruwweg samengevat wordt daarin beschreven dat gebruiker+blabla@ een alias (sub-adres) is van gebruiker@.
Gewijzigd op 14/02/2017 15:06:04 door Willem vp
 
Frank Nietbelangrijk

Frank Nietbelangrijk

14/02/2017 16:11:29
Quote Anchor link
Dat is goed om te weten Willem. Bedankt voor jouw bijdrage.

Het zijn naar mijn idee beiden geen zaken om je heel erg druk over te maken maar je zou in theorie kunnen meemaken dat een bedrijf de volgende mensen en mailadressen in gebruik heeft:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
John Doe j.doe@bedrijf.nl
Jens Doe J.doe@bedrijf.nl


Maar als dat zo is zullen ze daar snel vanaf willen denk ik want dan gaat het wel heel vaak mis of niet?
 



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.