username check not working for me

[webguync]

I had this working at some point, but must have changed the code or messed up the login. Anyway the check no longer works to see if a username exist in the database and if it does, display an error. Right now I am just getting the generic error . "Duplicate entry 'SamJ' for key 'usr'".

 

<?

// session_start(); 
//Print_r ($_SESSION);

include('config.php');

// Passkey t from link
$passkey=$_GET['passkey'];

$tbl_name1="Profile_temp";

// Retrieve data from table where row matches passkey
$sql1="SELECT * FROM $tbl_name1 WHERE confirm_code ='$passkey'";
$result1=mysql_query($sql1);


// If successfully queried
if($result1){

// Count how many row has this passkey
$count=mysql_num_rows($result1);

// if passkey is found retrieve info from temporary DB
if($count==1){

$rows=mysql_fetch_array($result1);
$FirstName=$rows['FirstName'];
$LastName=$rows['LastName'];
$UserName=$rows['UserName'];
$Password= md5($rows['Password']);
$Password2=md5($rows['Password2']);
$email=$rows['email'];
$Zip=$rows['Zip'];
$Birthday=$rows['Birthday'];
$Security=$rows['Security'];
$Security2=$rows['Security2'];

$tbl_name2="Profile";

// Insert data that retrieves from "temp_members_db" into table "registered_members"
$sql2="INSERT INTO $tbl_name2(`FirstName`,`LastName`,`Username`,`Password`,`Password2`,`email`,`Zip`,`Birthday`,`Security`,`Security2`) VALUES ('$FirstName','$LastName','$UserName','$Password','$Password2','$email','$Zip','$Birthday','$Security','$Security2')"; 
//echo $sql2;
$result2=mysql_query($sql2) or die(mysql_error());
}

// if passkey is not found, display message "Wrong Confirmation code"
else {
echo "<h2>Sorry, Your passkey was not found.</h2>";
}

$sql3="select * from $tbl_name2 where username = '$UserName'"; 
$result3=mysql_query($sql3) or die(mysql_error());


if ($_REQUEST['error'] == 1){
     echo "Sorry, that user name already exist!";
 }
}
else {


}
while ($row = mysql_fetch_assoc($result3)) {
    $_SESSION['id'] = $row['id'];
$_SESSION['FirstName']=$row['FirstName']; 
}


// if successfully moved data from table"temp_members_db" to table "registered_members" displays message "Your account has been activated" and don't forget to delete confirmation code from table "temp_members_db"
if($result2){
echo "<h3>Welcome {$_SESSION['FirstName']} </h3>";

echo "<h2>Your account has been activated</h2>";
echo"<p>You may now upload a profile picture</p>";
//file upload

	echo "

	<form enctype='multipart/form-data' action='Profile.php' method='POST'>

    <fieldset>

    <legend>Upload your profile picture</legend>

    <ol>

      <li id='example3'>

        <label for='FileUpload'>Choose a file to upload:</label>

        <input name='myfile' id='FileUpload'  type='file' />
	 <input type='submit' name='submit' value='Upload File' />

      </li>

    </ol>

    </fieldset>

   

  </form>

  ";

  }
  




}
if($result3){




// Delete information of this user from table "temp_members_db" that has this passkey
$sql4="DELETE FROM $tbl_name1 WHERE confirm_code = '$passkey'";
$result4=mysql_query($sql4) or die(mysql_error());






?>

 

can anyone see what is wrong?

[jdavidbakr]

The "Duplicate Entry" error means that you are performing an insert but the field 'usr' is defined with a unique (or primary) index, and the value 'SamJ' already exists in the table.

 

Might change your die() to

die("mysql error on statement ".$statement." - ".mysql_error())

 

to see what the query is that's dying.

[webguync]

that just gives me this.

"mysql error on statement - Duplicate entry 'webguync' for key 'usr'"

 

I don't mind the error. I actually want the error if there is a duplicate entry in the username field. I just need the error to display something friendlier to the user. Something like "Sorry that user name is already taken, please go back and try again with a different user name!".

[jdavidbakr]

that just gives me this.

"mysql error on statement - Duplicate entry 'webguync' for key 'usr'"

 

I don't mind the error. I actually want the error if there is a duplicate entry in the username field. I just need the error to display something friendlier to the user. Something like "Sorry that user name is already taken, please go back and try again with a different user name!".

 

Then don't use "die" because that will stop the script.  Do something like this:

 

mysql_query($statement);
if (mysql_error()) {
  echo "That already exists";
}

[BlueSkyIS]

You shouldn't use an error for that, you should check the database for the value via SQL query. Otherwise, you've got to parse the error message, and it might not always be that error. Don't intentionally cause errors for any reason. Avoid all errors if at all possible.

[webguync]

I think I am already doing that?

$sql5="select * from $tbl_name2 where username = '$UserName'";
//echo $sql5;
$result5=mysql_query($sql5) or die(mysql_error());
if ($_REQUEST['error'] == 1){
     echo "Sorry, that user name already exist!";
 }
}
else {


}

 

