[SOLVED] Checking to see if username already exists?

[galvin]

My football pool website has a typical MySQL table called USERS which stores usernames and passwords.  I allow people to change their username/password.  I had an incident just uncovered today where two people had the same username which caused problems naturally. So I have to add code when they try to change their username to check to see if that new username already exists in the USERS table.  And I need to get it done tonight if possible.

 

Here is the full code for the related area.  The part surrounded by ***** is where I need help (obviously I put some plain english in there just to explain what I'm looking to do).  I know there are probably several ways do this, but can anyone help me with the easiest/most efficient way?

 

Do I first have to create an array of ALL the usernames that are returned?  I think I do, but then how do I compare $_POST['username'] to all those usernames to see if there is an exact match?...

 

 

if(isset($_POST['submit']) && $_POST['submit'] == "Update Username") {


	$query = "SELECT username FROM users";	

			$result = mysql_query($query, $connection);
			if (!$result) { 
			die("Database query failed: " . mysql_error());
			} else {

				*****if ($_POST['username'] == any of the existing usernames)*****  {

				echo "That username is already taken, please choose another username";

				} else {

					$query = "UPDATE users
						 		SET username = '{$_POST['username']}'
								WHERE userid ='{$_SESSION['userid']}'";


						$result = mysql_query($query, $connection);
						if (!$result) { 
						die("Database query failed: " . mysql_error());
						} else {
						$updatemessage = "Your username has been changed."; 
						}

				}

			}

}

 

Thanks for taking the time to read this/respond.

 

-Greg

[ToonMariner]

http://www.phpfreaks.com/forums/index.php/topic,268350.msg1265888.html

 

read through that thread.  the issue of updating rather than inserting is still handled by the same method of enforcing unique values in the database.

[galvin]

ok, Ill read through that, Thanks!.  Quick question...how can I run a quick query on the USERS database to see where I currently have duplicates?

 

SELECT username from USERS where ???????

[ToonMariner]

SELECT a.* FROM table as a, table as b WHERE a.username = b.username and a.id != b.id

 

try that

[galvin]

hmmm, that produces....

 

MySQL said: 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table as a, table as b

WHERE a.username = b.username and a.userid != b.userid' at line 2

[ToonMariner]

table as a, table as b - please replace the 'table' with what ever your table is called. - just checked your code and it appears to be called 'users'

[galvin]

im such an idiot (i change ID to be userid, but forgot about the tablename itself). Thanks again!