PDO::lastInsertID returning 0

Question

After reading and trying almost every answer on this subject lastInsertId still returns 0. I think I might be overlooking something quite obvious, but can't seem to find what.

This is my code:

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();
        $workorderid = $this->db->lastInsertId();
        echo $workorderid;
        exit();

        return $stmt;

        save_lines($workorderid, $lineids, $amounts, $descriptions, $prices);
    } catch(PDOException $e) {
        echo $e->getMessage();
    }
}

$this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE); use before prepare — devpro, Feb 11 '16 at 13:33
Possible duplicate of [http://stackoverflow.com/questions/11776818/pdo-lastinsertid-always-return-0](http://stackoverflow.com/questions/11776818/pdo-lastinsertid-always-return-0) — Vijay Sai Chaudary, Feb 11 '16 at 13:35
When I add `$this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES,TRUE);` it works. — Martijn, Feb 11 '16 at 13:37
@devpro Please explain? I am facinated to know why putting off compiling a query until the `->execute()` is run would make that work when it did not before. As I thought it was defaulted to TRUE anyway — RiggsFolly, Feb 11 '16 at 13:39
It's already TRUE by default - so it shouldn't affect anything. Looks like there was a typo that you accidentally fixed. — Your Common Sense, Feb 11 '16 at 13:40
@RiggsFolly: yes brother u r right, but again its working for OP, dont know Y, manual mention this `PDO::ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them.` i am also want to know the reason. — devpro, Feb 11 '16 at 13:52

Martijn · Accepted Answer · 2016-02-11T17:46:50.720

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

PDO::lastInsertID returning 0

1 Answers1