INT(16) Issue 1

[sipps]

Hi all,

I am creating a prototype site, using PHP/MySQL. It's almost finished, but I'm getting a funny output from MySQL. I insert a credit card number into the database, into a Credit_Card_Number INT(16) column. As I understand it, up to 16 digits can be inserted into this field.

I have run through a process of inserting many numbers into the field, and when I bring back all the numbers that are in that column for every record, any that are 11 digits long or over, always end up as the same number - 2147483647. Can someone tell me if I ahve done somethign wrong by setting up INT(16)?

Thank you

sipps

 

[swampBoogie]

16 does not signify the number of digits. It is just a displaylength used by some tools. The values that can be stored is still 4-byte integer values. The whole idea is very confusing.

You can use a BIGINT instead which uses 8 bytes and which is sufficient to hold 16 digits.

 

[sipps]

Sorry, I didn't mean to confuse, all I want to do is store 16 digits, should I use an ALTER TABLE statement to change the column to BIGINT? Thanks

 

[swampBoogie]

Yes, alter table should be okay. I did not mean that your post was confusing, I rather meant the implementation in Mysql. Especially since you don't get any error when inserting an invalid value.

 

[sipps]

I altered the table so that the column was now BIGINT, and I tried to insert a number longer that 10 digits but it still displays the wrong number. No error message appears when a number is inserted through the web site but when I look back at what was inserted, it is always the same 10 digit number. This is very strange, it seems that it is acting like a normal INT column which can hold from -2147483648 > 2147483648. This is the number that is displayed when I view all the credit card values in the database.

Any ideas?

Thanks

sipps

 

[sipps]

It gets stranger, if I use an UPDATE statement on a booking that holds the credit card number that was there already, I can make the number bigger, i.e. 1231231231231231. Do you think that it might be to do with sessions within PHP? Can a session only hold up to a twn digit number? I don't think it is this, considering the value I always get back from the db is the biggest number you can get using INT, 2147483648.

?!?

 

[sleipnir214]

PHP sessions can hold enormous amounts of data. Much more than 10 digits.


Anyway, I recommend against storing a credit-card number in a numerical field anyway. It's actually a string that happens to contain only numerical characters.

The operative question I ask when deciding on storage for data like a credit-card number (or a status code like "1.1&quot is: Am I ever going to perform explicit or implicit mathematical operations between to pieces of data of this type?

In the case of a credit-card number, no. You are never going, for example to add two credit-card numbers together to generate a third.
Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[sipps]

Well I agree with you sleipnir214,

I changed the column to VARCHAR(16), but weirdly enough, I still get the same problem, if I enter any digits over the length of 10, then I always get back the number 2147483648. I have enven changed the column to VARCHAR(64), but still it brings back the wrong number.

I cannot see what is going wrong here, I can UPDATE that column and put a longer number in, but the INSERT statement seems to have problems somewhere!!!

 

[sleipnir214]

Did the ALTER TABLE take? What does &quot;DESCRIBE <tablename>&quot; say? Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[sipps]

Yes the ALTER statement took:

DESCRIBE booking;

Field Type

Credit_Card_Number VARCHAR(64)

It is strange no?!

I am running more tests through PHP and Access. I have set up an ODBC connection through Access so I can insert data from there as well, I still get the same problem, it just wont let me insert anything bigger!

Cheers

sipps

 

[JustKIDn]

See, this is why I like tools like MySQL-CC.

With a GUI tool you can 'visually see' what's in there.

This helps cut down on the guessing game a lot.

just my .02 cents.
tgus

____________________________
Families can be together forever...

 

[sleipnir214]

I do know that if you are using a linked table in Access, you must refresh Access' table links when you update the table's structure. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[strella]

Hello tgus,

MySQL Newbie here. Please say more about MySQL-CC GUI. I googled it and got only information about mysql.cc.

Thanks

 

[sleipnir214]

strella:

I think your question would have a wider audience if you posted it as a new thread. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[QatQat]

Tgus,

are you somehow involved in the development of MySQL-CC or do you get something out of it 'cause you continuosly suggest anyone to use it.

Just kidding...

Anyway, now I am serious, MySQL CC is extremely limited in its features and although discontinued, MySQL front looks much more complete.
However, it is a bad lazy way of dealing with Mysql as the command IS MYSQL.


Bye


Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)