unique fields?

[Love2c0de]

Hello,

 

I am attempting a user login system, got all my DB setup and wrote the code. I am having some issues with the username and email fields not being unique (even though when I created my table I made them both unique).

 

Weird thing is, if I reload page and re-enter a username which I know is already entered, it re-enters it the firs time, but when I try to enter it again with a simple F5, it shows the error that it's already registered here.....

 

Here's my form processing code:

<?php

//array to deal with any processing errors, such as empty strings, invalid email etc.
$errors = array();

unset($_POST['reg_submit']);

//registration form submitted,
$_POST['username'] = trim($_POST['username']);
$_POST['password'] = trim($_POST['password']);
$_POST['email'] = trim($_POST['email']);

if($_POST['username'] == "")
{
   $errors[] = "You must enter a username.";
}

if($_POST['password'] == "")
{
   $errors[] = "You must enter a password.";
}

if($_POST['email'] == "")
{
   $errors[] = "You must enter an email address.";
}

   if(!empty($errors))
   {
       $res = implode("<br />",$errors);
   }
   else
   {
       //we know all fields now have some sort of data for us to work with.

       //check the username field
       $user_len = strlen($_POST['username']);

       if($user_len < 8 || $user_len > 32)
       {
           $errors[] = "Your username must be more than 7 and less than 32 characters.";
       }

       if(!ctype_alnum($_POST['username']))
       {
           $errors[] = "Your username can only contain letters and digits.";
       }

       //check the password field
       $pass_len = strlen($_POST['password']);

       if($pass_len < 6 || $pass_len > 18)
       {
               $errors[] = "Your password must be more than 5 and less than 33 characters.";
       }

       //check the email field
       if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL))
       {
           $errors[] = "You did not enter a valid email address.";
       }

       if(!empty($errors))
       {
           $res = implode("<br />",$errors);
       }
       else
       {
           require("db_queries/registration_insert.php");
       }
   }


if(isset($rows))
{
   if($rows == 1)
   {
       $res = "Registration Successful.";
   }
   else
   {
       $res = "Username and/or email address already in use.";
   }
}
?>

 

Here is the prepared insert:

<?php

$conn = new mysqli("localhost","root","","gaming") or die("Error: ");
$stmt = $conn->prepare("INSERT INTO users (username,password,email) VALUES(?,?,?)") or die("Error:".mysqli_error($conn));

$stmt->bind_param("sss",$_POST['username'],$_POST['password'],$_POST['email']);
$stmt->execute();
$stmt->store_result();

$rows = $stmt->affected_rows;

?>

 

I have also provided a couple of images. One of my db structure and one of the data which is entered.

 

Can anyone help?

 

Kind regards,

 

L2c.

[cpd]

What have you done to isolate your error? See the "+Indexes" link right below the table structure "Add new columns" section - click it and see what's in there, is the username field unique?

[Love2c0de]

I didn't know how to deal with the error.f what i

 

It would seem that both the username and email are unique but I think I've done something wrong somewhere.

 

Here is an image of what it is showing me:

 

 

Kind regards,

 

L2c.

Edited January 4, 2013 by Love2c0de

[cpd]

What you've done is created a UNIQUE constraint on two columns, not individually i.e. both the `username` and the `email` combined must be unique.

 

E.g. Valid entries

 

Username	Email
cpd	cpd@phpfreaks.com
cpd	iam@phpfreaks.com

 

Invalid entries

 

Username	Email
cpd	cpd@phpfreaks.com
cpd	cpd@phpfreaks.com

 

You need to edit the constraint and remove the email, then add a new unique constraint on the email column.

 

Edit: I don't know why there's soo much whitespace....

 

Edit: the nl2br (or similar) function was screwing up the post!!!!

Edited January 4, 2013 by CPD

[Love2c0de]

Thank you very much, just edited it and now it is working perfectly.

 

Kind regards,

 

L2c.