Checkout.Php In Second Example

[hbphoto]

Hi,

 

In the second example in this book, in the checkout.php, a phone number is checked as part of form submission.

 

I'm using the same syntax for the validation as well as the form field.

 

When I run my script, the phone number that I enter into the form, 222-333-4444, does not insert into the database table as 2223334444. Instead, the result that is being populated into my table for phone is 2147483647. I have no clue as to what is causing this.

 

In my table, the phone field is set as INT(10) NOT NULL just as in the book.

 

Can someone help?

 

Here's my code starting with the form field and then to validation and then to insert.

 

Form Field:

<label><strong>Phone:</strong></label><input type="text" name="phone" value="<?php if (isset($_POST['phone'])) echo $_POST['phone']; ?>" />

 

Form Validation:

// Check for a phone number:
// Strip out spaces, hyphens, and parentheses:
$phone = str_replace(array(' ', '-', '(', ')'), '', $_POST['phone']);
if (preg_match ('/^[0-9]{10}$/', $phone)) {
 $ph  = $phone;
} else {
 $add_client_errors['phone'] = 'Please enter your phone number!';
}

 

INSERT statement:

$pwd = get_password_hash($p);

 $q = "INSERT INTO client (first_name, last_name, address, city, state, zip, phone, email, pass, date_created)
	 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CURDATE() )";
 $stmt = mysqli_prepare($dbc, $q);
 mysqli_stmt_bind_param($stmt, 'sssssiiss', $fn, $ln, $sa, $c, $st, $z, $ph, $e, $pwd);
 mysqli_stmt_execute($stmt);

 

As I mentioned, I'm not sure why the phone number is being switched from what I enter to another number. This is happening to any number I enter into the form and generates the same result.

[rob]

You should really start by following normal query debugging steps: manually write out and execute the SQL directly on the MySQL database and see what happens.

 

[edit] I've assumed that you've checked both the values used and the generated query to ensure it is what you're expecting in your code.

 

Also are you making sure your $phone value is an integer before passing it to the query?

[Edward]

Why don't you insert the phone number into the database as a string, a VARCHAR SQL datatype rather than an integer.

[hbphoto]

Yes, I am doing debugging steps.

 

First, I echo'd the results of my form submission so I'm certain that what I typed into the form is being passed through the validation process.

 

Here is the code and the results:

// Check for a phone number:
// Strip out spaces, hyphens, and parentheses:
$phone = str_replace(array(' ', '-', '(', ')'), '', $_POST['phone']);
if (preg_match ('/^[0-9]{10}$/', $phone)) {
 $ph  = $phone;
} else {
 $add_client_errors['phone'] = 'Please enter your phone number!';
}

  echo $_POST['phone'];
  echo "<br />"; // creating a new line
  echo $phone;  
  echo "<br />"; // creating a new line  
  echo $ph;  
  echo "<br />"; // creating a new line

 

Results:

333-444-5555
3334445555
3334445555

---------------------------------------

Here is the code for my insert statement and the result of the numbers of rows which were inserted.

// Add the client to the database:

 $pwd = get_password_hash($p);

 $q = "INSERT INTO client (first_name, last_name, address, city, state, zip, phone, email, pass, date_created)
	 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, CURDATE() )";
 $stmt = mysqli_prepare($dbc, $q);
 mysqli_stmt_bind_param($stmt, 'sssssiiss', $fn, $ln, $sa, $c, $st, $z, $ph, $e, $pwd);
 mysqli_stmt_execute($stmt);

 printf("%d Row inserted.\n", mysqli_stmt_affected_rows($stmt))

 

Result:

1 Row inserted

 

-----------------------------------------------------------------------------

Finally, here is the code and results from a select statement from the table. You will see that the phone number that was entered into the table is not the same phone number as entered into the form.

 //Checking results inserted into table
 $q = "SELECT * FROM client WHERE email='$e'";
 $r = mysqli_query ($dbc, $q);

 // Prints insert results from table
    while($row = mysqli_fetch_assoc($r)){
 foreach($row as $cname => $cvalue){
 print "$cname: $cvalue\t";	 }
 print "\r\n"; }   

 

Results:

client_id: 22
first_name: Minnie
last_name: Mouse
address: 123 Minnie Lane
city: Ft. Myers
state: FL
zip: 30309
***  phone: 2147483647
email: minnie@mouse.com
pass: £9•Øq?ë%+uÞðÈ÷í÷áÖýŸ õ-]tÜX¥Vô4
user_level: 0
active:
date_created: 2012-08-29

 

I'm not sure what else to do to check my script.

[Edward]

Can you try type casting the $phone variable for example

 

$ph = (int) $phone;

 

Try that and see what you get now?

[rob]

I think you've hit a system maximum for INT (2147483647).

 

I'm going to hazard a guess and say your INT field is set to signed and that you're on a 32 bit system?

 

Change the field to BIGINT instead of INT and let me know if that works.

[Edward]

I reckon that is correct rob, the unsigned INT can only go to a max of 4,294,967,295, so if you had a phone number starting with 9 that datatype would fail. BIGINT is only 8 bytes so it works out better than using a CHAR(10) of 10 bytes.

[hbphoto]

_Hello:

 

_{Changing the INT to BIGINT generated the same result. I'm going to try CHAR.}

[hbphoto]

I changed my field to UNSIGNED while using BIGINT. The result was the same.

 

I then changed my field to CHAR. The result is now correct.

 

I'm at a loss as to why the INT wasn't working. The example in the book is using INT.

[rob]

Max value for specifying type i (for mysqli_stmt_bind_param) is 2147483647.

 

You're not using the code in the book, you've replaced the stored procedures with your own database queries.

[Antonio Conte]

Just a little trick I've learned in Java. If you don't need to work with numbers, use Strings as they tend to be more reliable. Using a char type might be a good enough solution here. If you can't find any problems saving them as char, I'd say stick to it.