HELP! Php + MSSQL Dropped Data

[sledgeweb]

Hello-

Here's my situation. I have an online application form that I'm having some data trouble with. Here's a general run down of the process I use...

1) Collect application data
2) Collect payment information
3) Store data in table1
4) Submit transaction to authorize.net
5) Process return from authorize.net
6) If sucessful, store data in table2 from table1
7) Update data in table2 with transaction code from authorize
8) Delete data from table1
9) If unsuccessful, leave table1 as is and ask the user to submit new payment information. Repeat.

Table1 acts as a temporary table to hold the users application data. This is in case something happens during the transaction and a response isn't received from authorize, but the transaction was completed. So, transactions with some kind of error stay in Table1, while successful transactions are moved to Table2. They are deleted from Table1 after being moved.

This is working on 95% of transactions. However, I'm still getting 5% of successful transactions with no data in Table2. This is a problem because I've charged the user money, and don't have their application. This means our membership department has to look the person up (based on information I send to authorize) and have them re-submit all their information again. Big pain, right? For now, I've disabled the deletion of successful transactions from the temp table, Table1. But, if anyone has any ideas on why this might be happening, please help! Code below.

//If transaction response successful
if ($x_response_code == 1)
	{
                //Store a session variable
	session_register('paymentComplete');
	$_SESSION['paymentComplete'] = 1;
                //Insert into Table2 from Table1
	$query = "INSERT INTO Table2 SELECT * FROM Table1 WHERE (TimeStampId = '" . $_SESSION['timeStampId'] . "')";
	$result = mssql_query($query);
	//If insert query successful
                if ($result)
		{
                                //Add authorize.net info to Table2
		$query = "UPDATE Table2 SET TransactionCode = '$x_transaction_code', AuthorizationCode = '$x_auth_code' WHERE (TimeStampId = '" . $_SESSION['timeStampId'] . "')";
		$result = mssql_query($query);
		mssql_free_result($result);
                                //Remove data from Table1
	                $query = "DELETE FROM Table1 WHERE (TimeStampId = '" . $_SESSION['timeStampId'] . "')";
		$result = mssql_query($query);
		}
	mssql_free_result($result);
	mssql_close();
	}

Would there be a problem if two users submitted at the same time? What happens when a table is hit with two queries at the same exact time? Any other thoughts?

Thanks,
Sledgeweb

[bastien]

yes, using a timestamp id could have the impact that 2 users are submitting at exactly the same time therefore there are errors or confusion in the table

it would be better to use some kind of unique id (like session ID or some hashed version of micro time) to create very distinct ids...

the database will queue the requests and deal with them based on an internal algorithim...but we are talking microseconds between requests being processed...

you might wanna consider a stored procedure to move the data over and delete the old data based on a trigger that gets fired when the authorization code is entered into table2...

also consider adding a column to table1 called 'deleted' and marking that when the data is to be deleted...then delete the data weekly or monthly when you are sure that there are no problems with the transaction...this is what i like to do...uses a little more space, but who cares...makes transaction recoverability better for you guys

hth

[sledgeweb]

Thanks, I like the idea of purging the entries once a month, good suggestion.

To clarify, the timestamp session variable isn't just a timestamp. It is the current timestamp with the user id, which is unique, appended to the end. As unlikely as it may seem, I've had experiences in the past where I used the timestamp by itself as a unique id, and have twice had users submit data at the exact same second. Anywho, I learned that lesson a long time ago!

I don't see a need to have a marker field though. I should be able to set a procedure that deletes any entries from table1 that appear in table 2, based on the unique identifier. If I have the record in table 2, then I really don't care if it is in table1. Ideally, table 1 would only contain entries that are not in table 2.

Again, thanks for the input. You got me thinking on some new solutions...