mySQL removes first digit

Question

Hi guys, I am inputting data into a mySQL database via a PHP script, but for some reason when I check the database, all of the phone numbers have their first digit removed, like so, 0123456789 shows up as 123456789 in the database, but if I change the data type from INT to TEXT, it shows correctly, I am very hesitant to keep it as TEXT though, as I am sure this will cause complications further down the road as the database app starts to become more complicated, here is the PHP code.

    <?php


$gender = $_POST['gender']; 

$first_name = $_POST['first_name']; 

$second_name = $_POST['second_name']; 

$id_number = $_POST['id_number']; 

$home_number = $_POST['home_number']; 

$cell_work = $_POST['cell_work']; 

$email_address = $_POST['email_address']; 


        $curDate = date("Y-m-d");

        mysql_connect ("server", "user", "pass") or die ('Error: ' . mysql_error());
        mysql_select_db ("database");

        $query = "INSERT INTO table (id,gender,first_name,second_name,id_number,home_number,cell_work,email_address,date) VALUES('NULL','".$gender."','".$first_name."','".$second_name."','".$id_number."','".$home_number."','".$cell_work."','".$email_address."','".$curDate."' )";

        mysql_query($query) or die (mysql_error());

        ?>

Thanx in advance!

Answer 1

you will need to store the number as a CHAR, VARCHAR or TEXT. CHAR or VARCHAR are recommended since TEXT is for very long strings and isn't as efficient. use CHAR if all numbers are always the same length or VARCHAR otherwise.

the reason you can't store that as an INT is that the number 0123456789 is actually 123456789 (assuming an initial 0 doesn't mean octal :P). the strings are different but the numbers are the same. for your purposes the phone number needs to be a string.

Answer 2

That's because 0000001 is just 1...

Take a look at this post, it may help you a lot, I found it via this Google search.

Answer 3

Define the column using ZEROFILL:

ALTER TABLE `sweet_table` CHANGE `id` `id` INT( 9 ) UNSIGNED ZEROFILL NOT NULL

That will pad the left side of the number with zeroes.