April 11, 2012

mysql database insert is changing all IDs to 4294967295

Question by three3

Something really weird is going on with my database. I am using PHP to insert data into my database and I have been doing this for the past 2 years without any problems. When a customer makes a payment on my website, I store all of the data from that transaction in my database. Each transaction has a unique “transaction_id”. When I insert the payment information into the database, all of the information is correctly inserted except for the “transaction_id”. ALL transactions are given the “transaction_id” of “4294967295”. So I did some testing. Here is what I did:

1) I echoed out the “transaction_id” to my screen to see what is would say. The results were that the “transaction_id” that was being echoed out was CORRECT. It was not the repeating “4294967295”. However, when I look in my database, it shows “4294967295”.

2) This time I decided to echo out the query to my web browser. The query was CORRECT. In the query, the CORRECT “transaction_id” was in the query. However, when I look in my database, it shows “4294967295”.

I have 3 different pages where customers can make payments. ALL 3 pages started doing this on April 6th, 2012. None of those pages were modified at all. I have not modified those pages in over 2 years. Any help is greatly appreciated!

$query = "INSERT INTO payments (customer_id, transaction_id, invoice_number, authorization_code, subscription, subscription_id, avs_result, cvv_result, amount, full_tuition, payment_date, ip_address) VALUES ({$_SESSION['customer_id']}, {$_SESSION['transaction_id']}, {$_SESSION['invoice_number']}, '{$_SESSION['authorization_code']}', '{$_SESSION['subscription']}', {$_SESSION['subscription_id']}, '{$_SESSION['avs_result']}', '{$_SESSION['cvv_result']}', {$_SESSION['amount']}, {$_SESSION['full_tuition']}, '{$_SESSION['payment_date']}', '{$_SESSION['ip_address']}')" ;
$result = mysqli_query($dbc, $query) OR die ('<p>There was an error with the INSERT payments query.: ' . mysqli_error($dbc) . '<br />Query:' . $query . '</p>') ;

echo '<p>Transaction ' .  $_SESSION['transaction_id'] . ' has been <font color="green">APPROVED</font> by the system.</p>' ;

echo '<br /><br />' ;

echo '<p>Below is a summary:</p>' ;
echo '<p>Transaction ID: ' .  $_SESSION['transaction_id'] . '<br />
Payment Method: XXXX<br />
Amount: $' . $amount . '<br />
Customer Name: ' . $_SESSION['first_name'] . ' ' . $_SESSION['last_name'] . '<br />
</p>' ;

echo "<p>Note: Please do NOT click the browser's Back button to enter a new transaction.</p>" ;

echo $query ;

Answer by user282172

Your number is larger than the field in the DB can handle…

4294967295 is the largers number 32 bits can hold, your transaction ID is now larger than the numerical field your DB can hold.

Answer by Starx

Change the data type of the transaction_id to BIGINT

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+


Please fill the form - I will response as fast as I can!