unique fields

pablodelapena

Hey, im a newbie but i thought id better ask in here as this is concerning MySQL.

Ok well, on my site it has a registration feature. When you register it puts your details into a table called 'pending' after checking to see if there are duplicate entries in auth or userifno. Finally it sends you an email with a randomly generated number. Tjhere are also 2 other tables, auth and userinfo.

When you check your inbox in the email that was sent is a link which takes you to the activae.php page. You type in your code and it copies the info from pending into auth and userinfo. Then it deletes your information from pending.

Ok that all works great!

But earlier today i came across a problem and im not sure who to go about fixing it.

Some one registered, for simplicity we'll use: Pablo

Everything went fine and all was a-ok. Then today someone else came along and registered with the name: pablo

it accepted the 2nd because it was a lower case 'p'. However, when they activated their account it would not add them to auth or userinfo because apparently pablo was a duplicate of Pablo (I have set the username fields as unique in all tables).

Im confused as im not sure how to stop a user adding themselves to the pending table. This is the code i use to check:

//Include the configuration file
include ("config.php");

//Lets check to see if their username is already taken in auth.
$query_checkUsername = "SELECT * FROM auth";
$result = mysql_query($query_checkUsername);


While ($row = mysql_fetch_array($result))
{
	if ($row[username] === "$chosenusername") {
	header ("location: register.php?error=taken&email=$email&name=$name&location=$location");
	exit();
	} else {}

}

//Lets check to see if their username is already in the pending list
$query_checkUsername = "SELECT * FROM pending";
$result = mysql_query($query_checkUsername);


While ($row = mysql_fetch_array($result))
{
	if ($row[username] === "$chosenusername") {
	header ("location: register.php?error=taken&email=$email&name=$name&location=$location");
	exit();
	} else {}

}

😕

thanks
Pablo de la Pena

pablodelapena

or would i be best not having the fields set as unique? and leaving the code to work on its onw as is?

kevinsequeira

some suggestions :
1.
why dont u do the select queries like this
instead of looping the result set of all the users.

SELECT from auth where username ='$chosenusername';
and
SELECT from pending where username ='$chosenusername';
then check if mysql_num_rows($result) ==0, if it is then its a new user

also instead of having 2 tables like auth and pending , have a single table with an extra column called status which could be 'pending' or 'authenticated'
this will save u the trouble of checking 2 tables and deleting from pending table. once user clicks the email link change status from pending to authenticated.
also what i would do is to to force all the usernames to lowercase[ but if u do the query the way i suggested it would take care of both cases]

to force to lowercase

SELECT * from auth where username ='".strtolower($chosenusername)."'";

so whereever u use usernaem or chosenusername [ like inserting into db, or checking] use always strtolower.

hope this helps
reg
kevin

pablodelapena

thats great! but ifi force to lower case when a user logs in will it not get an error if they decide to have a capital letter somewhere? or is there a javascript that could warn them?

kevinsequeira

no it wont matter if they use uppercase / mixed case while logging in.
The reason being when the user logs in through the form u will convert the entered user_name to lower case before checking against the database.
In this way u will have a neat db with all usernames in lowercase

also try this on ure database

select from $table where user = 'pablo';
select from $table where user = 'Pablo';
select * from $table where user = 'pABlo';

u get the same id for all three searches.

the problem u had was because u where looping through the resultset and comparing 2 strings and therefore
'pablo' != 'Pablo'

hope that clears ure doubts
reg
kevin