last inserted id

[Muhammad Shahzad]

Hi,

How can I get last inserted id?

Initializing database connection in index.php

$f3->set("db.instance", new DB\SQL(
    "mysql:host=" . $f3->get("db.host") . ";port=" . $f3->get("db.port") . ";dbname=" . $f3->get("db.name"),
    $f3->get("db.user"),
    $f3->get("db.pass")
));

Here is my code in controller:

$sql = "INSERT into table ...."
$res = $f3->get('db.instance')->exec($sql);
print_r($res); // always display me 1

echo $res->id; //Trying to get property of non-object

[Vester]

You should use:

$id = $f3->get('db.instance')->lastInsertId();

$f3->get('db.instance') is a F3 SQL object and it forwards all non-implemented methods to the underlying PDO object ($this->pdo) via __call()

[Muhammad Shahzad]

Hi,

Thank you for your reply.

Is it reliable method? I meant once I insert the record and at the same time someone else do the same thing, so last insert id will not mix up?

[Muhammad Shahzad]

Is there any other method to insert record using insert query instead of calling exec function or sql mapper class?

[Vester]

It's unlikely, but it could happen.

To avoid this, you can use transactions. Just set the table engine to one that supports transactions (e.g InnoDB) and wrap your SQL sentences between $f3->get('db.instance')->begin() and $f3->get('db.instance')->commit(); the lastInsertId() must be placed before commit().

[Vester]

Well, exec() uses PDO and SQL Mapper too.

If your DB engine is MySQL/MariaDB, you can use MySQLi extension (http://php.net/manual/en/book.mysqli.php). PDO is more universal: the same class works for several DB Engines.

If you don't need the 'id' values to be in strict sequential order as natural numbers (autoincrement), you can generate the 'id' yourself before the insert based on microtime(), a time based UUID or a random hash. Then you supply the generated 'id' to the insert query with other values.

[Muhammad Shahzad]

Thank you, informative.