Jump to content

Username Increment

DavidY

By DavidY
in PHP

Recommended Posts

DavidY Newbie

DavidY

Posted
Posted

I'm wanting to increment usernames in ONLY the lowercase a-z for example if a user enters "abc" it will automatically be given "abc1" and the next user if entering the same username will automatically be given "abc2", the next "abc3" and so on. The base login and register script I'm using can be found on http://evolt.org/node/60265/ The database.php and register.php pages are below, but excluding the amendments I am seeking. I have created a MySQL database with 2 tables, 1 table named 'Users' with 3 columns named (1) 'userid' primary key, auto increment, (2) 'username' (3) 'password'. The other table is named 'UserCount' with 2 columns named (1) 'username' (2) 'count'.Can someone provide the PHP coding for the following process? When a new user registers a new username firstly PHP to check the characters are in lowercase a-z only and no other characters, and reject if they do not match, then for PHP to check to see if that username exists in the 'UserCount' table. If it doesn't exist, PHP to insert the new username into the 'username' column and also set the value of the 'count' column to 1. If the username does exist, PHP to add that username to the 'username' column and increment the value of the 'count' column by 1. Then in the 'Users' table PHP to insert a record with 'Users.username = UserCount.username + count' and PHP to inform the new user of their complete username including the integer that has been added to their first entered username.It would also help if I could be provided a link to a website on where I can better understand how to collate coding of this kind, coz I have tried to formulate this coding but as I'm playing around in the dark I have no idea if it is the right process in particular considering security factors.database.php<?php/** * Connect to the mysql database. */$conn = mysql_connect("localhost", "your_username", "your_password") or die(mysql_error());mysql_select_db('your_database', $conn) or die(mysql_error());?>register.php<?phpsession_start(); include("database.php");/*** Returns true if the username has been taken* by another user, false otherwise.*/function usernameTaken($username){global $conn;if(!get_magic_quotes_gpc()){$username = addslashes($username);}$q = "select username from Users where username = '$username'";$result = mysql_query($q,$conn);return (mysql_numrows($result) > 0);}/*** Inserts the given (username, password) pair* into the database. Returns true on success,* false otherwise.*/function addNewUser($username, $password){global $conn;$q = "INSERT INTO Users VALUES ('$username', '$password')";return mysql_query($q,$conn);}/*** Displays the appropriate message to the user* after the registration attempt. It displays a * success or failure status depending on a* session variable set during registration.*/function displayStatus(){$uname = $_SESSION['reguname'];if($_SESSION['regresult']){?><h1>Registered!</h1><p>Thank you <b><?php echo $uname; ?></b>, your information has been added to the database, you may now <a href="main.php" title="Login">log in</a>.</p><?php}else{?><h1>Registration Failed</h1><p>We're sorry, but an error has occurred and your registration for the username <b><?php echo $uname; ?></b>, could not be completed.<br>Please try again at a later time.</p><?php}unset($_SESSION['reguname']);unset($_SESSION['registered']);unset($_SESSION['regresult']);}if(isset($_SESSION['registered'])){/*** This is the page that will be displayed after the* registration has been attempted.*/?><html><title>Registration Page</title><body><?php displayStatus(); ?></body></html><?phpreturn;}/*** Determines whether or not to show to sign-up form* based on whether the form has been submitted, if it* has, check the database for consistency and create* the new account.*/if(isset($_POST['subjoin'])){/* Make sure all fields were entered */if(!$_POST['user'] || !$_POST['pass']){die('You didn\'t fill in a required field.');}/* Spruce up username, check length */$_POST['user'] = trim($_POST['user']);if(strlen($_POST['user']) > 30){die("Sorry, the username is longer than 30 characters, please shorten it.");}/* Check if username is already in use */if(usernameTaken($_POST['user'])){$use = $_POST['user'];die("Sorry, the username: <strong>$use</strong> is already taken, please pick another one.");}/* Add the new account to the database */$md5pass = md5($_POST['pass']);$_SESSION['reguname'] = $_POST['user'];$_SESSION['regresult'] = addNewUser($_POST['user'], $md5pass);$_SESSION['registered'] = true;echo "<meta http-equiv=\"Refresh\" content=\"0;url=$HTTP_SERVER_VARS[php_SELF]\">";return;}else{/*** This is the page with the sign-up form, the names* of the input fields are important and should not* be changed.*/?><html><title>Registration Page</title><body><h1>Register</h1><form action="<?php echo $HTTP_SERVER_VARS['PHP_SELF']; ?>" method="post"><table align="left" border="0" cellspacing="0" cellpadding="3"><tr><td>Username:</td><td><input type="text" name="user" maxlength="30"></td></tr><tr><td>Password:</td><td><input type="password" name="pass" maxlength="30"></td></tr><tr><td colspan="2" align="right"><input type="submit" name="subjoin" value="Join!"></td></tr></table></form></body></html><?php}?>

