Return record ID upon update

[showman13]

Using PHP / MySql...
I have a transaction table and a record is automatically inserted when someone Initiates a transaction, and then is updated later in the process, based on the recID.

I want / need to keep the recIDs filled with completed transactions without any skips in the ID sequence (as created by the auto increment on insert)

If someone Voids a transaction without completing it, the record status is set to ‘V’ so it can be reused for the next transaction.

My Question:
How can I get the recID of a transaction returned to my after doing an UPDATE to the record?

I can’t seem to find a way to do it, unless I do a manual QUERY for the recID after the UPDATE searching on unique data that was updated.
Is this the only way to accomplish this task or is there a built in function that would return the recID?

Thanks in advance for your feedback.
Douglas

[showman13]

Maybe this will help.

This is the Function I created to accomplish what I need. (untested)
But it just seems to me that there should be a better method, so that is what I am asking... Is there a better way to accomplish this?

//**************************************
  // Function to locate Voided transactions:
    // Function is a 2 step process
  function locate_void($tier_num, $payer, $payer_sub, $created) {
  // STEP 1
  // Function submits an update query with the new data to update the record
  // ONLY updates if a Voided Record is found in the selected Tier table
  $sql_u = "
    UPDATE tier_".$tier_num."
    SET payer_ID = '".$payer."', sub_ID = '".$payer_sub."', create_date = '".$created."',  status = 'I'
    WHERE status = 'V'
    ORDER BY rec_ID asc
    LIMIT 1
  " ;
  mysql_query($sql_u);

  // STEP 2
  // Function selects a record based on the same data that was just used in the Update query
  $sql = "
    SELECT rec_ID
    FROM tier_".$tier_num."
    WHERE payer_ID = '".$payer."' AND  sub_ID = '"$payer_sub"' AND create_date = '".$created."' AND  status = 'I'
    LIMIT 1
  " ;

  if ($result = mysql_query($sql)){
    $row = mysql_fetch_row($result);
    $rec_ID=$row[0];
  }else{
    $rec_ID=0;
  }

  // If a rec_ID is returned, then a voided trans was located and updated
  // return the updated rec_ID, if zero, no update was done, so do INSERT
  return $rec_ID;
  }

Any response would be greatly appreciated.

[showman13]

Well, I guess that was a useless waste of a donation to get an expedited answer from someone that knows what they are talking about.

[cereal]

If the update is limited to a single row, as in your example query, you could add rec_id = LAST_INSERT_ID(rec_id) to register the value to the function and then select it, for example:

UPDATE tier_".$tier_num."
SET payer_ID = '".$payer."', sub_ID = '".$payer_sub."', create_date = '".$created."',  status = 'I', rec_id = LAST_INSERT_ID(rec_id)
WHERE status = 'V'
ORDER BY rec_ID asc
LIMIT 1

And then just run:

SELECT LAST_INSERT_ID() as `rec_id`;

[showman13]

I may be wrong, but I believe the last insert id only relates to INSERT and not to UPDATE.

After an exhaustive search online, I don't find anything that will accomplish what i need in any more direct manner.

Guess I'll stick with what I have.

Thanks
Douglas

[cereal]

LAST_INSER_ID() without arguments will return the last inserted id, if you pass the autoincrement key, during an update query, then it will return it, as in my example. This is how you get the id of the updated row in MySQL. Check the examples here:

• http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_last-insert-id

Bye!