phpmysqlmysqliauto-increment

MySQL insert_id returning null even though there was an insert

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.

Solution

  • 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;