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.

1 Year
Discussion Span
Last Post by cereal

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

  // 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);

  // 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.


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.


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

And then just run:

SELECT LAST_INSERT_ID() as `rec_id`;

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.


This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.