[PHP] Meerdere queries in mysqli transaction i.c.m. binding

[ZeroXT]

Ik probeer via Mysqli een transaction te starten met meerdere queries die afhankelijk zijn van elkaar.

PHP:

//Let op: zwaar versimpelde versie van de transaction:

$value = 5;

$mysqli -> autocommit(FALSE);
$mysqli -> begin_transaction(MYSQLI_TRANS_START_READ_WRITE);

$stmt = $mysqli -> prepare('SELECT @last_sale_guid := (guid + 1) AS last_sale_guid FROM sale ORDER BY id DESC LIMIT 1;');
$stmt -> execute();

//Onderstaande $stmt retourneert false omdat "@last_sale_guid" waarschijnlijk niet beschikbaar is vanuit de vorige query
$stmt = $mysqli -> prepare('SELECT CASE WHEN @last_sale_guid IS NULL THEN @last_sale_guid := 1 ELSE @last_sale_guid := @last_sale_guid END AS last_sale_guid;');
$stmt -> execute();

$stmt = $mysqli -> prepare('INSERT INTO sale (guid, establishment_id) VALUES(@last_sale_guid, ?);');
$stmt -> bind_param('i', $value);
$stmt -> execute();

$mysqli -> commit();

De tweede statement is echter false en dit komt waarschijnlijk omdat "@last_sale_guid" niet beschikbaar is vanuit de eerste statement.

Ik heb na voorbeelden gezocht, echter zijn deze enkel met een enkele query i.c.m. binding en transaction.

Heeft iemand een idee of dit functioneel kan worden en zo ja, hoe?

(ps. ik heb dit wel werkend gekregen met de multi_query functie, echter kan ik hiermee niet standaard variablen binden en hiermee SQL injection tegengaan)

[orf]

Waarom niet gewoon subqueries gebruiken? Dan past het prima in 1 statement

[ZeroXT]

Zoals al eerder beschreven. Het is een zwaar versimpelde query. Ik ben me ervan bewust dat deze queries ook in één kan, maar voor de volledige query is dit niet mogelijk/wenselijk.

[orf]

En wat gebeurt er als je een SET gebruikt?

User-Defined Variables

You can store a value in a user-defined variable in one statement and then refer to it later in another statement. This enables you to pass values from one statement to another. User-defined variables are session-specific. That is, a user variable defined by one client cannot be seen or used by other clients. All variables for a given client session are automatically freed when that client exits.

User variables are written as @var_name, where the variable name var_name consists of alphanumeric characters, “.”, “_”, and “$”. A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var', @"my-var", or @my-var).

https://dev.mysql.com/doc/refman/5.6/en/user-variables.html

PHP:

$stmt = $mysqli -> prepare('SET @last_sale_guid := (guid + 1) AS last_sale_guid FROM sale ORDER BY id DESC LIMIT 1;');

[ZeroXT]

Dat is een goede tip. Het werkt niet exact zoals je had beschreven maar met de onderstaande code werkt het wel:

PHP:

$stmt = $mysqli -> prepare('SET @last_sale_guid = (SELECT (guid + 1) FROM sale ORDER BY id DESC LIMIT 1);');

Echter zijn hiermee drie nieuwe problemen gekomen:

Dubbele producten:
Wanneer ik alle statements in een for loop plaats. Lijken deze twee keer zoveel uitgevoerd te worden. For loop 0 - 100, resultaat is 200 producten toegevoegd.

Niet consistent
Wanneer ik meerdere keren direct achter elkaar de for loop uitvoer worden de producten toegevoegd aan de database. Het aantal producten welke toegevoegd worden verschilt. Wanneer ik de functie twee keer uitvoer worden er 400 producten toegevoegd. Wanneer ik dit snel achter elkaar doe, dan kan het zo zijn dat er maar 397 producten worden toegevoegd.

Meerdere variabelen setten werkt niet
Onderstaande voorbeeld gaat niet werken omdat de variabelen enkel geset kunnen worden met de SET functie zoals hierboven beschreven. Echter kan ik maar één variabel selecteren bij een SELECT dus moet ik meerdere SELECT statements maken om alle SETS te vullen.

SQL:

SELECT 
    @last_sale_id   := id AS last_sale_id,
    @last_sale_guid := (guid + 1) AS last_sale_guid
FROM 
    sale