html registration form insertion into a database error

Question

I have a registration form in html which I have validated using javascript. The problem is when I enter into my database table it shows an error for the phone no field which is a primary key in the table.The problem is although each time I try entering different values for the primary key field it shows error as in given below example:

INSERT INTO user_details(Name,Phone_No,Date_Of_Birth,Gender,Email,Password) VALUES ('XYZ','6734746586','2010-10-29','Female','xyz@gmail.com','123456')

Duplicate entry '2147483647' for key 'Phone_No'

As you can see here that I have entered 6734746586 for phone no. column but the error is showing for 2147483647. How is it possible?

The registration.html form is given below:

<div id="id02" class="modal">
<form name="register" class="modal-content animate" action="register.php" method="post" onSubmit="return validate();">
    <div class="imgcontainer">
        <span onclick="document.getElementById('id02').style.display='none'" class="close" title="Close Modal">&times;</span>
    </div>
    <div class="container">
        <label><b>Name:</b></label>
        <input type="text" placeholder="Enter Name" name="name" id="name" required>   
        <label><b>Phone No.:</b></label>
        <input type="text" placeholder="Enter Phone number" name="phone" id="phone" maxlength="10" required>    
        <label><b>Date of Birth:</b></label>
        <input type="date" placeholder="Enter Date of Birth" name="dob" id="dob" required>
        <label><b>Gender:</b></label>
        <table width='100%'>
        <tr>
        <td><input type="radio" name="gender" id="gender" value="Male" checked> Male </td>
        <td><input type="radio" name="gender" id="gender" value="Female"> Female </td>
        </table>
        <label><b>E-mail:</b></label>
        <input type="text" placeholder="Enter Email Id" name="email" id="email" required>
        <label><b>Password:</b></label>
        <input type="password" placeholder="Enter Password" name="psw" id="psw" required>
        <label><b>Repeat Password:</b></label>
        <input type="password" placeholder="Repeat Password" name="psw-repeat" id="psw-repeat" required>            
        <input type="checkbox" checked="checked"> Remember me
        <p>By creating an account you agree to our <a href="#">Terms & Privacy</a>.</p>
        <div class="clearfix">      
            <button type="submit" class="register" id="register"> Register </button>
            <button type="button" onclick="document.getElementById('id02').style.display='none'" class="cancelbtn">Cancel</button>     
        </div>
    </div>
</form>
<script>
    // Get the modal
    var modal = document.getElementById('id02');
    // When the user clicks anywhere outside of the modal, close it
    window.onclick = function(event) 
    {
        if (event.target == modal) 
        {
            modal.style.display = "none";
        }
    }
</script>       

The javascript used to validate the form is as given below:

function validate() 

{

    var val=document.getElementById('phone').value;

    if (!(/^\d{10}$/.test(val)) )  // Here \d means "digit," and {10} means "ten times." The ^ and $ anchor it to the start and end, so something like asdf1234567890asdf does not match.
    {
        alert("Invalid phone number. It must be of 10 digits only");

        phone.focus();

        return false;

    }

    var psw=document.getElementById('psw').value;

    var psw_repeat=document.getElementById('psw-repeat').value;

    if(!(psw==psw_repeat))

    {
        alert ("Password & Repeat Password fields must be same");

        return false;

    }   

    return true;

}

The php form for database connectivity is as follows:

    <?php
$name=$_POST["name"];

$phone=$_POST["phone"];

$dob=$_POST["dob"];

$gender=$_POST["gender"];

$email=$_POST["email"];

$psw=$_POST["psw"];


$dbhost = "localhost:3306";

$dbuser = "root"; 

$dbpass = ""; 

$dbname="hotel";

//create connection

$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

//check connection 

if( $conn->connect_error ) 

{ 

    die("Connection failed:" . $conn->connect_error); 

}


$sql="INSERT INTO user_details(Name,Phone_No,Date_Of_Birth,Gender,Email,Password) VALUES ('$name','$phone','$dob','$gender','$email','$psw')";

if( $conn->query($sql)== TRUE ) 

{ 

    header('Location: index.php');  

} 

else

{

    echo "Error:".$sql."<br>".$conn->error;

}

$conn->close();

?>

Answer 1

Phone numbers aren't integers, they're strings. You created your column as an integer and the phone number is larger than that data type allows for so it truncates the value to the largest value it can hold which is 2147483647 on a 32-bit system. Apparently you also set it to have a unique index so you obviously encountered a truncated number previously and didn't catch your error and now you can't insert another row with that same value. Change that column to be a char or varchar and it will work (assuming you do not try to insert the same phone number twice as the unique index you have will throw the same error if you do).

ALTER TABLE user_details MODIFY Phone_No CHAR(10);

FYI, your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared parameterized statements.