Check to see if username is already being used

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 1569 Views - Last Post: 06 March 2013 - 08:54 PM Rate Topic: -----

#1 hugoriffic  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 185
  • Joined: 16-September 09

Check to see if username is already being used

Posted 03 March 2013 - 05:52 PM

I am trying to check my database for a username when a new user tries to create an account. If the username chosen already exists I want to display an error message stating this. Here is the code I cam up with but it does not work. Also, I'd like to check the same for email addresses. I do not want a user to have multiple usernames on the same email address.

$sql = "SELECT COUNT(*) FROM user WHERE user_name = '{$_POST['user_name']}'";
sql_result = mysql_query($sql);
 
if (mysql_result($sql_result, 0) > 0)
{
Echo "This username is already in use.Please choose another.";
showForm(null);
exit();
}
else //insert into database
{
//do insert
}



I've also tried this code:
$sql = mysql_query("SELECT * FROM user WHERE user_name='$UserName'");
$result = mysql_num_rows($sql);

if($result !=="0"){
   
echo "The user name you have chosen already exists! Please enter a different user name";
exit();
}
else //insert into database
{
//do insert
}



Any help would be appreciated. Also, would the code to check the email be added to the SQL statement within the code where I have it??

Is This A Good Question/Topic? 0
  • +

Replies To: Check to see if username is already being used

#2 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2911
  • View blog
  • Posts: 10,079
  • Joined: 08-August 08

Re: Check to see if username is already being used

Posted 03 March 2013 - 07:45 PM

Do you have everything backed up so that when your site is hacked you'll be able to restore it? You're begging for it, so you should be prepared.

Read up on prepared statements, then worry about the right query.
Was This Post Helpful? 0
  • +
  • -

#3 hugoriffic  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 185
  • Joined: 16-September 09

Re: Check to see if username is already being used

Posted 03 March 2013 - 07:56 PM

CTphpnwb,
Yes, I do have the prepared statements written. If it is necessary I could post the entire code for review tomorrow. I just wanted to see if the stripped down query itself was written properly. I've never checked to see if a username was already in use, let alone an email address, and there is very little out there on the web for how to do so.
If asked I will post the entire webpage code for review.
Thank you though for your input. I appreciate any, and all, feedback at this time as I am relatively new to PHP. I'm studying it more and trying to improve (especially in regards to security) but a lot of the code I run across is advanced and makes no sense to me yet. I'll get there though.
Was This Post Helpful? 0
  • +
  • -

#4 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2911
  • View blog
  • Posts: 10,079
  • Joined: 08-August 08

Re: Check to see if username is already being used

Posted 03 March 2013 - 08:11 PM

Well, if you're using prepared statements they'd look more like this:
SELECT * FROM user WHERE user_name=?

but I'd check the count
SELECT coun(*) FROM user WHERE user_name=?


Read up on PDO.
Was This Post Helpful? 0
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3512
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Check to see if username is already being used

Posted 03 March 2013 - 11:07 PM

View Posthugoriffic, on 04 March 2013 - 01:52 AM, said:

If the username chosen already exists I want to display an error message stating this. Also, I'd like to check the same for email addresses.

the most foolproof way to do that is to set UNIQUE (resp. PRIMARY KEY) constraints onto the DB table. after that you simply cannot insert values that already exist.

and in extension of that, if there are such constraints, you donít even need to test those values first. you simply can insert and when the query fails, the name (or email) already existed.
Was This Post Helpful? 1
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3715
  • View blog
  • Posts: 5,972
  • Joined: 08-June 10

Re: Check to see if username is already being used

Posted 03 March 2013 - 11:27 PM

SQL Injection threads, deprecated APIs, and improper unique constraint setups aside... I don't see a reason why the first code snippet you posted should not work, given that you fix the syntax error in line 2 where you create the $sql_result variable.

The method you are trying to use there is in fact the best way to check for the existence of rows matching a specific criteria. That is, by using the COUNT() function in SQL and reading that value in your PHP code. The other method, where you select everything (or even just some specific fields) from all the matching rows, and then use mysql_num_rows() to count them, is far inferior in that it wastes resources on fetching data you have no intention of using.


Also, there is one thing about your second snippet worth pointing out. That is: your use of the strict !== comparison operator. The mysql_num_rows function would return a number; an integer specifying how many rows there are in the result set. If you compare that strictly to the string "0" it'll never be true, even if there are no rows. A strict comparison between the number 0 and the string "0" will never be true, as a strict comparison checks the type as well as the value, and doesn't type-cast in the background like a normal != comparison would.
Was This Post Helpful? 1
  • +
  • -

#7 hugoriffic  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 185
  • Joined: 16-September 09

Re: Check to see if username is already being used

Posted 04 March 2013 - 07:00 AM

The database is set up with a composite primary key containing both the user name and the email address. Each user ID therefore is unique.
How would I go about setting up constraints?

View PostAtli, on 04 March 2013 - 06:27 AM, said:

SQL Injection threads, deprecated APIs, and improper unique constraint setups aside... I don't see a reason why the first code snippet you posted should not work, given that you fix the syntax error in line 2 where you create the $sql_result variable.

Are you stating that if I change '{$_POST['user_name']}'"; to ? it should work properly??
Was This Post Helpful? 0
  • +
  • -

#8 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3512
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Check to see if username is already being used

