Pdo Lastinsertid() Always Return 0

PDO lastInsertId() always return 0

Other than a bug in php/PDO or your framework, there are two possibilities. Either lastInsertId() is called on a different MySQL connection than the insert, or you are generating the id in your application/framework and inserting it, rather than letting auto_increment generate it for you. Which column in the table is the primary key/auto_increment? Is that column included in $attributes in your create() function?

You can test PDO to make sure that part is working correctly with this code (in a new file):

// Replace the database connection information, username and password with your own.
$conn = new PDO('mysql:dbname=test;host=127.0.0.1', 'user', 'password');

$conn->exec('CREATE TABLE testIncrement ' .
            '(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50))');
$sth = $conn->prepare('INSERT INTO testIncrement (name) VALUES (:name)');
$sth->execute([':name' => 'foo']);
var_dump($conn->lastInsertId());
$conn->exec('DROP TABLE testIncrement');

When I ran this script, the output was

string(1) "1"

pdo lastInsertId returns zero(0)

With PDO_MySQL we must use

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE); // there are other ways to set attributes. this is one

so that we can run multiple queries like:

$foo = $DB->prepare("SELECT * FROM var_lst;INSERT INTO var_lst (value) VALUES ('durjdn')");

but sadly, doing so relieves the $DB from returning the correct insert id. You would have to run them separately to be able to retrieve the insert id. This returns the correct insert id:

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE);
$foo = $DB->prepare("INSERT INTO var_lst (value) VALUES ('durjdn')");
$foo->execute();
echo $DB->lastInsertId();

but this won't:

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE);
$foo = $DB->prepare("SELECT * FROM var_lst;INSERT INTO var_lst (value) VALUES ('durjdn')");
$foo->execute();
echo $DB->lastInsertId();

and this won't even run the two queries:

$DB->setAttribute(PDO::ATTR_EMULATE_PREPARES,FALSE); // When false, prepare() returns an error
$foo = $DB->prepare("SELECT * FROM var_lst;INSERT INTO var_lst (value) VALUES ('durjdn')");
$foo->execute();
echo $DB->lastInsertId();

lastInsertId returns 0

$lastInsertId() returns the ID that was inserted on the same connection. You get a new connection every time you call $this->connect().

You need to save the result of $this-connect() so you use the same connection.

public function insert_row($created_at){
    $sql = "INSERT INTO empty_rows (created_at) VALUES (?)";
    $connect = $this->connect();
    $stmt = $connect->prepare($sql);
    $stmt->execute([$created_at]);  
    $id = $connect->lastInsertId();

    echo $id;
}

In general you'll slow your application down significantly if you keep creating new connections. I suggest you change the connect() method so it only connects the first time.

protected function connect(){
    if (!isset($this->pdo) {
        $dns = "mysql:host=" . $this->server . ";dbname=" . $this->database;
        $this->$pdo = new PDO($dns, $this->username, $this->password);
        $this->$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }
    return $this->$pdo;
}

Why does PDO::lastInsertId return 0?

Remove the semicolon or the whitespace after the semicolon or both from your query:

$sql = "
INSERT INTO `wallet` (`uid`, `created_at`, `amount`, `type`, `payment_id`) VALUES (:uid, CURRENT_TIMESTAMP, :amount, 'payment', :payment_id)
";

PDO::lastInsertID returning 0

It was something obvious...
I forgot to check if the record already existed. If I don't change anything in the record (workorder) the SQL query is not doing anything and the lastInsertId returned 0.

My updated code (checking the orderid for an id first):

public function create($ordertype, $userid, $travel, $distance, $time, $description, $lineids, $amounts, $descriptions, $prices, $orderid = null) {
        try {
            $stmt = $this->db->prepare("INSERT INTO workorder(orderid, userid, ordertype, travel, description, distance, totaltime) 
                                        VALUES(:orderid, :userid, :ordertype, :travel, :description, :distance, :totaltime)
                                        ON DUPLICATE KEY UPDATE userid=:userid, ordertype=:ordertype, travel=:travel, description=:description,
                                        distance=:distance, totaltime=:totaltime");

            $stmt->bindparam(":orderid", $orderid);
            $stmt->bindparam(":userid", $userid);
            $stmt->bindparam(":ordertype", $ordertype);
            $stmt->bindparam(":travel", $travel);
            $stmt->bindparam(":description", $description);
            $stmt->bindparam(":distance", $distance);
            $stmt->bindparam(":totaltime", $time);
            $stmt->execute();
            if($orderid == null) {
                $workorderid = $this->db->lastInsertId();
            } else {
                $workorderid = $orderid;
            }
            if($amounts !== '') {
                $this->save_lines($workorderid, $lineids, $amounts, $descriptions, $prices);
            }
        } catch(PDOException $e) {
            echo $e->getMessage();
        }
    }

lastInsertID returning 0, even with a persistent Connection

You would need to capture and store the ID when the row is inserted.

sonderbuchungEditID.php:

<?php
    echo isset($_SESSION['last_id']) ? $_SESSION['last_id'] : "-1";
?>

reservationType.php:

<?php
require_once('bdd.php');

if(isset($_POST['reservationtype'])){
    $reservationtype = $_POST['reservationtype'];
    $sql = "INSERT INTO reservations(reservationtype) values ('$reservationtype')";
    $query = $bdd->prepare($sql);
    if ($query == false) {
        file_put_contents('LOGname.txt', print_r($bdd->errorInfo(), true));
        die ('Error prepairing');
    }
    $sth = $query->execute();
    if ($sth == false) {
        file_put_contents('LOGname.txt', print_r($query->errorInfo(), true));
        die ('Error executing');
    } else {
        //  Remember the last ID inserted.
        $_SESSION['last_id'] = $bdd->lastInsertID();
    }
}

?>

PDO lastinsertId is returning 0 in a transaction, php - 5.6

Get the insert id before committing your transaction:

$id = self::$conn->lastInsertId(); 
self::$conn->commit();

http://www.php.net/manual/en/pdo.lastinsertid.php#85129

Pdo Lastinsertid() Always Return 0