PHP value incorrect in MySQL table, but correct in echo

[Craig78]

I am a super noob with PHP and MySQL, my experience is HTML and CSS but I am trying to improve my skillset and have begun a small project to learn the ropes of PHP and MySQL. I'm not familiar with much of the terminology yet so apologize if this post is in any way unclear.

In my project I am currently trying to get user information Inserted into a MySQL database I created. I have had most of the fields being input correctly until tonight when I added 3 File Upload fields. These 3 fields are working from my form to my database (ie filenmes are being inserted properly), but now my telephone form field input is not being inserted correctly. The data echos as expected, but is written to the data base as a different phone number each time. The different value is the same each time, but NOT what is input into the form. I am assuming I have a value stated explicitly somewhere, but can not find it. Also, when I echo the php variable $phone, the correct user-input value is shown. It's driving me crazy b/c it is probably something silly I am overlooking.

Here is my code for the php form file:

if (isset($_POST['register_submit'])) {
                            // Variables
                            $first_name = $_POST['register_firstname'];
                            $last_name = $_POST['register_lastname'];
                            $gameranger = $_POST['register_gameranger'];
                            $email = $_POST['register_email'];
                            $password = $_POST['register_password']; 
                            $password_confirm = $_POST['register_password_confirm'];
                            $phone = $_POST['register_phone'];
                            $civ = $_POST['register_civ'];
                            $map = $_POST['register_map'];
                            $color = $_POST['register_color'];
                            $motto = $_POST['register_motto'];
                            $crest = $_FILES['register_crest'] ['name'];
                            $player_image = $_FILES['register_image'] ['name']; 
                            $sound = $_FILES['register_sound'] ['name'];  
                            $rules = $_POST['register_rules'];
                            $output_form = false; 
                        
                            // Validation checks here

if ((!empty($first_name)) && (!empty($last_name)) && (!empty($gameranger)) && (!empty($email)) && (!empty($password)) && (!empty($password_confirm)) && (!empty($phone)) && (!empty($civ)) && (!empty($map)) && (!empty($color)) && (!empty($motto)) && (!empty($rules))) {
                                // Connect to database and insert player information
                                $dbc = mysqli_connect('localhost', 'yourpass', 'Colin$2009', 'yourpass_aoe')
                                    or die('Could not connect to database.');
                                $query = "INSERT INTO players (first_name, last_name, gameranger, email, phone, password, civ, color, map, motto, crest, sound, accept_rules, player_image)" .
                                    "VALUES ('$first_name', '$last_name', '$gameranger', '$email', '$phone', SHA('$password'), '$civ', '$color', '$map', '$motto', '$crest', '$sound', '$rules', '$player_image')";
                                $result = mysqli_query($dbc, $query)
                                    or die('Error querying database.');
                                mysqli_close($dbc);
                            }
                        }
                        else {
                            $output_form = true;
                        }
                    if ($output_form) { 

<form enctype="multipart/form-data" id="register" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
							<input type="hidden" name="MAX_FILE_SIZE" value="2000000" /> <!-- Max file size = 2 megs -->
							<fieldset>
								<ul>
									<li class="required">
										<label for="register_firstname">First Name:</label>
										<input type="text" class="text" name="register_firstname" id="register_firstname" value="<?php echo $first_name ?>" />
										<strong>Required</strong>
<!-- rest of form here -->
<li class="required">
										<label for="register_phone">Phone:</label>
										<input type="text" class="text" name="register_phone" id="register_phone" value="<?php echo $phone ?>" />
										<strong>Required</strong>
									</li>

else {
                        echo '<p>Welcome to the AOE Tournament of Conquerors, Lord ' . $last_name . '. You must be a brave and noble sovereign indeed to lead the mighty ' . $color . ' armies of the ' . $civ . ' into battle against your enemies across the lands of the ' . $map . '.</p>';
                        echo '<p>Should your opponents need to send you their intent to do battle tthey will do so either by sending emissaries to your castle\'s address or a messenger pigeon to your Chancellor at ' . $phone . '. You are bound by tournament rules to peacefully accept these emissaries and respond in kind.</p>'; 
                        echo '<p>To enter the tournament grounds, you will need to provide the guards at the gate of the King\'s Castle with your castle\'s address and password. We have recorded your castle\'s address as <strong>' . $email . '</strong> and your password as <strong>' . $password . '</strong>.</p>';
                        echo '<p>As King of Niagara Thistle and the promoter of this Tournament of Conquerors, I wish you and your brave armies good luck and Godspeed!</p>';
                        echo '<p>As they say in your lands: ' . $motto . '!</p>';
                        echo '<div class="content"><a class="button" href="user_profile.php" rel="" title="Continue to your Lord\'s chambers.">Enter Tournament</a></div>';
                    } 

Any help in figuring out why the User Phone number echos correctly in the form and also but is not being inserted correctly into the database would be great. Again, a specific 10-digit number IS being inserted each time I create a user and is the same each time, but it is NOT the user input value.

[bradgrafelman]

First and foremost, you should never place user-supplied data directly into a SQL query, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. See the manual page security.database.sql-injection for an introduction to the former.

Next, what column type is this `phone` column in your DB? Have you tried echo'ing out the SQL query to examine it before it gets executed - does it contain the data you expected?

[Weedpacket]

What is the incorrect value that keeps coming back? (To expand on bradgrafelman's second paragraph, if you're using the wrong type to store the number MySQL will happily mangle the number to make it fit: if I recall, MySQL assumes - unless you tell it otherwise - that you'd rather have invalid data being stored than be told that it's invalid.)

[Craig78]

@bradgrafelman: I have the phone column set to INT to take phone numbers. All input values so far have been integers only (no dashes, other characters).

@weedpacket: the value that is getting written to the database then spit out when I do a SELECT * is "2147483647" (quotes added by me). This value comes back EVERYTIME from the database no matter what value I enter for a phone number. BUT when the app echos the inout variable on a conirmation message I created, it echos the correct users value. My thought is the problem lies in my INSERT statement somehow but not sure what I am doing wrong.

[Weedpacket]

You're using the wrong data type to store phone numbers; 2147483647 is the largest number that can be stored in a 32-bit signed integer - try to store anything larger and it seems MySQL reckons it's close enough to what you asked for (the correct behaviour would be to fail).

Since you're not planning on doing any arithmetic with phone numbers (what would it even mean to add two phone numbers together?) it would be safer to store them as character data (that's all they are after all - it's just that the characters used happen to be limited to "0123456789").

And fix the security hazards bradgrafelman mentioned

[Craig78]

Thanks guys! Problem resolved! I changed "phone" data type to "VARCHAR" instead of "INT" and it works!

Silly question but why didn't INT work? I mean isn't a phone number technically a number which is technically an INTEGER? I know no arithmatic would be used with the phone number, but isn't it still a number as far as the database is concerned or no?

Thanks guys.

[bradgrafelman]

Silly question but why didn't INT work?

Read over Weedpacket's post again - he explained why many phone numbers wouldn't be able to be stored in "INT" columns due to the maximum value restriction (2^31 - 1 for a signed int, 2^32 - 1 for a signed one).

I mean isn't a phone number technically a number

Not necessarily. Just because a phone number is a piece of data which is comprised of a series of digits doesn't mean you have to consider it as a number of any sort. You'd never do any arithmetic on a phone number, nor would you probably ever use them in an inequality. Thus, it makes more sense to treat it as a VARCHAR (e.g. a collection of characters, which happen to be digits assuming you remove any separators) than any sort of numeric type.

...which is technically an INTEGER?

Careful there; declaring a column of type "INT" is really saying "a 32-bit signed integer." Weedpacket pointed this out when he referenced the maximum value such an integer can hold. In other words, if your phone number was 555-555-5555, you would be unable to store it in your database since the integer 5,555,555,555 can not be represented using a 32-bit signed integer.

I know no arithmatic would be used with the phone number, but isn't it still a number as far as the database is concerned or no?

It certainly is "still a number" - one that the database can't store due to size constraints you've placed on the column by declaring it as an "INT".

[Ashley Sheridan]

A phone number isn't technically a valid integar, despite its name. That is because a valid phone number can contain brackets, the plus sign, spaces, and leading zeros (which get stripped when cast as an int). Same deal with things like ISBN, although it has the word 'number' in the name, you try storing 978-1-4493-2285-4 in an int field and you'll likely get an assortment of different values depending on what you use to insert the data.

[Derokorian]

Also, if you are storing phone numbers as a string you can accommodate full contact numbers such as those that have an extension.

[johanafm]

try to store anything larger and it seems MySQL reckons it's close enough to what you asked for (the correct behaviour would be to fail).

Correct behaviour is a good thing. The SQL standard defines correct behaviour for good reasons. In MySQL you can get this behaviour by specifying server mode in mysql's config file (my.conf iirc). See http://dev.mysql.com/doc/refman/5.6/...-sql-mode.html for info on how to do this and available modes.

For this particular issue, you should set STRICT_TRANSTABLES (for inno db) and STRICT_ALL_TABLES (for all/other? engines). Best thing to do though, is to use server mode TRADITIONAL since it is equivalent to

STRICT_TRANS_TABLES
STRICT_ALL_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION

Also add ANSI while you're at it.