SQLite: last_insert_rowid() in same INSERT statement

0
votes

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

  • driver_id [PK]
  • driver_name

CARS

  • car_id [PK]

  • fk_driver_id [FK]

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

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?

1
phpmysqlsqlitepdosql-insert
print the sql statements sent to execute to be sure they are what you believe. The only reason I can see for the update not to work is the WHERE condition – koriander
Are you sure that executing multiple statements with a single execute is even possible? – CL.

1 Answers

0
votes

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));