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

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.

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
LIMIT 1

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.

Thanks
Douglas

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.