Page 1 of 1

Checking with a MySQL database that the username is unique

Posted: Sat Jun 05, 2004 9:48 am
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.

Posted: Sat Jun 05, 2004 10:59 am
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..

Posted: Sat Jun 05, 2004 11:31 am
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.

Posted: Sat Jun 05, 2004 1:23 pm
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"; }

Posted: Sat Jun 05, 2004 2:54 pm
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.

Posted: Mon Jun 07, 2004 6:04 am
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...

:)

Posted: Mon Jun 07, 2004 6:12 am
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.

Posted: Mon Jun 07, 2004 10:12 am
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).

Posted: Mon Jun 07, 2004 10:19 am
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?

Posted: Mon Jun 07, 2004 10:23 am
by dave420
I hear what you're saying... oopsie on that query :-P

Posted: Mon Jun 07, 2004 10:39 am
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! :)

Posted: Mon Jun 07, 2004 10:43 am
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!

Posted: Mon Jun 07, 2004 10:55 am
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

Posted: Mon Jun 07, 2004 11:05 am
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