Link to comment
Share on other sites
MrAdam Member

MrAdam

Posted
Posted

You don't need to use a second table for this. I wrote a little function for you that should work nicely with your code:

function nextUsername($username){	global $conn;	// select the highest username from a sub-query that	// selects all usernames matching the base username	$q = "		select max(sub.username) from (			select username from Users where username like '" . mysql_real_escape_string($username) . "%'		) sub	";	// run the query	$result = mysql_query($q, $conn);	if (mysql_num_rows($result) == 0)	{		// if there wasn't a mtched username it doesn't		// exist yet, so just return the username anyway		return $username;	}	// extract the previous username from the query	$prev_username = mysql_result($result, 0);	// if the previous username is the requested username,	// we need to return the first incremented version	if ($prev_username == $username)	{		return $username . '2';	}	// else return the username appended with the number	// from the previous username, incremented by one	return $username . (substr($prev_username, strlen($username)-1) + 1);}

I've added plenty of comments so you should be able to understand how it works. I've not tested it though, there may be a syntax or logic error in there somewhere.

Link to comment
Share on other sites
DavidY Newbie

DavidY

Posted
  • Author
Posted
You don't need to use a second table for this. I wrote a little function for you that should work nicely with your code:
function nextUsername($username){	global $conn;	// select the highest username from a sub-query that	// selects all usernames matching the base username	$q = "		select max(sub.username) from (			select username from Users where username like '" . mysql_real_escape_string($username) . "%'		) sub	";	// run the query	$result = mysql_query($q, $conn);	if (mysql_num_rows($result) == 0)	{		// if there wasn't a mtched username it doesn't		// exist yet, so just return the username anyway		return $username;	}	// extract the previous username from the query	$prev_username = mysql_result($result, 0);	// if the previous username is the requested username,	// we need to return the first incremented version	if ($prev_username == $username)	{		return $username . '2';	}	// else return the username appended with the number	// from the previous username, incremented by one	return $username . (substr($prev_username, strlen($username)-1) + 1);}

I've added plenty of comments so you should be able to understand how it works. I've not tested it though, there may be a syntax or logic error in there somewhere.

Thanks MrAdam, I am grateful for your input. I've replaced the code below with your code but while it does open the Register Page and enables a username to be inserted after a username and password has been inserted and the Join button is clicked the page goes blank and nothing is inserted into the database. I would prefer to use one table as you suggest. Can you give it a test and find the problem?function usernameTaken($username){global $conn;if(!get_magic_quotes_gpc()){$username = addslashes($username);}$q = "select username from Users where username = '$username'";$result = mysql_query($q,$conn);return (mysql_numrows($result) > 0);}
Link to comment
Share on other sites
MrAdam Member

MrAdam

Posted
Posted

Hi DavidY, What's the expected output? Also do you have errors disabled? It may be a simple parse error that is hidden under your configuration. Try adding these two lines just after your first opening PHP tag:

ini_set('display_errors', 1);error_reporting(E_ALL);

Link to comment
Share on other sites
DavidY Newbie

DavidY

Posted
  • Author
Posted
You don't need to use a second table for this. I wrote a little function for you that should work nicely with your code:
function nextUsername($username){	global $conn;	// select the highest username from a sub-query that	// selects all usernames matching the base username	$q = "		select max(sub.username) from (			select username from Users where username like '" . mysql_real_escape_string($username) . "%'		) sub	";	// run the query	$result = mysql_query($q, $conn);	if (mysql_num_rows($result) == 0)	{		// if there wasn't a mtched username it doesn't		// exist yet, so just return the username anyway		return $username;	}	// extract the previous username from the query	$prev_username = mysql_result($result, 0);	// if the previous username is the requested username,	// we need to return the first incremented version	if ($prev_username == $username)	{		return $username . '2';	}	// else return the username appended with the number	// from the previous username, incremented by one	return $username . (substr($prev_username, strlen($username)-1) + 1);}

I've added plenty of comments so you should be able to understand how it works. I've not tested it though, there may be a syntax or logic error in there somewhere.

I just had an after thought after sending the last message, that is to delete the code below but rather than delete I shifted the */ to the end, however when inserting "abc" it did NOT add the number 1 but "abc" did enter the database, and I then was able to enter "abc" again and that same username entered the database, again with no number. /* Check if username is already in use */ if(usernameTaken($_POST['user'])){ $use = $_POST['user']; die("Sorry, the username: <strong>$use</strong> is already taken, please pick another one."); }
Link to comment
Share on other sites
DavidY Newbie

DavidY

Posted
  • Author
Posted
Hi DavidY, What's the expected output? Also do you have errors disabled? It may be a simple parse error that is hidden under your configuration. Try adding these two lines just after your first opening PHP tag:
ini_set('display_errors', 1);error_reporting(E_ALL);

Hi MrAdam,I have two php.ini files, one in Apache the other in Windows and I don't know which file is the right one. Both had display_errors = Off which I changed to display_errors = On but it had no effect. When I inserted your snippet after the first PHP tag I got the following message;ForbiddenYou don't have permission to access /<br /><b>Notice</b>: Undefined variable: HTTP_SERVER_VARS in <b>C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/register7.php</b> on line <b>168</b><br /> on this server.Line 168 is <form action="<?php echo $HTTP_SERVER_VARS['PHP_SELF']; ?>" method="post">I now have 4 usernames of "abc" in the database.
Link to comment
Share on other sites
MrAdam Member

MrAdam

Posted
Posted
ForbiddenYou don't have permission to access /
That is an Apache error I believe, saying you don't have permission (web user) to access the document root directory. Sounds like you have some things configured wrong.
<br /><b>Notice</b>: Undefined variable: HTTP_SERVER_VARS in <b>C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/register7.php</b> on line <b>168</b><br /> on this server.
That is the PHP error. I'm not sure how the 2 errors have mixed like that to be honest, but it's just a notice error that you're using the deprecated or out of scope $HTTP_SERVER_VARS.. Use $_SERVER instead.
Link to comment
Share on other sites
DavidY Newbie

DavidY

Posted
  • Author
Posted
That is an Apache error I believe, saying you don't have permission (web user) to access the document root directory. Sounds like you have some things configured wrong.That is the PHP error. I'm not sure how the 2 errors have mixed like that to be honest, but it's just a notice error that you're using the deprecated or out of scope $HTTP_SERVER_VARS.. Use $_SERVER instead.
Thanks MrAdam, I've changed as you suggested. I now get no error but still no number at the end of the username. I've now got 5 "abc" usernames in the database. It's past my bedtime so will try any other suggestions tomorrow.
Link to comment
Share on other sites
MrAdam Member

MrAdam

Posted
Posted

Okay.. We need to see your code really; showing just abstract snippets doesn't help to figure out the problem. If we can see the logic you use, where the function I provided you with is used in conjunction with your own code, should make things a lot clearer.

Link to comment
Share on other sites
DavidY Newbie

DavidY

Posted
  • Author
Posted
Okay.. We need to see your code really; showing just abstract snippets doesn't help to figure out the problem. If we can see the logic you use, where the function I provided you with is used in conjunction with your own code, should make things a lot clearer.
Thanks MrAdam, The evolt site provided 5 pages (1) database.php (2) login.php (3) logout.php (4) main.php (5) register.php The first and the last I posted above coz I guessed only those 2 would need to be changed more in particular the register.php. I could post the others but if you need them it maybe easier for you to visit the site. http://evolt.org/node/60265/ and then you may need to set them up in the Apache htdocs folder to get the base system to work then adjust the code to gain my desired result. Another thought I could send you an email.
Link to comment
Share on other sites
DavidY Newbie

DavidY

Posted
  • Author
Posted
Using two tables for this is completely redundant.If you can post the code and show me how you're using that function I wrote, I should be able to sort it out for you.
I'd prefer to use only one table but need to be shown how. The code you requested is posted below. You can see that I've disabled the first function and also the usernameTaken the later simply by shifting the */<?phpini_set('display_errors', 1);error_reporting(E_ALL);session_start(); include("database.php");/** * Returns true if the username has been taken * by another user, false otherwise. */// *****************************************************/**function usernameTaken($username){ global $conn; if(!get_magic_quotes_gpc()){ $username = addslashes($username); } $q = "select username from users where username = '$username'"; $result = mysql_query($q,$conn); return (mysql_numrows($result) > 0);} */// *****************************************************function nextUsername($username){ global $conn; // select the highest username from a sub-query that // selects all usernames matching the base username $q = " select max(sub.username) from ( select username from Users where username like '" . mysql_real_escape_string($username) . "%' ) sub "; // run the query $result = mysql_query($q, $conn); if (mysql_num_rows($result) == 0) { // if there wasn't a mtched username it doesn't // exist yet, so just return the username anyway return $username; } // extract the previous username from the query $prev_username = mysql_result($result, 0); // if the previous username is the requested username, // we need to return the first incremented version if ($prev_username == $username) { return $username . '2'; } // else return the username appended with the number // from the previous username, incremented by one return $username . (substr($prev_username, strlen($username)-1) + 1);}// *****************************************************/** * Inserts the given (username, password) pair * into the database. Returns true on success, * false otherwise. */function addNewUser($username, $password){ global $conn; $q = "INSERT INTO users VALUES ('$username', '$password')"; return mysql_query($q,$conn);}/** * Displays the appropriate message to the user * after the registration attempt. It displays a * success or failure status depending on a * session variable set during registration. */function displayStatus(){ $uname = $_SESSION['reguname']; if($_SESSION['regresult']){?><h1>Registered7</h1><p>Thank you <b><?php echo $uname; ?></b>, your information has been added to the database, you may now <a href="main.php" title="Login">log in</a>.</p><?php } else{?><h1>Registration Failed</h1><p>We're sorry, but an error has occurred and your registration for the username <b><?php echo $uname; ?></b>, could not be completed.<br>Please try again at a later time.</p><?php } unset($_SESSION['reguname']); unset($_SESSION['registered']); unset($_SESSION['regresult']);}if(isset($_SESSION['registered'])){/** * This is the page that will be displayed after the * registration has been attempted. */?><html><title>Registration Page7</title><body><?php displayStatus(); ?></body></html><?php return;}/** * Determines whether or not to show to sign-up form * based on whether the form has been submitted, if it * has, check the database for consistency and create * the new account. */if(isset($_POST['subjoin'])){ /* Make sure all fields were entered */ if(!$_POST['user'] || !$_POST['pass']){ die('You didn\'t fill in a required field.'); } /* Spruce up username, check length */ $_POST['user'] = trim($_POST['user']); if(strlen($_POST['user']) > 30){ die("Sorry, the username is longer than 30 characters, please shorten it."); } /* Check if username is already in use if(usernameTaken($_POST['user'])){ $use = $_POST['user']; die("Sorry, the username: <strong>$use</strong> is already taken, please pick another one."); }*/ /* Add the new account to the database */ $md5pass = md5($_POST['pass']); $_SESSION['reguname'] = $_POST['user']; $_SESSION['regresult'] = addNewUser($_POST['user'], $md5pass); $_SESSION['registered'] = true; echo "<meta http-equiv=\"Refresh\" content=\"0;url=$HTTP_SERVER_VARS[php_SELF]\">"; return;}else{/** * This is the page with the sign-up form, the names * of the input fields are important and should not * be changed. */?><html><title>Registration Page7</title><body><h1>Register7</h1><form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"><table align="left" border="0" cellspacing="0" cellpadding="3"><tr><td>Username:</td><td><input type="text" name="user" maxlength="30"></td></tr><tr><td>Password:</td><td><input type="password" name="pass" maxlength="30"></td></tr><tr><td colspan="2" align="right"><input type="submit" name="subjoin" value="Join!"></td></tr></table></form></body></html><?php}?>
Link to comment
Share on other sites
MrAdam Member

MrAdam

Posted
Posted

Okay so this is the block we need to look at:

/* Check if username is already in useif(usernameTaken($_POST['user'])){$use = $_POST['user'];die("Sorry, the username: <strong>$use</strong> is already taken, please pick another one.");}*/

When the user tries to register do you want to automatically create them an incremented username (if it exists already), or confirm it with them first?

Link to comment
Share on other sites
DavidY Newbie

DavidY

Posted
  • Author
Posted
Okay so this is the block we need to look at:
/* Check if username is already in useif(usernameTaken($_POST['user'])){$use = $_POST['user'];die("Sorry, the username: <strong>$use</strong> is already taken, please pick another one.");}*/

When the user tries to register do you want to automatically create them an incremented username (if it exists already), or confirm it with them first?

Thanks MrAdam, That block had been disabled by shifting the */ from after the word "use" to after the curly bracket. Before shifting the */ and after inserting the function you provided I just received a blank screen.Yes, I want to automatically create an incremented username to make the process of applying for a username as simple as possible, meaning whatever username is inserted it will be accepted but with a different integer added to the end. With that system it would not matter if the username exists already because each similar username will have a different number, making the entire username unique. If the username does not already exits then the number 1 to be added. No, I don't want any confirmation from the user, but do want to provide the opportunity for users to change their username at a later date however we can worry about that after we have this current problem resolved.
Link to comment
Share on other sites
DavidY Newbie

DavidY

Posted
  • Author
Posted
Okay so this is the block we need to look at:
/* Check if username is already in useif(usernameTaken($_POST['user'])){$use = $_POST['user'];die("Sorry, the username: <strong>$use</strong> is already taken, please pick another one.");}*/

When the user tries to register do you want to automatically create them an incremented username (if it exists already), or confirm it with them first?

Hi MrAdam, Are you progressing or have you given up?
Link to comment
Share on other sites
DavidY Newbie

DavidY

Posted
  • Author
Posted
Okay so this is the block we need to look at:
/* Check if username is already in useif(usernameTaken($_POST['user'])){$use = $_POST['user'];die("Sorry, the username: <strong>$use</strong> is already taken, please pick another one.");}*/

When the user tries to register do you want to automatically create them an incremented username (if it exists already), or confirm it with them first?

Hi MrAdam, Over the last few days I've thought about a search in MySQL based on counting the existing usernames, for example if result is <1 insert in an adjacent column the number 1, if the result is >1, + 1. I've found some data in the MySQL manual 24.2.2. CREATE FUNCTION Syntax, and found COUNT() but I have no idea how to fabricate any coding. Your comments are welcome.
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...