Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
David L

Authorize Id # Changing On Db Insert

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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

  • Upvote 1

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...