Checking with a MySQL database that the username is unique

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mr-punkstar
Forum Newbie
Posts: 19
Joined: Mon May 31, 2004 1:12 pm

Checking with a MySQL database that the username is unique

Post by mr-punkstar »

basically, I am making an online php based game, and the registration script requires a username and an email to be inputted. Well there are others, but these are the only ones which I require to be unique.

I have thrown together a little script, but i doesn't work. And I cant think of a way to get around it really.

Code: Select all

<html>
<?php

//getting all the information from the form that has been sent to us from signup.php
$username = $_POST['username'];
$password = $_POST['password'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$clubname = $_POST['clubname'];

//connect to the MySQL information
include("config.php");

//the stuff to be put in the database
$query = "INSERT into userinfo (firstname,lastname,email,username,password) VALUES ('".$firstname."','".$lastname."','".$email."','".$username."','".$password."')";
$query1 = "INSERT into clubinfo (username,clubname) VALUES ('".$username."','".$clubname."')";

//check that the username is unique and then email is unique
$usernameunique = "Select email from userinfo where username = '".$username."'";
$resultusernameunique = mysql_query($usernameunique, $connect);
$emailunique = "Select username from userinfo where email = '".$email."'";
$resultemailunique = mysql_query($emailunique, $connect);

if (mysql_num_rows($resultusernameunique) == 1)
{
	echo 'Username already in use';
}
else
{
	if (mysql_num_rows($resultemailunique) == 1)
	{
	      echo 'Email is already registered';
	}
	else
	{
//actually inserting into the database
$result_userinfo = mysql_query($query, $connect);
$result_clubinfo = mysql_query($query1, $connect);

//now that we have told the data to be entered into the database, we can check
$query2 = "SELECT username from userinfo where email = '".$email."' and password = '".$password."'";

$result_test = mysql_query($query2, $connect);

if( mysql_num_rows($result_test) == 0)
{
echo "THERE WAS A FATAL ERROR - PLEASE CONTACT THE WEBMASTER";
}
 		 else
{
echo "Your information has been successfully entered<br />"; 
}
}
}
/*
NOW ENTERING THE MAILING SECTION

The person who is getting the email is $email
*/

$subject = 'Welcome to CLUBLIFE';
$message = 'Welcome to the onlince nightclub game, CLUBLIFE \n If you did not sign up then someone has signed up using this email address \n Below, you will find your login information \n Username - ".$username." \n Password - ".$password." \n\n Enjoy the Game!! \n Nick';
mail($email,$subject,$message) or die('Your email was sent unsuccessfully');

//close the connections
mysql_close();
?>
</html>
Now the script is successful in entering the data into the database, but it allows copies of the username!!

Any assistance would be appreciated.

Nick.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you could search for the username first, or make username a unique key..

You may want to add some validation processing for the post vars instead of blindly adding them..
mr-punkstar
Forum Newbie
Posts: 19
Joined: Mon May 31, 2004 1:12 pm

Post by mr-punkstar »

i have made it an unqiue key on the table. But it still allows it to be enterted into the table.

:roll:

As for validation, I am hoping to include that aswell.

Nick.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

After you connect to the database, add this code:

Code: Select all

$sql = SELECT username FROM yourtable;
$result = mysql_query($sql, $dbconnection); //change this variable to whatever it's set to in your config.php file
while ($row=mysql_fetch_assoc($result)) {
if ($username == $row['username']) {
$uniqueusername = "false"; } //set some variable to false
}//close the 'while brace'
if ($uniqueusername != "false") {
//do all your inserting and all that
}
else {
echo "Your username is already taken"; }
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

If it's a proper unique field in MySQL, then it will not allow a duplicate entry for it - but you'd have to post your SQL table structure for us to see to confirm this.
dreamline
Forum Contributor
Posts: 158
Joined: Fri May 28, 2004 2:37 am

Post by dreamline »

Yep, what i do is after submitting the form is to do a select on the database and see if the user is already there.. if not then i do an insert...

:)
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

There is an easier way that saves you having to do 2 SQL queries - just do an INSERT IGNORE. That way if the user exists already, it won't duplicate them - and if they don't, it'll create a new record.

It also gets around the issue of the fact that using 2 separate queries means it's possible for someone to actually register with the same username inbetween the time it takes for your first SELECT query to bring back the fact that no user exists, and your INSERT query to add them. Although granted, unless you run a popular site this isn't going to happen very often! but it's still possible.
dave420
Forum Contributor
Posts: 106
Joined: Tue Feb 17, 2004 8:03 am

Post by dave420 »

launchcode - your method doesn't tell the user that their username is a duplicate, which in a sign-up process could cause problems ;) The "insert ignore" feature is more for when you want to update a record associated with something/someone (where duplication isn't an issue, as each "thing" you're inserting for is already defined as a single entity), as opposed to check for duplicates... Like, for example, "last accessed" record for a file (only one file has the same path and filename, so there can be no mix-up).
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

