phpmysqlpdolastinsertid

PDO::lastInsertID returning 0


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

Solution

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