Help with SQL query

[fallenapples]

How come I do not get the numbers I entered when I created the this table?

CREATE TABLE contacts
(
name CHAR(15),
phone_mobile INT(15),
phone_work INT(15),
email CHAR(15),
relation_id INT(15),

INSERT INTO contacts VALUES ('Robert',4167234839,9384534455,'robertad',1);

When I SELECT * FROM contacts, columns 2 and 3 show numbers 2147483647. All the other values are correct; why don't the numbers show correctly?

 

[carnageX]

The number 2,147,483,647 (or hexadecimal 7FFF,FFFF16) is the maximum positive value for a 32-bit signed binary integer in computing. It is therefore the maximum value for variables declared as integers (e.g., as int ) in many programming languages, and the maximum possible score, money, etc. for many video games.

Use BigInt
https://msdn.microsoft.com/en-us/library/ms187745.aspx

Edit, sorry, that's a link to T-SQL (Microsoft SQL) data types / syntax... you're probably using MySQL, so this link will be relevant to you:
https://dev.mysql.com/doc/refman/5.5/en/integer-types.html

 

[strollin]

A phone number is NOT really a number (the digits don't represent a numeric value) so it really should be declared as a char field.

 

[carnageX]

A phone number is NOT really a number (the digits don't represent a numeric value) so it really should be declared as a char field.

Good catch - I wasn't even paying attention to what he was trying to store other than he was overflowing his ints.

+1 to what strollin said - just make it a varchar(15).

 

[fallenapples]

When I used varchar it said column count doesn't match value count at row 1. The syntax was exactly the same. So I used char and it worked fine. But just for future reference can I use hyphens and the @ symbol when using char?

 

[Mischa]

When I used varchar it said column count doesn't match value count at row 1. The syntax was exactly the same. So I used char and it worked fine. But just for future reference can I use hyphens and the @ symbol when using char?

Yeah, char can contain special characters so - and @ are fine. Basically if information in a certain column is going to always be a fixed length use char. If the info entered will vary in size use varchar.

Edit: my original post was worded pretty poorly I think.

 

[carnageX]

I prefer varchar - mostly because I use T-SQL and not MySQL , but also because char pads inserted values with spaces if they're less than the length. Varchar doesn't pad with spaces. Though it doesn't really matter when retrieving the value, because the trailing spaces are truncated anyway.

More info on the differences here: https://dev.mysql.com/doc/refman/5.0/en/char.html

Your "Column count doesn't match value count at row 1" usually means you didn't have a matching number of insert values / columns in your INSERT statement.

 

[strollin]

If you use varchar then you would be able to handle phone numbers in different formats like:
1+(555)123-4567 x 123
5551234567
(555)1234567