PHP value incorrect in MySQL table, but correct in echo
Results 1 to 10 of 10

Thread: PHP value incorrect in MySQL table, but correct in echo

  1. #1
    Junior Member
    Join Date
    Dec 2012
    Posts
    3

    PHP value incorrect in MySQL table, but correct in echo

    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:

    PHP Code:
    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) { 
    HTML Code:
    <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>
    PHP Code:
    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.

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,395
    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?

  3. #3
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,843
    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.)
    Last edited by Weedpacket; 12-29-2012 at 07:22 PM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  4. #4
    Junior Member
    Join Date
    Dec 2012
    Posts
    3
    @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.

  5. #5
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,843
    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
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  6. #6
    Junior Member
    Join Date
    Dec 2012
    Posts
    3

    resolved

    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.

  7. #7
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,395
    Quote Originally Posted by Craig78 View Post
    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).

    Quote Originally Posted by Craig78 View Post
    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.

    Quote Originally Posted by Craig78 View Post
    ...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.

    Quote Originally Posted by Craig78 View Post
    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".

  8. #8
    Senior Member
    Join Date
    Aug 2008
    Location
    London, UK
    Posts
    753
    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.
    Ashley Sheridan
    www.ashleysheridan.co.uk

  9. #9
    Senior Member Derokorian's Avatar
    Join Date
    Apr 2011
    Location
    Denver
    Posts
    1,767
    Also, if you are storing phone numbers as a string you can accommodate full contact numbers such as those that have an extension.
    Sadly, nobody codes for anyone on this forum. People taste your dishes and tell you what is missing, but they don't cook for you. ~anoopmail
    I'd rather be a comma, then a full stop.
    User Authentication in PHP with MySQLi - Don't forget to mark threads resolved - MySQL(i) warning

  10. #10
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,637
    Quote Originally Posted by Weedpacket View Post
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •