Database Field Comparison

[designanddev]

Hi all i have nearly accomplish this registration form, i just have a minor problem with matching the post values to whats already stored in the database to then return back a message to the user letting them know whether someone has taken there input entry username/email

 

Heres my code i can compare both username and email fields and also echo the message letting the user know if what soever has already been taken but when it queries the check for the username and i insert a a string that currently in the database it comes up with the error message and also ll does the elseif statement and inserted the records into the database even though its already taken. but the email check stops and doesn't insert the query if there a duplicate.

 

public function Reg() {
global $database;
if(empty($this->username)){
  $this->errors[1] = "You forgot to enter your
  Username."; 
}
if(empty($this->email)){
  $this->errors[2] = "You forgot to enter your
  email."; 
}
if(empty($this->last_name)){
  $this->errors[3] = "You forgot to enter your
  last name."; 
}
if(empty($this->first_name)){
  $this->errors[4] = "You forgot to enter your
  first name.";   
}
if(empty($this->password)){
  $this->errors[6] = "You forgot to enter your
  password.";   
}
if ($this->password != $this->password2) {
$this->errors[5] = 'Your password did not
match the confirmed password.';
}

elseif(empty($this->errors)){
// Register the user in the database...
$match_user = "SELECT * FROM users WHERE username ='{$this->username}' LIMIT 1";
$result1 = mysql_query($match_user,$database->connection);
$num1 = mysql_num_rows($result1);
$match_email = "SELECT * FROM users WHERE email ='{$this->email}' LIMIT 1";
$result2 = mysql_query($match_email,$database->connection);
$num2 = mysql_num_rows($result2);
if($num1 > 0){
echo "username already exist";
}
if($num2 > 0){
echo "email already exist";
}
else{
$insert = "INSERT INTO users (username, password, first_name, last_name, email) VALUES ('{$this->username}','{$this->password}','{$this->first_name}','{$this->last_name}','{$this->email}')";
$r = mysql_query ($insert,$database->connection);
}
if($r){
echo "Thank you for registering";
}
}
}//End of Reg

 

Kind Regards

[Psycho]

After you check all the "basic" (required fields, lengths, formats, etc.) validations you should put an IF condition to do the database validations ONLY if all the basic validations passed. Your elseif to check for existign values is only run if the LAST if condition was NOT true. Plus, Your code to insert the record is an else condition on the count of errors - so the insert would only occur if there WAS an error in the string validation!

 

No need to use specific indexes on the error array - just let PHP do it for you.

 

In your database validation logic you are creating a lot of unnecessary variables. Also, you should definitely make the columns for username and email address as unique in your table. Otherwise, even with your logic you could - potentially - end up with duplicate values due to race conditions. And, if you do that, you could change the logic to attempt the insert first, and if it fails then check for duplicates

 

Lastly, your method should not be echoing the message "Thank you for registering". It should instead return true, if successful, or false if there were errors.

 

Here is a rewrite of your logic. Not tested, so there could be some syntax errors

 

public function Reg()
{
   global $database;

   //Perform initial string validations
   if(empty($this->username)){
    $this->errors[] = "You forgot to enter your Username.";
   }
   if(empty($this->email)){
    $this->errors[] = "You forgot to enter your email.";
   }
   if(empty($this->last_name)){
    $this->errors[] = "You forgot to enter your last name.";
   }
   if(empty($this->first_name)){
    $this->errors[] = "You forgot to enter your first name.";   
   }
   if(empty($this->password)){
   $this->errors[] = "You forgot to enter your password.";   
   }
   if ($this->password != $this->password2) {
    $this->errors[] = 'Your password did not match the confirmed password.';
   }

   //If string errors, perform database validations
   if(count($this->errors)==0)
   {
    // Register the user in the database...
    $query = "SELECT username FROM users WHERE username ='{$this->username}' LIMIT 1";
    $result = mysql_query($query, $database->connection);
    //Check that query succeeded
    if(!$result){
	    $
	    $this->errors[] = "There was an error validating your username.";
	    //Uncomment this line for debugging
	    //$this->errors[] = "Query: {$query}<br>Error: " . mysql_error();
    }
    elseif(mysql_num_rows($result)) {
	    $this->errors[] = "That username already exists";
    }

    //Username check OK
    $query = "SELECT email FROM users WHERE email ='{$this->email}' LIMIT 1";
    $result = mysql_query($query, $database->connection);
    if(!$result){
	    $this->errors[] = "There was an error validating your email.";
	    //Uncomment this line for debugging
	    //$this->errors[] = "Query: {$query}<br>Error: " . mysql_error();
    } elseif(mysql_num_rows($result)) {
	    $this->errors[] = "That email already exists";
    }
   }

   //If no string or database validation errors, insert record
   if(count($this->errors)==0)
   {
    $query = "INSERT INTO users
				  (username, password, first_name, last_name, email)
			  VALUES
				  ('{$this->username}','{$this->password}','{$this->first_name}','{$this->last_name}','{$this->email}')";
    $result = mysql_query ($query, $database->connection);
    //Check for query error
    if(!$result)
    {
	    $this->errors[] = "There was an error creating the record.";
	    //Uncomment this line for debugging
	    //$this->errors[] = "Query: {}<br>Error: " . mysql_error();
    }
   }

   //Return true if no errors, false otherwise
   return (count($this->errors) == 0);

}//End of Reg