2

I'm trying to enter a phone number into my database, the column is set as varchar (15) and inside phpMyAdmin it will accept a phone number with a leading 0 (07712123123 for example).

In my PHP script I have the variable $contactNo which is a string containing 07712123123

But when I use my prepared statement to input the data into the database the leading zero is removed. How can I stop this?

// Insert data into customer_accounts
if($stmt = $mysqli -> prepare("INSERT INTO customer_accounts (username, password, firstname, lastname, email, number) VALUES (?,?,?,?,?,?)")) {
    $stmt -> bind_param("ssssss", $username, $password, $firstname, $lastname, $emailAddress, $contactNo);
    $stmt -> execute();
    $stmt -> close();
}

I have echo'd out the variable before and after the statement and the 0 is there so it must be removed as its put into the database. I'm new to prepared statements and I can't figure out what to do to fix it.

Dharman
  • 30,962
  • 25
  • 85
  • 135
TomFrom
  • 89
  • 1
  • 3
  • 9

1 Answers1

1

The prepared statement you provide in your question has no flow, so I am guessing there is a silent conversion of datatype.

Double-check that column number is indeed of type VARCHAR. I am pretty sure you have an INT (or similar) type there instead.

Panagiotis Moustafellos
  • 973
  • 1
  • 8
  • 18
  • The number column is definately a varchar(15). and ive checked the variable with is_int() and its also definetley not an int. How can i improve the flow of the statement? thanks for the help – TomFrom Mar 25 '13 at 21:58
  • If you are making the assignment in your code before the statment as `$contactNo=07712123123;` then PHP assumes it's of octal type, then converted to int internally before submitted as string with to the prepared statement. Check if that is the case. (However you should not be seeing only a leading zero being removed, you should be seeing a whole 'different' number, the decimal representation of the octal). Try handling the `$contactNo` variable as string in the code before passing it to the statement. – Panagiotis Moustafellos Mar 25 '13 at 22:37
  • The variable is set from a form using $_POST['contact-no'], this is then sanitized and then ive checked is_numeric which is true, so the variable needs to be converted to a string and both $var = "$var" & $var = strval($var) but it still strips the leading zero. i think its something to do with mysqli_stmt_bind_param($stmt, 'ssssss', if i set the column to decimal 11,0 in the database and use 'sssssd' it still strips the 0. – TomFrom Mar 26 '13 at 03:09
  • If `is_numeric` returns true then the leading zero will be removed, also the decimal `,0` that you place. `is_numeric` will also return true if its octal as I wrote above (i.e. 021231313 is octal and will return true). So you need to completely treat `$_POST['contact-no']` as a string, if you want it only have numbers and strip out the rest symbols use a regular expression. – Panagiotis Moustafellos Mar 26 '13 at 13:51
  • Example usage of `preg_replace` with regular expression to keep only numbers in a string: `$contactNo = preg_replace( '/[^0-9]/', '', $_POST['contact-no' );` – Panagiotis Moustafellos Mar 26 '13 at 14:00
  • Ive just tried your example: `$contactNo = preg_replace( '/[^0-9]/', '', $_POST['contact-no' );` and it is still returning as numeric. testing with `if (is_numeric($contactNo)) {echo "'{$element}' is numeric", PHP_EOL;} else {echo "'{$element}' is NOT numeric", PHP_EOL;}` "'07801555555' is numeric" – TomFrom Mar 26 '13 at 19:14
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26972/discussion-between-tomfrom-and-panagiotis-m) – TomFrom Mar 26 '13 at 20:49