I have the following PHP code. I wish to return the last id of the insert:
$sql = $this->dbLink->prepare("
INSERT INTO VesselTransits (CreatedByUserID, TerminalID, VesselArrivalDate, VesselNameID)
VALUES (?,?,?,?)
");
//i=integer, s=string
$sql->bind_param('iisi', $userID, $terminalID, $arrivalDate, $vesselNameID);
$userID = $this->invoiceData['userID'];
$terminalID = $this->invoiceData['terminalID'];
$arrivalDate = date('Y-m-d', strtotime($this->invoiceData['berthDate']));
$vesselNameID = $this->invoiceData['vesselNameID'];
$sql->execute();
$vesselTransitID = $sql->insert_id;
//echo('RecordInvoice line '.__LINE__.'<pre>');print_r($vesselTransitID);echo('</pre>');exit();
return($vesselTransitID);
My DB schema is as follows:
CreatedByUserID int
CreationDate datetime
ModifiedByUser int
ModifyDate datetime
TerminalID int
VesselArrivalDate datetime
VesselNameID int
VesselTransitID int [auto_increment]
I've thrown the kitchen sink at it, but no dice. No matter what I do, $vesselTransitID
is always undefined. I have verified that there is actually an insert. I'm left with the only (HORRIBLE) option of running another select and returning the MAX VesselTransitID, which doesn't guarantee the correct result.
While the statement object has a insert_id
property, it does appear to cause issues from time to time, as here.
As the statement objects insert_id
property is still after all these years not completely documented, I would suggest for simplicity and safety using the connection objects version of insert_id
So do
$vesselTransitID = $this->dbLink->insert_id;