With regards to this:
Now someone else told me this statement
$insert = mysql_query("INSERT INTO users VALUES ('NULL', '".$_POST['email']."', '".$_POST['password']."')")
or die("Could not insert data because ".mysql_error());
Should be this
$insert = mysql_query("INSERT INTO users (email, password) VALUES ('NULL', '".$_POST['email']."', '".$_POST['password']."')")
or die("Could not insert data because ".mysql_error());
They are incorrect, and whomever told you that needs to sit down and relax. The number of columns in an insert statement must have a counterpart in the values area (and vice-versa). So you have 2 columns (email and password) however you're putting in values for 3 columns (NULL, $POST['email'], $POST['password']). Now, there are two issues in play here:
1.) Putting quotes around "NULL" will turn it into the string "NULL" and not be the keyword meaning nothing.
2.) With that "NULL" being first, you will get some awkward functionality:
2.a) Your users will always be inserted with an email address of "NULL"
2.b) Your SQL statement should error because you have specified 3 values for only 2 columns.
To fix this, your statement should be:
$query = "INSERT INTO `users` (`email`, `password`)
VALUES ('" . mysql_real_escape_string($_POST['email']) . "', '" . mysql_real_escape_string($_POST['password']) . "')";
$result = mysql_query($query) or die(mysql_error());
As for the blank page issue... here is your code a little prettier and for sanities sake, a little more definitive as to which braces match up with what if() or else statement:
<?php
// here, we check if the form has been submitted, because we need to handle
// redirection before we handle outputting the HTML stuff.
if (isset($_POST['submit']))
{
if (empty($_POST['email']) || empty($_POST['password']))
{
$error = 'Please fill in all fields.'; // here, they have not filled in either the username OR the password. Set an error.
}
else
{
// MAKE CONNECTION
include ('db_connect.php');
// connect to the mysql server
$link = mysql_connect($host, $username, $password)
or die ("Could not connect to mysql because ".mysql_error());
// select the database
mysql_select_db($database)
or die ("Could not select database because ".mysql_error());
// check if the email is taken
$check = "select email from users where email = '".$_POST['email']."'";
$qry = mysql_query($check) or die ("Could not match data because ".mysql_error());
$num_rows = mysql_num_rows($qry) or die(mysql_error());
if ($num_rows < 1)
{
// Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.
if(get_magic_quotes_gpc())
{
$product_name = stripslashes($_POST['email']);
$product_description = stripslashes($_POST['password']);
}
else
{
$product_name = $_POST['email'];
$product_description = $_POST['password'];
}
// Make a safe query
$query = sprintf("INSERT INTO users (`email`, `password`) VALUES ('%s', '%s')",
mysql_real_escape_string($email, $link),
mysql_real_escape_string($password, $link));
$result = mysql_query($query, $link);
// If there is no result, or there was not at least 1 row affected, die...
if(!$result || mysql_affected_rows() < 1)
{
die('Could not insert user because ' . mysql_error());
}
// redirect them to the user account page, because we successfully ran the SQL
// notice how we haven't output ANYTHING to the browser yet- header() works
header('Location: user.php');
exit();
}
else
{
$error = 'That email is already in use, please select a different one.';
}
}
}
else
{
// now here, we've either redirected to the user account page, this is the first visit, or there
// was an error with the form/registration. So, we echo the HTML
}
?>
Now, I've modified the section where you say "Make a safe query" to actually use the proper syntax for sprintf() as well as the proper SQL query (you don't need the %d since you have no 3rd column defined, see earlier notes).
Notice that you start your file by looking at the $_POST superglobal and seeing if a form has been submitted. This is good; however, if a form has not been submitted, the very last "else" statement will be triggered. In this case, you have nothing there. So there's a blank page possibility.
Now, in the last else{} statement you have comments which hint to the fact that if the user hasn't visited the page yet, or there were errors, you are to show an HTML form. The thing to remember about if()else{} statements is that if something evaluates to true, then else won't run (and vice-versa, if something evaluates to false, then else{} will run but if() won't).
So how is it that we show the form with the $_POSTed data and error messages? Remove the final else{} (which now is blank and has only comments in it) and just print up an HTML form. Here's an example of something that might work:
<?php
// here, we check if the form has been submitted, because we need to handle
// redirection before we handle outputting the HTML stuff.
if (isset($_POST['submit']))
{
if (empty($_POST['email']) || empty($_POST['password']))
{
$error = 'Please fill in all fields.'; // here, they have not filled in either the username OR the password. Set an error.
}
else
{
// MAKE CONNECTION
include ('db_connect.php');
// connect to the mysql server
$link = mysql_connect($host, $username, $password) or die ("Could not connect to mysql because ".mysql_error());
// select the database
mysql_select_db($database) or die ("Could not select database because ".mysql_error());
// check if the email is taken (safe query):
$query = sprintf("SELECT `email` FROM `users` WHERE `email` = '%s'",
mysql_real_escape_string($_POST['email']));
$qry = mysql_query($query) or die ("Could not match data because ".mysql_error());
$num_rows = mysql_num_rows($qry) or die(mysql_error());
if ($num_rows < 1)
{
// Reverse magic_quotes_gpc/magic_quotes_sybase effects on those vars if ON.
if(get_magic_quotes_gpc())
{
$product_name = stripslashes($_POST['email']);
$product_description = stripslashes($_POST['password']);
}
else
{
$product_name = $_POST['email'];
$product_description = $_POST['password'];
}
// Make a safe query
$query = sprintf("INSERT INTO users (`email`, `password`) VALUES ('%s', '%s')",
mysql_real_escape_string($email, $link),
mysql_real_escape_string($password, $link));
$result = mysql_query($query, $link);
// If there is no result, or there was not at least 1 row affected, die...
if(!$result || mysql_affected_rows() < 1)
{
$error = 'Could not insert user because ' . mysql_error());
}
else
{
// redirect them to the user account page, because we successfully ran the SQL
// notice how we haven't output ANYTHING to the browser yet- header() works
header('Location: user.php');
exit();
}
}
else
{
$error = 'That email is already in use, please select a different one.';
}
}
}
// If they've posted but there was an error, kindly show their email address for them again.
if(isset($_POST['email']))
$email = $_POST['email'];
else
$email = '';
// If there is an error, show it:
if(isset($error) && !empty($error))
{
echo '<div><h4>Error!</h4><p>' . $error . '</p></div>';
}
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<label for="email">Email Address:</label> <input type="text" name="email" id="email" value="<?php echo $email; ?>" /><br />
<label for="password">Password:</label> <input type="password" name="password" id="password" value="" /><br />
<input type="submit" name="submit" value="Submit" />
</form>
Hope that helps.
EDIT: If you're wondering about the header() call and the plain HTML at the end, don't. Since you're posting back to the same script, the if() statement will catch when the form has been posted, and since between the start of the script processing and the time you call header() nothing has been outputted, you will be fine. Please note that while I'm 99% sure this will work, I did not test this code.
