PDO lastInsertId in the same query

Question

Hello so I have a table named tblcontactlist and have 5 columns (contactID, contactName, contactEmail, contactNumber, hashed_id) and this is my working query

$query = "INSERT INTO tblcontactlist (contactName, contactEmail, contactNumber) VALUES (:cname, :cea, :cnum)";
        $stmt  = $dbc->prepare($query);
        $stmt->bindParam(':cname', $contactName);
        $stmt->bindParam(':cea', $emailAdd);
        $stmt->bindParam(':cnum', $contactNumber);
        $stmt->execute();

        $last_id = $dbc->lastInsertId('contactID');
        $hashed_id = sha1($last_id);

$query2 = "UPDATE tblcontactlist SET hashed_id=:hid WHERE contactID=:cid";
        $stmt2 = $dbc->prepare($query2);
        $stmt2->bindParam(':hid', $hashed_id);
        $stmt2->bindParam(':cid', $last_id);
        $stmt2->execute(); 

What this basically does is insert a new record then updates the latest inserted record with a hashed id on the hashed_id column. Is there a proper way of doing this? I mean shorter code or better code. Thanks!

Answer 1

lastInsertId presupposes that you have a previous INSERT beforehand, that you don't have. In this case, lastInsertId is the max contactID. So I would perform a query to get and hash the max contactID and then perform one insert query (and no update).

//fetch Max contactID
    $res=$dbc->prepare("SELECT MAX(contactID) FROM tblcontactlist");    
    $res->execute();        
    $fetchMax=$res->fetch(PDO::FETCH_NUM);

    $last_id=$fetchMax[0];
//hash the max contactID
    $hashed_id = sha1($last_id);
  //for reusability you can create a function with the above code.

And now perform the insert query:

  $query = "INSERT INTO tblcontactlist (contactName, contactEmail, contactNumber, hashed_id) VALUES (:cname, :cea, :cnum, :hid)";               
            $stmt  = $dbc->prepare($query);    
            $stmt->bindParam(':cname', $contactName);
            $stmt->bindParam(':cea', $emailAdd);
            $stmt->bindParam(':cnum', $contactNumber);
            $stmt->bindParam(':hid', $hashed_id);
            $stmt->execute(); 

Is that better for you?