Posted 04 March 2013 - 07:05 AM

View Posthugoriffic, on 04 March 2013 - 03:00 PM, said:

The database is set up with a composite primary key containing both the user name and the email address. Each user ID therefore is unique.

nope. a composite primary key ensures, that the combination of name and email is unique, but doesnít say anything about the uniqueness of each field in itself.
Was This Post Helpful? 0
  • +
  • -

#9 hugoriffic  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 185
  • Joined: 16-September 09

Re: Check to see if username is already being used

Posted 04 March 2013 - 11:21 AM

Is this the correct format?? or should it be one of these?

user_name = :user_name
user_name = $UserName

$sql = "SELECT * FROM user WHERE user_name = user_name";
sql_result = mysql_query($sql);
 
if (mysql_result($sql_result, 0) > 0)
{
Echo "This username is already in use.Please choose another.";
showForm(null);
exit();
}
else //insert into database
{
//do insert
}


This post has been edited by hugoriffic: 04 March 2013 - 11:31 AM

Was This Post Helpful? 0
  • +
  • -

#10 hugoriffic  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 185
  • Joined: 16-September 09

Re: Check to see if username is already being used

Posted 04 March 2013 - 12:08 PM

Sorry for posting the other topic. I thought they were separate issues. So, I believe I have the password check working. I get no errors and I double checked the database naming conventions to match with my code.
So, the remaining question is, how to I direct the user who has just logged in to the homepage?? I've checked several dozen sites and nothing indicates how to do so or where to put the code, although they do mention that it is often done.
Here is what I have:

<?php
	//declaring variables and assigning values from log in form
	
	//validate text was entered in UserName text box
	if(empty($_POST['txtUserName']))
		{
			showForm(null);
			exit();
		}
	else
		{
			$UserName = $_POST['txtUserName'];
		}
		
	//validate text was entered in password text box
	if(empty($_POST['txtPassword']))
		{
			showForm(null);
			exit();
		}
	else
		{
			$Password = $_POST['txtPassword'];
		}
		
	
	//$UserName = $_GET['txtUserName'];
	//$Password = $_GET['txtPassword'];
	
	//declare and assign values to variables
	$dsn = 'mysql:host=XXX;dbname=XXX';
	$username='XXX';
	$password='XXX';
	//variable for errors
	$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
	//try to run code
	try {
	//object to open database
	$db = new PDO($dsn,$username,$password);
	//check username against password
		$SQL = $db->prepare("Select Password from user where USER_NAME = :USER_NAME");
		$SQL->bindValue(':UserName', $UserName);
		$SQL->execute();
		$username = $SQL->fetch();
		
        if($username === false)
			{
				$Password = null;
		    }
		else
		    {
				$Password = $username['Password'];
			}
			
		return $Password;
		$SQL->closeCursor();
		$db = null;
				
		} catch(PDOException $e){
			$error_message = $e->getMessage();
			echo("<p>Database Error: $error_message</p>");
			exit();
		}
    
	
function showForm($formMessage = "Please Enter Valid User ID and Password")
	{ ?>


Was This Post Helpful? 0
  • +
  • -

#11 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3512
  • View blog
  • Posts: 10,136
  • Joined: 08-June 10

Re: Check to see if username is already being used

Posted 04 March 2013 - 12:13 PM

View Posthugoriffic, on 04 March 2013 - 08:08 PM, said:

So, I believe I have the password check working. I get no errors and I double checked the database naming conventions to match with my code.

nope, but since you forgot to enable the exceptions, PDO didnít tell you (the $options only work, if you use them).

how to test whether a login-password combination is valid has been explained by Atli previously.

and when you use named placeholders, make sure they are both named the same.

View Posthugoriffic, on 04 March 2013 - 08:08 PM, said:

So, the remaining question is, how to I direct the user who has just logged in to the homepage??

in pseudo-code
if (password_match) {
  include "page content";
}
else {
  include "login form";
}

Was This Post Helpful? 0
  • +
  • -

#12 hugoriffic  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 185
  • Joined: 16-September 09

Re: Check to see if username is already being used

Posted 04 March 2013 - 04:46 PM

Finally got the page to work properly except one thing. If I enter a username with the wrong password the fields go blank and no message is displayed. Here is the code I used:

if ($Password != Password($User_Name))
	{
		showForm('Customer ID and Password did not match. Please try again!');
		exit();
	}
	else {
		include "index.php";
		exit();
	}


Was This Post Helpful? 0
  • +
  • -

#13 hugoriffic  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 185
  • Joined: 16-September 09

Re: Check to see if username is already being used

Posted 04 March 2013 - 05:05 PM

There is a showForm function that is written as such:

function showForm($formMessage='Invalid User Name And Password. Please Try Again.')



Is there a conflict here??
Was This Post Helpful? 0
  • +
  • -

#14 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3715
  • View blog
  • Posts: 5,972
  • Joined: 08-June 10

Re: Check to see if username is already being used

Posted 04 March 2013 - 05:52 PM

Where is the rest of that function? You've only shown us the definition line, not the code.
Was This Post Helpful? 0
  • +
  • -

#15 hugoriffic  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 185
  • Joined: 16-September 09

Re: Check to see if username is already being used

Posted 04 March 2013 - 07:36 PM

That is all that was written. I don't know what to follow it up with
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2