[Resolved] Checking if a username already exists

Calahan

This is what I have so far:

<?php
session_start();
include("common.php");

if(!($link_id = mysql_connect($Host, $User, $Pass))) 
{
die(mysql_error());
}

mysql_select_db($DB);
$userexists = "SELECT ID FROM " . $Table . " WHERE username=' " . $_POST['username'] . "'";
$sql = "INSERT INTO " . $Table . " VALUES('','" . $_POST['username'] . "','" . md5($_POST['password']) . "','" . $_POST['firstname'] . "','" . $_POST['lastname'] . "','" . $_POST['email'] . "','" . $_POST['address1'] . "','" . $_POST['address2'] . "','" . $_POST['county'] . "','" . $_POST['postcode'] . "','" . $_POST['country'] . "')";

if(!($result = mysql_query($userexists))) 
{
    die(mysql_error());
}

if(mysql_num_rows($result) == 1)
{
echo "That username has already been taken. Please <a href='register.html'>click here</a> to select an alternative one.";
}

if(!($result2 = mysql_query($sql))) 
{
    die(mysql_error());
}

if(mysql_num_rows($result2) == 1)
{ 
	mysql_query($sql);
	header('refresh: 5; url=index.php');
	echo "<b><u><center>Your account has been created! </b></u><br>Sit tight, we're sending you back to the login page in 5 seconds.<br>
	If your browser doesn't support redirection and you're still here in 5 seconds, <a href='index.php'>click here</a></center>";
}

?>

But the only thing I get is:
Duplicate entry 'xxxxx' for key 2

I would like a more detailed notification to the user of what went wrong.
So what am I missing? Why does it jump over the loop?

tekky

add comments to your die() functions.. like..

die("I died after trying to query to check for duplicate usernames: " .mysql_error());

that way you know which query is erroring

also note, your if checks could be summed up in 1 line by doing

$result = mysql_query($sql) OR die(mysql_error());

which eliminates several lines of things 😉

Calahan

Done. It seems that it's jumping the first if loop and stopping at the next one -

<?php
session_start();
include("common.php");

if(!($link_id = mysql_connect($Host, $User, $Pass))) 
{
die(mysql_error());
}

mysql_select_db($DB);
$userexists = "SELECT ID FROM " . $Table . " WHERE username=' " . $_POST['username'] . "'";
$sql = "INSERT INTO " . $Table . " VALUES('','" . $_POST['username'] . "','" . md5($_POST['password']) . "','" . $_POST['firstname'] . "','" . $_POST['lastname'] . "','" . $_POST['email'] . "','" . $_POST['address1'] . "','" . $_POST['address2'] . "','" . $_POST['county'] . "','" . $_POST['postcode'] . "','" . $_POST['country'] . "')";
$result = mysql_query($userexists) OR die("Died at first loop " . mysql_error());

if(mysql_num_rows($result) == 1)
{
echo "That username has already been taken. Please <a href='register.html'>click here</a> to select an alternative one.";
}
else
{
	$result2 = mysql_query($sql) OR die("Died at second loop " . mysql_error());
    mysql_query($sql);
    header('refresh: 5; url=index.php');
    echo "<b><u><center>Your account has been created! </b></u><br>Sit tight, we're sending you back to the login page in 5 seconds.<br>
    If your browser doesn't support redirection and you're still here in 5 seconds, <a href='index.php'>click here</a></center>";
}
?>

The resulting error:
Died at second loop Duplicate entry 'xxxxx' for key 2

Its obvious why it died and why the result was returned but what isn't clear to me is why it ignored the first loop.....
I have a rough idea of what the problem is but not the knowledge to fix it. Echoing $result gives me resource ID3. The last time this happend I needed to step through an array to get all the values out but I can't see why I would in this instance as it's only supposed to contain 1 number (the ID number of the existing user) and hence isn't an array but I may be wandering off in a completely different direction here.....

tekky

you have an extra mysql_query($sql); by itself after the else { area.... that oculd be giving it PMS...

and maybe its something with your INSERT string? (I would assume this by the die error)

echo "$sql<br>";
and also, can you list the properties of that table to show the column stats... ie autoinc char[] int... etc etc...

Calahan

The extra one is supposed to be there (or at least I thought so!). I need it to update the database with a new member. Basically, I had this script working fine using:

<?php
include("common.php");

if(!($link_id = mysql_connect($Host, $User, $Pass))) 
{
die(mysql_error());
}
mysql_select_db($DB);

$sql = "INSERT INTO " . $Table . " VALUES('','" . $_POST['username'] . "','" . md5($_POST['password']) . "','" . $_POST['firstname'] . "','" . $_POST['lastname'] . "','" . $_POST['email'] . "','" . $_POST['address1'] . "','" . $_POST['address2'] . "','" . $_POST['county'] . "','" . $_POST['postcode'] . "','" . $_POST['country'] . "')";
if(!($result = mysql_query($sql))) 
{
    die(mysql_error());
}
else
{
	header('refresh: 5; url=index.php');
	echo "<b><u><center>Your account has been created! </b></u><br>Sit tight, we're sending you back to the login page in 5 seconds.<br>
	If your browser doesn't support redirection and you're still here in 5 seconds, <a href='index.php'>click here</a></center>";
}

?>

This worked fine with ID as the primary key and username as a unique field. I just wanted to make the notification that a user with the same username already existed on the database and to prompt the user to select another username rather than the standard : "Duplicate entry 'xxxxx' for key 2"
The way I read the code I have written in my previous posts is basically, if the result of the userexists query returns 1 match, display the message "Username exists" else run the $sql query and update the database with the new member. Have I missed a big chunk out here?