Check for existing users

[Anon]

I am working on building a registration page which includes signing up for a username. Is there an easy way to check through a MySQL database to see if that user name is already taken?

[Anon]

yea, i had that problem this morning...

variables used:
$username_f = is the submited username
$pass_1 = is the submited password
$pass_2 = is the verifying password
$cherr = short for check error...

$link_id = mysql_connect("localhost","db_username","db_pass");
mysql_select_db("db")

if ( empty( $username_f ) || //check to make sure it's all filled out
	 empty( $pass_1 ) ||
	 empty( $pass_2 ) ||
	 empty( $age_f ) ||
	 empty( $country_f ) ||
	 empty( $email_f ) ) 
	$message .= "* You must fill in ALL fields<br>";

 if ( $pass_1 != $pass_2 ) //check to see if the passwords match
	$message .= "* Your passwords do not match<br>";

 if ( $message == "" ) { //this checks if the username is taken
	$result = mysql_query( "SELECT * FROM users WHERE user = '$username_f' ", $link_id );
	$query_data = mysql_fetch_array($result);
	$user = $query_data["user"];
	if ( $user == $username_f )
	 $cherr = "error";	
 if ($cherr != ""){
		$message .= "* Username $username_f already exists. Please pick different Username<br>";
		}
 elseif ($cherr == "") { ///add'em to the database... they have passed...

[Anon]

<?
if($ref)
{
setcookie("ref", $ref);
}
$title = "Sign Up for domain.com!";
$access = 'y';
include("/home/sites/site143/web/functions.php");
include("/home/sites/site143/web/header.php");
$username = strtolower($username);
$cheech = @("SELECT FROM user WHERE user = '$username'");
$memberz = @mysql_num_rows($cheech);
$cheech1 = @("SELECT FROM user WHERE email = '$email'");
$emailnum = @mysql_num_rows($cheech1);
$joindate = date("y/m/d");
$age = ageof($month, $day, $year);
if ($action == "yes")
{

if ((!$username || !$f_name || !$zip || !$sex || !$email || !$country || !$month || !$day || !$year) || ($year == "Select one") || ($month == "Select one") || ($day == "Select one") || ($sex == "Select one"))
{ echo "<font color=\"#808080\" size=\"1\"
face=\"Tahoma\"><strong><div align=\"center\">Please Fill in All fields unless they are marked by a And/Or Select Something From The Drop Down Menus. Please try again.</font></strong></div>"; }
elseif ($password != $password2)
{ echo "<font color=\"#808080\" size=\"1\"
face=\"Tahoma\"><strong><div align=\"center\">Passwords do not match! Please try again.</font></strong></div>"; }
elseif ($memberz != "0")
{ echo "<font color=\"#808080\" size=\"1\"
face=\"Tahoma\"><strong><div align=\"center\">Sorry, the username you selected was allready taken. Please Try again. </font></strong></div>"; }
elseif (!is_numeric($zip))
{ echo "<font color=\"#808080\" size=\"1\"
face=\"Tahoma\"><strong><div align=\"center\">Sorry, zip code is wrong. please only use numbers!</font></strong></div>"; }
elseif(!eregi("^0-9a-z@0-9a-z*\.[a-z]{2,3}$",$email))
{ echo "<font color=\"#808080\" size=\"1\"
face=\"Tahoma\"><strong><div align=\"center\">Please provide a valid email, i.e. [e-mail].</font></strong></div>"; }
elseif($age < 14)
{ echo "<font color=\"#808080\" size=\"1\"
face=\"Tahoma\"><strong><div align=\"center\">Sorry. You have to be 13 to sign up 🙁</font></strong></div>"; }
elseif($emailnum >= 1)
{ echo "<font color=\"#808080\" size=\"1\"
face=\"Tahoma\"><strong><div align=\"center\">Sorry. One account per person, you know that! ;(</font></strong></div>"; }
else
{
$password = strtolower($password);
srand(time());
$pin = rand(1,5000);
mysql_query("INSERT INTO user (user,pw,email,joindate,fname,zip,country,month,day,year,confirmed,ref,sex)
VALUES ('$username','$password','$email','$joindate','$f_name','$zip','$country','$month','$day','$year','$pin','$ref','$sex')");
?>
<center><font color="#808080" size="1"
face="Tahoma">Thanks for signing up for the fastest growing community, <? echo"$f_name"; ?>! You should recieve an email soon which you must read before your account will become active.<? echo $ref; ?></font></center>
<?
mail("$email", "Thank You for Joining domain.Com!", "

Sup, $name

Thanks again for joing domain.com $f_name.  To help out site run smoother we require that you do the following:

Go To: <a href=\"http://www.domain.com/confirm.php?user=$username&pin2=$pin&action=yes\">Here</a> ( http://domain.com/confirm.php ):

If asked, please enter the following:

Username: $username
Pin: $pin

Thank You,
The domain.Com Team.
","From: [e-mail]\nReply-To: [e-mail]\nX-Mailer: domain.com/");
include("footer.php");
exit;
}}
?>
<center><font color="#808080" size="1"
face="Tahoma"><h1>Signup</h1><FORM METHOD="post" ACTION="<? echo"$PHP_SELF"; ?>" onSubmit="submitonce(this)"><P><strong>Username:</strong><br><INPUT TYPE="text" Name="username" SIZE=25 MAXLENGTH=25 VALUE="<? echo "$username" ?>"></P><P><strong>Password:</strong><br><INPUT TYPE="password" NAME="password" SIZE=25 MAXLENGTH=25 VALUE="<? echo "$password" ?>"></P><P><strong>Confirm Password:</strong><br><INPUT TYPE="password" NAME="password2" SIZE=25 MAXLENGTH=25 VALUE="<? echo "$password2" ?>"></P><P><strong>Your Email Address:</strong><br><INPUT TYPE="text" NAME="email" SIZE=25 MAXLENGTH=50 VALUE="<? echo "$email" ?>"></P><P><strong>First Name:</strong><br><INPUT TYPE="text" NAME="f_name" SIZE=25 MAXLENGTH=50 VALUE="<? echo "$f_name" ?>"></P><P><strong>Zip Code:</strong><br><INPUT TYPE="text" NAME="zip" SIZE=5 MAXLENGTH=5 VALUE="<? echo "$zip" ?>"></P><P><strong>Country:</strong><br><INPUT TYPE="text" NAME="country" SIZE=25 MAXLENGTH=25 VALUE="<? echo "$country" ?>"></P><P><strong>Sex:</strong><br><select tabindex="21" name="sex">
<option>Select one</option>
<option>m</option>
<option>f</option>
</select></P><P><strong>Birthday:</strong><br>Month:<select tabindex="21" name="month">
<option>Select one</option>
<option>01</option>
<option>02</option>
<option>03</option>
<option>04</option>
<option>05</option>
<option>06</option>
<option>07</option>
<option>08</option>
<option>09</option>
<option>10</option>
<option>11</option>
<option>12</option>
</select>Day:<select tabindex="22" name="day">
<option>Select one</option>
<option>01</option>
<option>02</option>
<option>03</option>
<option>04</option>
<option>05</option>
<option>06</option>
<option>07</option>
<option>08</option>
<option>09</option>
<option>10</option>
<option>11</option>
<option>12</option>
<option>13</option>
<option>14</option>
<option>15</option>
<option>16</option>
<option>17</option>
<option>18</option>
<option>19</option>
<option>20</option>
<option>21</option>
<option>22</option>
<option>23</option>
<option>24</option>
<option>25</option>
<option>26</option>
<option>27</option>
<option>28</option>
<option>29</option>
<option>30</option>
<option>31</option>
</select>Year:<select tabindex="20" name="year">
<option>Select one</option>
<option>1900</option>
<option>1901</option>
<option>1902</option>
<option>1903</option>
<option>1904</option>
<option>1905</option>
<option>1906</option>
<option>1907</option>
<option>1908</option>
<option>1909</option>
<option>1910</option>
<option>1911</option>
<option>1912</option>
<option>1913</option>
<option>1914</option>
<option>1915</option>
<option>1916</option>
<option>1917</option>
<option>1918</option>
<option>1919</option>
<option>1920</option>
<option>1921</option>
<option>1922</option>
<option>1923</option>
<option>1924</option>
<option>1925</option>
<option>1926</option>
<option>1927</option>
<option>1928</option>
<option>1929</option>
<option>1930</option>
<option>1931</option>
<option>1932</option>
<option>1933</option>
<option>1934</option>
<option>1935</option>
<option>1936</option>
<option>1937</option>
<option>1938</option>
<option>1939</option>
<option>1940</option>
<option>1941</option>
<option>1942</option>
<option>1943</option>
<option>1944</option>
<option>1945</option>
<option>1946</option>
<option>1947</option>
<option>1948</option>
<option>1949</option>
<option>1950</option>
<option>1951</option>
<option>1952</option>
<option>1953</option>
<option>1954</option>
<option>1955</option>
<option>1956</option>
<option>1957</option>
<option>1958</option>
<option>1959</option>
<option>1960</option>
<option>1961</option>
<option>1962</option>
<option>1963</option>
<option>1964</option>
<option>1965</option>
<option>1966</option>
<option>1967</option>
<option>1968</option>
<option>1969</option>
<option>1970</option>
<option>1971</option>
<option>1972</option>
<option>1973</option>
<option>1974</option>
<option>1975</option>
<option>1976</option>
<option>1977</option>
<option>1978</option>
<option>1979</option>
<option>1980</option>
<option>1981</option>
<option>1982</option>
<option>1983</option>
<option>1984</option>
<option>1985</option>
<option>1986</option>
<option>1987</option>
<option>1988</option>
<option>1989</option>
<option>1990</option>
<option>1991</option>
<option>1992</option>
<option>1993</option>
<option>1994</option>
<option>1995</option>
<option>1996</option>
<option>1997</option>
</select></P><? if(!$ref) { ?><P><strong>Referral:</strong><br><INPUT TYPE="text" Name="ref" SIZE=25 MAXLENGTH=25 VALUE="<? echo "$ref" ?>"></p><? } else {?><input type="HIDDEN" value="<? echo $ref; ?>" name="ref"><? } ?><input type="HIDDEN" value="yes" name="action"><P><br>By clicking "Add User" below, You verify you agree to our <A HREF="/TOS.php"><font color="#808080">TOS</A> and <A HREF="Privacy.php"><font color="#808080">Privacy Policy</a>. You also agree all the above information entered is correct.<br><INPUT TYPE="SUBMIT" NAME="submit" VALUE="Add User"></p></center></form>
<?
include("/home/sites/site143/web/footer.php");
?>

[kparker]

This is all very good advice, right down to the SELECT query to find out if the username already taken. That's the part that's not so good, and here's why: <i>any number of clients can be executing this same script at the same time</i>, so finding the id not existing one moment is no guarantee it's still available the next.

Here's the guaranteed-safe way to do it:

(1) Make sure that the user id is defined as the primary key, or at least with a UNIQUE index.

(2) When you get to that point, just go ahead and INSERT the row.

(3) If the insertion succeeds, congratulations! Your work is done.

(4) If the insertion fails because of a key-duplication violation, then you can inform the user that the ID is already taken, repopulate the other fields on the form, and ask them to choose another ID.

The reason step (2) works without fail is that the database backend handles the insertions so that there is no conflict between them.

[Anon]

how is this then? i changed the field in the db to VARCHAR, and set it as unique.... now would this code work ok?

if ( empty( $username_f ) ||
	 empty( $pass_1 ) ||
	 empty( $pass_2 ) ||
	 empty( $age_f ) ||
	 empty( $country_f ) ||
	 empty( $email_f ) ) 
	$message .= "* You must fill in ALL fields<br>";

 if ( $pass_1 != $pass_2 ) 
	$message .= "* Your passwords do not match<br>";

 if ( $message == "" ) {

$result = mysql_query("insert into users (id,user,pass,email,age,country,credits) values ('$id','$username_f','$pass_1','$email_f','$age_f','$country_f','$credits')",$link_id);
if ( ! mysql_query( $query, $link_id ) ){
$message .= "* Username $username_f already exists. Please pick different Username<br>";
return false;
}
else {
return true;

[kparker]

There seems to be an extra (leftover?) call to mysql_query there. Your first one is the one you want.