CODEWITHSUNDEEP
mysql
user195257
user195257

Reputation: 3316

Number in PHP form being saved as a random number in mySQL DB

I have a form to add/edit a record in a mysql database. However when entering a phone number, such as '07854301812', '2147483647' is saved in the database. I have echoed out the $number variable, and this is the correct value, so something is going wrong when storing in the database. 

$sql = mysql_query("UPDATE `customer` SET `f_name` = '$fname',`l_name` = '$lname',`email` = '$email',`number` = '$number' WHERE `c_id` = '$id'");

and this is my sql DDL

CREATE TABLE Customer(
    c_id        INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    f_name      VARCHAR(50) NOT NULL,
    l_name      VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL,
    number      INTEGER,
    date_joined DATE NOT NULL CHECK (date_added <= now())
);

Upvotes: 0

Views: 330

Answers (2)

technocrat
technocrat

Reputation: 3705

I agree, looks like the '2147483647' is the max positive value for an INT.

Some info on integer types (and sizes): http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

You can quickly change the schema to use a larger number like the following:

mysql> ALTER TABLE Customer MODIFY number BIGINT;

Some more info on the Alter syntax: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

So long as you go from a smaller number to larger number you won't lose any information in your table.

You should be able to test pretty quickly with that and see if it works.

Upvotes: 1

CanSpice
CanSpice

Reputation: 35818

Your number is larger than the largest integer you can store. Given your numbers start with a 0 that'll get truncated if you switch to using a BIGINT, you should probably just store them as a VARCHAR.

Upvotes: 1

Related Questions