Authorize Id # Changing On Db Insert

[David L]

My transactions table has a authorize_id BIGINT(20) UNSIGNED NOT NULL column as specified in the book.

 

However the ID numbers being returned by Authorize change upon the DB insert because it is being inserted as an integer.

 

For example, Authorize ID returns transaction number 2161546092. When it is inserted into the database, it changes to 2147483647. (In PHP, doing echo intval('2161546092') also produces 2147483647).

 

How do I stop this happening?

[Stuart]

Check the PHP manual and you'll see the root of your problem at least with respect to intval:

 

The maximum value depends on the system. 32 bit systems have a maximum signed integer range of -2147483648 to 2147483647. So for example on such a system, intval('1000000000000') will return 2147483647. The maximum signed integer value for 64 bit systems is 9223372036854775807.

 

That said the max value for a bigint field should be -9223372036854775808 to 9223372036854775807 OR 0 to 18446744073709551615 which shouldn't cause you any problems. I'd first of suggest double checking your column definition and which OS you're server is running. (This last part I'm kind of winging I dont understand 32/64 in any detail - but should put you on the right track).

[David L]

I now see that this problem only occurs when using prepared statements. If I run a normal query without a prepared statement, like:

 

"INSERT INTO transactions (order_id, trans_type, trans_amount, response_code, response_reason, authorize_id, response, trans_date) VALUES (100,'AUTH_CAPTURE','100',1,'This transaction has been approved.',2161546092,'response string here',NOW())"

 

then I have no problems and the transaction id remains 2161546092.

 

Anyone knows how to make this work with prepared statements too?

[Larry]

I suspect the problem is that your prepared statement is not using the proper identifier for the data type.

[David L]

I was using i for integer - is that incorrect?

[Larry]

In theory that's correct, except that forces PHP to typecast the value to a PHP integer and a PHP integer may be smaller than the actual number, as you're seeing. If you use "s", for string, no PHP conversion will occur and the original value will be maintained. What you're doing would seem to be appropriate, but there are behind-the-scenese complications.