but the code isn't getting processed and I just get the generic error I posted before.

[jdavidbakr]

I think I am already doing that?

$sql5="select * from $tbl_name2 where username = '$UserName'";
//echo $sql5;
$result5=mysql_query($sql5) or die(mysql_error());
if ($_REQUEST['error'] == 1){
     echo "Sorry, that user name already exist!";
 }
}
else {


}

 

but the code isn't getting processed and I just get the generic error I posted before.

 

die() doesn't just print an error.  It kills the script after it prints the error.  Your script isn't passing the die() line.

 

I'm not sure where $_REQUEST['error'] is coming from, it won't be set to 1 if your query fails.

 

But BlueSkyIS is correct anyway, you should check for the username and send the error if it exists rather than intentionally tripping an error.

[webguync]

is this the best way?

 

if(mysql_num_rows(mysql_query("SELECT * FROM $tbl2_name WHERE username = '$Username'"))){
echo "<p>this username already exist</p>";
}

[webguync]

I think a usability problem I have is, when a user fills out a form, they get an email to confirm their membership, and then when they click the link, they get the username already taken error. From a user perspective it would be best to get this error message before they get that far. The reason the login doesn't work at that point is because when you initially register info is sent to a temp table and then when you click the confirm link, the temp table info is deleted so initially you will never see a duplicate username. The duplicate username error should ideally appear with the initial regisration, but not sure how I can do this?

[jdavidbakr]

I wouldn't use a temp table, instead have a column "user_verified" that is initially set to false, and is switched to true when they've verified.  Then just add a test against all three columns - user name, password, and user verified - instead of just user name and password, when logging the user in.

[webguync]

when you say set to false do you mean a zero for false and a one for true?

[jdavidbakr]

Yes, although with MySQL you can use a TINYINT (you can define it as BOOL and it will make it a TINYINT) and use the following, it makes more logical sense than using 1 and 0 to me when you're reading through the code:

 

update user set user_active = true where USER_ID = 1;
select * from user where user_active = false;

 

etc.  Just don't put quotes around 'true' and 'false'.

[webguync]

so basically what I am doing currently is setting up a temp table and inserting a randomized confirmation code along with the form info and then deleting that and inserting into a perm table. The link with randomized code is sent to the users email address in the DB(that they entered).  I need to get rid of the temp table part and just do this?

// temp table name
$tbl_name=Profile_temp;
// Random confirmation code
$confirm_code=md5(uniqid(rand()));

if(!mysql_num_rows($result)){

$sql1="INSERT INTO $tbl_name(`confirm_code`,`FirstName`,`LastName`,`Username`,`Password`,`Password2`,`email`,`Zip`,`Birthday`,`Security`,`Security2`) VALUES ('$confirm_code','$FirstName','$LastName','$UserName','$Password','$Password2','$email','$Zip','$Birthday','$Security','$Security2')";

$sql2="update $tbl_name set user_active = true where USER_ID = 1;
select * from user where user_active = false"; 

$sql3="select * from $tbl_name where username = '$UserName'";
$result=mysql_query($sql, $con) or die(mysql_error());

if (user_active == 1){
     echo "Sorry, that user name already exist!";
}

 

I can just use  a PERM table and then insert this info and then run the update after the insert code?

[jdavidbakr]

More or less.  Obviously you don't want to execute $sql1 until after you've verified that the username doesn't exist, and $sql2 is just a copy of my code which was just to illustrate how you use 'true' and 'false,' it doesn't do anything; instead, in $sql1 include the varified column and set it to false.

[webguync]

but doesn't the info need to be inserted into the DB (SQL1) before the usercheck is done?

[jdavidbakr]

but doesn't the info need to be inserted into the DB (SQL1) before the usercheck is done?

 

No, you check to see if the username exists before you attempt to insert it.  Your psudocode should be something like:

 

1) check to see if the user name exists.  If it does, tell the user that the username already exists and require them to choose another one.

2) If the user name doesn't exist, insert it into the database with the 'active' flag set to false.  Send the user the e-mail with the link to activate the account.

3) When the user clicks on the link, that page will change the 'active' flag to true.

4) Include a check for the 'active' flag when the user logs in to their account.