Dave -It can if you make it. If the insert fails, it already exists so you can prompt the user as you would have done normally - only this way you don't have to run 2 queries if the username *doesn't* exist. insert ignore is just a way to avoid doing what so many people do - a select check followed by an insert. Your "last accessed" example should use replace into, not insert ignore - as you'd want to keep the most current access date, not the very first one, right?
dave420
Forum Contributor
Posts: 106
Joined: Tue Feb 17, 2004 8:03 am

Post by dave420 »

I hear what you're saying... oopsie on that query :-P
User avatar
launchcode
Forum Contributor
Posts: 401
Joined: Tue May 11, 2004 7:32 pm
Location: UK
Contact:

Post by launchcode »

No worries - I used to do exactly the same thing ages ago (select/insert/update) as mentioned in this thread - infact I used to do a "select * where username=" sort of thing and count the results! Then I moved onto using "select count(*) where username=" which was faster :) But then I had to read up on MySQL for something and found insert ignore/replace into and never looked back. MySQL is so very powerful, but has the most terrible documentation ever :) All the info is there, it's just in a massive heap and you're supposed to know exactly what to look for! :)
magicrobotmonkey
Forum Regular
Posts: 888
Joined: Sun Mar 21, 2004 1:09 pm
Location: Cambridge, MA

Post by magicrobotmonkey »

Heh, yea the documentation is horrible!! I ask myself "can I do this in mysql?" then it takes me forever to find out if I can or not!
dave420
Forum Contributor
Posts: 106
Joined: Tue Feb 17, 2004 8:03 am

Post by dave420 »

magic - take solace in the fact that you're not the only one :) I received training from kaj arno, a mysql guy, and he said the same thing :-P
mr-punkstar
Forum Newbie
Posts: 19
Joined: Mon May 31, 2004 1:12 pm

Post by mr-punkstar »

thanks guys

i have sorted out the actually entering bit, but I havent done the validation as of yet

Check it out

Code: Select all

<?php
//getting all the information from the form that has been sent to us from signup.php
$username = $_POST['username'];
$password = $_POST['password'];
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$email = $_POST['email'];
$clubname = $_POST['clubname'];

//connect to the MySQL information
include("config.php");

//the stuff to be put in the database
$query = "INSERT into userinfo (firstname,lastname,email,username,password) VALUES ('".$firstname."','".$lastname."','".$email."','".$username."','".$password."')";
$query1 = "INSERT into clubinfo (username,clubname) VALUES ('".$username."','".$clubname."')";

//check that the username is unique and then email is unique
$usernameunique = "Select email from userinfo where username = '".$username."'";
$resultusernameunique = mysql_query($usernameunique, $connect);
$emailunique = "Select username from userinfo where email = '".$email."'";
$resultemailunique = mysql_query($emailunique, $connect);
$clubunique = "Select username from clubinfo where clubname = '".$clubname."'";
$resultclubunique = mysql_query($clubunique, $connect);


if (mysql_num_rows($resultusernameunique) == 1)
{
	 header("location:signup.php?reason=user");
}
else
{
   if(mysql_num_rows($resultclubunique) == 1)
	 {
	   header("location:signup.php?reason=club");
	 }
	 else
	 {
	   if (mysql_num_rows($resultemailunique) == 1)
	   {
	     header("location:signup.php?reason=email");
	   }
	else
	{
//actually inserting into the database
$result_userinfo = mysql_query($query, $connect);
$result_clubinfo = mysql_query($query1, $connect);

//now that we have told the data to be entered into the database, we can check
$query2 = "SELECT username from userinfo where email = '".$email."' and password = '".$password."'";

$result_test = mysql_query($query2, $connect);

if( mysql_num_rows($result_test) == 0)
{
echo "THERE WAS A FATAL ERROR - PLEASE CONTACT THE WEBMASTER";
}
 		 else
{
echo "Your information has been successfully entered<br />"; 
}
}
}
}
/*
NOW ENTERING THE MAILING SECTION

The person who is getting the email is $email
*/
echo "<PRE>_POST: "; 
print_r ($_POST); 
echo "</PRE>"; 

$subject = "Welcome to CLUBLIFE";
$message = "";
$message = "<div align="center"> />";
$message = "</div><center><p><u><b><font face="tahoma" size="2">Welcome to ClubLife!</font></b></u><p><font face="tahoma" size="2">";
$message = "If you did not sign up yourself then somebody else registered this email address to a great new game!<p>";
$message = "Below you will find your username and password which are required to sign into your console screen.<p>";
$message = "Username -".$username." <br />";
$message = "Password -".$password." <p>";
$message = "I hope you enjoy the game!<p>Nick";

$headers  = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";

mail($email,$subject,$message, $headers) or die('Your email was sent unsuccessfully');

//close the connections
mysql_close();
?>
Ahh i hate mail too!

Thats all correct isnt it!?

I am a begginer to php, sorryto be a bother lol.

This is myt first project, i know its ambitious. Lol!

Nick
Post Reply