problem with unique attribute

bootcom

I am fairly new to PHP/MySQL and have encountered a problem when I am writing my script. I am trying to setup a registration page for users, which I have done. I want the usernames to be unique, which I did through MySQL. If I try and register a username more than once, it will not enter it into the database. This is fine, but how can I inform the user that the username already exists and that they need to choose a different username, username is also the field name in the database.

nnichols

You can either use mysql_error() (or mysql_errno()) to find out the reason for the insert query failing (duplicate key error) or run a select query against the db first to check to see if the username is already taken.

Hope this helps

bootcom

What code would I need to write to query the database???

nnichols

You just need to add a SELECT statement to your username validation. You still need to check for errors after the insert as it is possible that between you checking the availability of a username and the insert statement being run, another user could have registered with that same username. Unlikely, but possible.

<?
function validate_username($input) {
// check length and anything else you want and then check availability

$sql = "SELECT COUNT(user_id) FROM tbl WHERE username='$input'";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);

if ($row[0] != 0)
    return false;
else
    return true;
}
?>

Hope this helps

bootcom

Here is the page that confirms the record has been added. I have tried your validation script but that doesnt seem to work either. If you could please take a look at the code below and tell me where I am going wrong and how to inform the user that the username has already been taken and thus is not entered into my database. Thanks.

<html>
<head>
<title>Confirmation</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="bootcom.css" rel="stylesheet" type="text/css">
</head>

<body>
<?
$username="bootcom";
$password="mankind";
$database="bootcom_users";

$connection = mysql_connect(localhost,$username,$password)
or die("Database Connection Failed. Please inform our administritive team.");
$result=mysql_select_db($database) or die( "Unable to select database");

function validate_username($username) {
// check length and anything else you want and then check availability

$sql = "SELECT COUNT(id) FROM user_information WHERE username='$username'";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);

if ($row[0] != 0)
return false;
else
return true;
}

$query = "SELECT max(id) as id from user_information";
$result = mysql_query($query);
$id = $row["customerid"] + 1;
$query = "INSERT INTO user_information VALUES ('". $ID ."', '". $firstname ."','". $lastname ."','". $DOB ."','". $email ."','". $city ."','". $telephone ."','". $gender ."','". $username ."','". $password ."','". $MSN ."','". $Yahoo ."','". $date ."')";
$result = mysql_query($query);
if (!$query)
{
die(" Query could not be executed");
}

mysql_close();
print "Record added\n";

?></br>
<p> </p></body>
</html>
😕 😕

nnichols

You need to call the function to get it to do the check -

<html> 
<head> 
<title>Confirmation</title> 
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
<link href="bootcom.css" rel="stylesheet" type="text/css"> 
</head> 

<body> 
<? 
$username="bootcom"; 
$password="mankind"; 
$database="bootcom_users"; 

$connection = mysql_connect(localhost,$username,$password) 
or die("Database Connection Failed. Please inform our administritive team."); 
$result=mysql_select_db($database) or die( "Unable to select database"); 

// validate username
if(!validate_username($username)) {
// damn. username already taken. tell user to use back button and try again
die("The username you chose is unavailable. Please use your browser's back button to try another.");
}

function validate_username($username) { 
// check length and anything else you want and then check availability 

$sql = "SELECT COUNT(id) FROM user_information WHERE username='$username'"; 
$result = mysql_query($sql); 
$row = mysql_fetch_row($result); 

if ($row[0] != 0) 
return false; 
else 
return true; 
} 

$query = "SELECT max(id) as id from user_information"; 
$result = mysql_query($query); 
$row = mysql_fetch_assoc($result);
$id = $row['id'] + 1; 
$query = "INSERT INTO user_information VALUES ('". $ID ."', '". $firstname ."','". $lastname ."','". $DOB ."','". $email ."','". $city ."','". $telephone ."','". $gender ."','". $username ."','". $password ."','". $MSN ."','". $Yahoo ."','". $date ."')"; 
$result = mysql_query($query); 
if (!$query) 
{ 
die(" Query could not be executed"); 
} 

mysql_close(); 
print "Record added\n"; 


?></br> 
<p> </p></body> 
</html>

nnichols

I would suggest that you use an AUTOINCREMENT column in stead of your existing method for assigning the users id. Your current method would fail if two members were signing up at the same time.

bootcom

Your a star !!!!! Thanks for all your help its working perfectly now

sfullman

consider the case. If I have username fullman, I might type it in with upper or lower case. Account for this by the following improved query:

select count(*) from table where LCASE(username) = '" . trim(strtolower($username)) . "'";

also notice the use of trim.

TECHNICALLY, DEPENDING ON HOW THE CASE OF THE DATABASE FIELD IS SET, THIS ISN'T NECESSARY, but it's good practice to implement so you don't have both fullman and Fullman and FULLMAN in the database.

Sam

bootcom

Thats brilliant but where in my script would I enter it 😕

sfullman

well, just replace your SQL query with mine.

Sam

bootcom

I changed the code to the following, and it comes up with an error message. Where am I going wrong ????

$sql = "select count(*) from table where LCASE(username) = '" . trim(strtolower($username)) . "'";

$result = mysql_query($sql);
$row = mysql_fetch_row($result);

bootcom

Also had a couple of people try to sign up to the website today and the error message "The username you chose is unavailable. Please use your browser's back button to try another" appeared. Just to make sure I dropped the database and started from scratch again. It allows me to enter my details but it wont let anyone else. Any ideas as to why its doing this.