CODEWITHSUNDEEP
phpmysqlsqlitepdosql-insert
Azevedo
Azevedo

Reputation: 2189

SQLite: last_insert_rowid() in same INSERT statement

I running this query in PHP/SQLite 3 (PDO)

Scenario: a new driver is inserted into drivers table and an existing car is immediately linked to him:

DRIVERS

CARS

It inserts the driver but does not UPDATE the cars table and produces no error either.

It works if I use the same query using SQLite Spy.

In PHP it will only if I break it in two parts:

$qr = "INSERT INTO drivers (driver_name) VALUES ('{$_GET['driver_name']}'); COMMIT; ";
$stmt = $dbh->prepare($qr);
$result = $stmt->execute();

$qr = "UPDATE cars SET fk_driver_id=( SELECT last_insert_rowid() ) WHERE car_id={$_GET['car_id']};";
$stmt = $dbh->prepare($qr);
$result = $stmt->execute();

What is wrong in the PHP code if it won't work in one single statement?

Upvotes: 0

Views: 505

Answers (1)

Alex
Alex

Reputation: 17289

Try this way :

$qr = "INSERT INTO drivers (driver_name) VALUES ('{$_GET['driver_name']}'); ";
$stmt = $dbh->prepare($qr);
$result = $stmt->execute();
$lastId = $dbh->lastInsertId();
$dbh->commit();

$qr = "UPDATE cars SET fk_driver_id=? WHERE car_id={$_GET['car_id']};";
$stmt = $dbh->prepare($qr);
$result = $stmt->execute(array($lastId));

Upvotes: 0

Related Questions