CODEWITHSUNDEEP
phpmysql
ddonk
ddonk

Reputation: 3

Numbers Are Being Reformatted In MySQL

I have been having issues with MySQL changing the values of numerical data. It happens upon form submission and when outputting to Excel. ID numbers like this 911001001235848 are coming out like this 9.1100100123585E+14. Any suggestions as to what is going on?

Upvotes: 0

Views: 54

Answers (2)

ddonk
ddonk

Reputation: 3

The user inputs the being and last of a sequence and the script fills the numbers in between. This is the PHP script I am using to input the numbers into MySQL and it only inputs the first number then the messed up number second. Small numbers work great but I need to be able to input 15 digit numbers.

$date  = date("m-d-Y");
$nstart=$_REQUEST['startid'];
$nend = $_REQUEST['endid'];
$doc = $_REQUEST['doc'];
$phone = $_REQUEST['phone'];


for ($i = $nstart; $i <= $nend; $i++) 
{ 
$query = "INSERT INTO petdb (doc,phone, userid, reg_date) VALUES ('$doc','$phone','     $i', '$date')"; 
mysql_query($query, $con); 
} 
mysql_close();

Upvotes: 0

Timothy Allyn Drake
Timothy Allyn Drake

Reputation: 926

According to Microsoft's documentation, you should ensure numeric values consisting of 12 or more digits are of type string and prefixed with an apostrophe to retain the original value. Below is an example of the above mentioned, followed by its supporting documentation.

"personId","personName","randomNumber"
1,"John Doe","'911001001235848"
2,"Jane Doe","'848532100100911"

Notice that the "randomNumber" column values are double quoted and prefixed with an apostrophe.

Text or number converted to unintended number format in Excel

Upvotes: 3

Related Questions