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