Okay, so here is as vague of a question as I can come up with…
What are some common things that would cause my PHP and MySQL code to fail during and INSERT??
I am following an example in a book, and this code was working just fine a couple of weeks ago, but when I ran it tonight it fails?! :-/
The way the code is written, when the INSERT fails, it throws a generic error, but doesn’t really tell you WHY it failed.
I stepped through my code, but can’t figure out what is wrong.
Because this is a Registration application, it is pretty hard to include all of my code here since it goes across several files.
Here is the Registration file…
<?php
// Get Configuration settings.
require('includes/config.inc.php');
// Set page title.
$page_title = 'Register';
// Get Header file.
require('includes/header.html');
// Get Database Connection.
require(MYSQL);
// Create empty array for storing errors.
$reg_errors = array();
// Check if form submitted.
//
// The first time the User goes to the form it will use a GET request,
// so the code that follows will not apply.
// When the User clicks submit, a POST request will be made,
// and the code below will apply.
if ($_SERVER['REQUEST_METHOD'] == 'POST'){
// Check for First Name.
// Allow letters, space, apostrophe, period, and hyphen. 2-20 characters.
if (preg_match ('/^[A-Z \\'.-]{2,20}$/i', $_POST['first_name'])) {
$fn = mysqli_real_escape_string($dbc, $_POST['first_name']);
} else {
// Add error-message to array.
$reg_errors['first_name'] = 'Please enter your first name!';
}
// Check for Last Name.
// Allow letters, space, apostrophe, period, and hyphen. 2-40 characters.
if (preg_match('/^[A-Z\\'.-]{2,40}$/i', $_POST['last_name'])){
$ln = mysqli_real_escape_string($dbc, $_POST['last_name']);
} else {
// Add error-message to array.
$reg_errors['last_name'] = 'Please enter your last name!';
}
// Check for Username.
// Allow letters and numbers. 2-30 characters.
if (preg_match('/^[A-Z0-9]{2,30}$/i', $_POST['username'])){
$u = mysqli_real_escape_string($dbc, $_POST['username']);
} else {
// Add error-message to array.
$reg_errors['username'] = 'Please enter a desired name!';
}
// Check for Email Address.
// if (filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)){
$e = mysqli_real_escape_string($dbc, $_POST['email']);
// } else {
// Add error-message to array.
// $reg_errors['username'] = 'Please enter a valid email address!';
// }
// Check for Password.
// Password must contain at least one Uppercase, one Lowercase,
// and one Number. It must be between 6 and 20 characters in lenth.
// if (preg_match('/^(\\w*(?=\\w*\\d)(?=\\w*[a-z])(?=\\w*[A-Z])\\w*){6,20}$/', $_POST['pass1'])){
if ($_POST['pass1'] == $_POST['pass2']){
$p = mysqli_real_escape_string($dbc, $_POST['pass1']);
// } else {
// Add error-message to array.
// $reg_errors['pass2'] = 'Your password did not match the confirmed password!';
}
// } else {
// $reg_errors['pass1'] = 'Please enter a valid password!';
// }
// Check for form errors.
if (empty($reg_errors)){
// No form errors.
// Check if Email and Username are available.
$q = "SELECT email, username
FROM users
WHERE email = '$e' OR username = '$u'";
$r = mysqli_query($dbc, $q);
$rows = mysqli_num_rows($r);
if ($rows == 0){
// No records found.
// Email and Username are available.
// Build query.
$q2 = "INSERT INTO users(username, email, pass, first_name, last_name, date_expires)
VALUES('$u', '$e', '" . get_password_hash($p)
. "', '$fn', '$ln', SUBDATE(NOW(), INTERVAL 1 DAY))";
// Add User to the database.
$r2 = mysqli_query($dbc, $q);
// The user's type does not need to be set because if no value is
// provided for an ENUM column, then the first enumerated value
// (i.e. "member") will be used.
// As a security measure, the User-Type can only ever be "member",
// after going through Registration. To create an "administrator",
// this must be done manually in the back-end database.
// Check if User was added.
if (mysqli_affected_rows($dbc) == 1){
// Insert was successful.
// Get (auto_increment) ID generated during INSERT.
$uid = mysqli_insert_id($dbc);
// Assign the new User's ID to the Session so that it may be
// accessed when the User returns from PayPal.
$_SESSION['reg_user_id'] = $uid;
// Display thank you message.
echo '<h3>Thanks!</h3>
<p>Thank you for registering! To complete the process,
please now click the button below so that you may pay for
your site access via Pay Pal. The cost is US$10 per year
<strong>Note: When you complete your payment at PayPal,
please click the button to return to this site.</strong></p>';
// Add PayPal Payment Button.
echo '<form action="https://www.sandbox.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_s-xclick">
<input type="hidden" name="hosted_button_id" value="">
<input type="image" src="https://www.sandbox.paypal.com/en_US/i/btn/btn_subscribeCC_LG.gif" border="0" name="submit" alt="PayPal - The safer, easier way to pay online!">
<img alt="" border="0" src="https://www.sandbox.paypal.com/en_US/i/scr/pixel.gif" width="1" height="1">
</form>';
// Create email body.
$body = "Thank you for registering at <whatever site>. Blah blah blah.\
\
";
// Send registration confirmation email to User.
mail($_POST['email'], 'Registration Confirmation', $body,
'From: admin@example.com');
// Get Footer file.
include ('includes/footer.html');
// Stop page to prevent registration page from being shown again.
exit();
} else {
// Insert failed.
// There was either a database or query error.
// Generate an error (using custom error-handler).
trigger_error('You could not be registered due to a system error.
We apologize for any inconvenience.');
}
} else {
// Email or Username NOT available.
if ($rows == 2){
// BOTH Email and Username taken.
$reg_errors['email'] = 'This email address has already been registered.
If you have forgotten your password, use the link
at the right to have your password sent to you.';
$reg_errors['username'] = 'This username has already been registered.
Please try another.';
} else {
// One or both may be taken.
$row = mysqli_fetch_array($r, MYSQLI_NUM);
if(($row[0] == $_POST['email']) && ($row[1] == $_POST['username'])){
// Both match.
$reg_errors['email'] = 'This email address has already been registered.
If you have forgotten your password, use the link
at the right to have your password sent to you.';
$reg_errors['username'] = 'This username has already been registered
with this email address. If you have forgotten
your password, use the link at the right to have
your password sent to you.';
} elseif ($row[0] == $_POST['email']){
// Emails match.
$reg_errors['email'] = 'This email address has already been registered.
If you have forgotten your password, use the link
at the right to have your password sent to you.';
} elseif ($row[1] == $_POST['username']){
$reg_errors['username'] = 'This username has already been registered.
Please try another.';
}
}
} // END (Check if Email and Username available)
} // END (Check for form errors)
} // END (IF METHOD == POST)
// Get Form Functions.
require('includes/form_functions.inc.php');
?>
<h3>Register</h3>
<p>Access to the site's content is available to registered users at a cost
of $10.00 (US) per year. Use the form below to begin the registration
process. <strong>Note: All fields are required.</strong> After
completing this form, you'll be presented with the opportunity to
securely pay for your yearly subscription via
<a href="http://www.paypal.com">PayPal</a>.
</p>
<form action="register.php" method="post" accept-charset="utf-8" style="padding-left:100px">
<p>
<label for="first_name"><strong>First Name</strong></label><br />
<?php create_form_input('first_name', 'text', $reg_errors); ?>
</p>
<p>
<label for="last_name"><strong>Last Name</strong></label><br />
<?php create_form_input('last_name', 'text', $reg_errors); ?>
</p>
<p>
<label for="username"><strong>Desired Username</strong></label><br />
<?php create_form_input('username', 'text', $reg_errors); ?>
<small>Only letters and numbers are allowed.</small>
</p>
<p>
<label for="email"><strong>Email Address</strong></label><br />
<?php create_form_input('email', 'text', $reg_errors); ?>
</p>
<p>
<label for="pass1"><strong>Password</strong></label><br />
<?php create_form_input('pass1', 'password', $reg_errors); ?>
<small>Must be between 6 and 20 characters long, with at least
one lowercase letter, one uppercase letter, and one number.</small>
</p>
<p>
<label for="pass2"><strong>Confirm Password</strong></label><br />
<?php create_form_input('pass2', 'password', $reg_errors); ?>
</p>
<input type="submit" name="submit_button" class="formbutton" id="submit_button"
value="Next →" />
</form>
<?php
// Get Footer file.
include ('includes/footer.html');
?>
My code is failing here…
(How do you BOLD code in the Code tags above???)
// Check if User was added.
if (mysqli_affected_rows($dbc) == 1){
} else {
// Insert failed.
// There was either a database or query error.
// Generate an error (using custom error-handler).
trigger_error('You could not be registered due to a system error.
We apologize for any inconvenience.');
}
How exasperating?! :mad:
Debbie
P.S. I took the SQL from the error, and pasted it into phpMyAdmin and the INSERT worked, so this must be a PHP error…
INSERT INTO users(username, email, pass, first_name, last_name, date_expires)
VALUES('bb', 'bb@mail.com', '�]ҥlmQH��J1 ��(�~%�$�y=q#', 'bb', 'bb', SUBDATE(NOW(), INTERVAL 1 DAY))
