Trouble inserting into database within While Loop

[Omgabee]

I am VERY new to PHP, and programming in general. I've been trying to self teach myself by building a pretty basic game on a website. I will probably have lots of questions as I am frequently finding myself getting stuck and not able to find a solution even after searching intensively on the web. It seems like every "solution" I find is severely outdated and/or just doesn't work at all. So I've resorted to posting on these forums for help!

include_once ('database-connect.php');

$name = $_POST['name'];
$username = $_POST['username'];
$password = $_POST['password'];
$email = $_POST['email'];
date_default_timezone_set('EST');

$checkquery = "SELECT * from users WHERE username = ?";
$checkstmt = $db->prepare($checkquery);
$checkstmt->execute(array($username));
while($row = $checkstmt->fetchObject())
		{
			$checkusername = $row->username;
			
			//Check if username is taken
			if ($username == $checkusername) {
					echo('Username is taken!');
					} else {
			
						//If username is available...move on here
								$query = "INSERT INTO users (name, username, password, email, signupdate) VALUES (:name,:username,:password,:email,:signupdate)";
								$statement = $db->prepare($query);
								$statement->execute(array(':name'=>$name,
														':username'=>$username,
														':password'=>$password,
														':email'=>$email,
														':signupdate'=>date('m-d-Y H:i:s')));
							}
		}

So that is the part I need help with. It works fine up until the "//If the username is available...move on here" part. I can't get it to enter the data into the database. It works absolutely fine if not put into the while loop.....but I'm trying to get it to verify that the username is not already taken...otherwise it creates the user with duplicate username anyway. Can someone spot my mistake, or even possibly give me a better way to go about this in general? I've literally been stuck on this problem for hours, I'm losing hope!

[kicken]

Your check for the duplicate username is more complicated that it needs to be. There i no need for a while loop at all, nor any need to compare $username to $checkusername, your query's WHERE clause already does that. Just run your query and see if a row is returned.

 

$checkquery = "SELECT * from users WHERE username = ?";
$checkstmt = $db->prepare($checkquery);
$checkstmt->execute(array($username));
$row = $checkstmt->fetchObject();
if ($row){
   //username taken
}
else {
   //username available, do your insert
}

Rather than doing a select to check if the username is taken however, a better approach would be to create a UNIQUE constraint on the username column and then simply try to insert the data. If the insert fails, the most likely cause is the username is already taken. You could inspect the error code if you want to determine exactly why it failed and provide a different error message.

$query = "INSERT INTO users (name, username, password, email, signupdate) VALUES (:name,:username,:password,:email,:signupdate)";
$statement = $db->prepare($query);
$result = $statement->execute(array(':name'=>$name,
   ':username'=>$username,
   ':password'=>$password,
   ':email'=>$email,
   ':signupdate'=>date('m-d-Y H:i:s')
));
if (!$result){
  //failed query, username probably taken.
}

[Omgabee]

Thank you! So, to put the unique constraint I will need to do change my actual database, right?

 

(thanks for the quick reply, btw!)

Edited February 24, 2014 by Omgabee

[kicken]

So, to put the unique constraint I will need to do change my actual database, right?

Yes, you would add a new unique index to the database for that column. If you use phpMyAdmin to manage your DB I believe there is a icon you can click to add the index. Otherwise you can create it with the following sql

 

create unique index ix_username on users (username)

Edited February 24, 2014 by kicken

[Omgabee]

Thanks again, I applied the changes, and works great!