[SOLVED] Incrementing Numbers With Text To Create Usernames

[jasont]

Hey folks,

 

I am adjusting my database to choose the username for new users according to their last name instead of letting them choose it themselves. I am running into a bit of a problem with validation however.

 

I would like to check the database for the same username if it already exists. If it does I want to add a numerical value to it until it becomes unique.

 

For example the username Smith is already taken, I query the database and add 01 to the username making it Smith01. If that is taken I do another query and add another 01 making the new username Smith02 etc. This goes on until a unique username is found.

 

I figured this would be done with a loop but I couldn't figure out the right way to get it working. I currently have the following but it doesn't seem to work for me:

 

$query = "SELECT * FROM client_details WHERE username = '$last_name'";

$data = mysqli_query($dbc, $query);

while(mysqli_num_rows($data) != 0) {
$i=1;
$last_name . $i++;
}

$query = "INSERT INTO client_details (username, fname, lname, email)" .
"VALUES ('$last_name', '$first_name', '$last_name', '$payer_email')";

$result = mysqli_query($dbc,$query)
or die ('Error querying database');

 

If anyone can help me out with this it would be greatly appreciated. I'm not the most advanced php person that's for sure!

 

Regards,

Jason

[cunoodle2]

You are only sending one query.  You need to loop through those to make sure

 

<?php
$query = "SELECT * FROM client_details WHERE username = '$last_name'";
$data = mysqli_query($dbc, $query);

//initialize counter outside of the loop
$i=1;

//set a temp value that stores the last name to later modify
$temp = $last_name;

while(mysqli_num_rows($data) != 0) 
{
   $i++;
   $last_name = $temp.$i;
   $query = "SELECT * FROM client_details WHERE username = '$last_name'";
   $data = mysqli_query($dbc, $query);
}

$query = "INSERT INTO client_details (username, fname, lname, email)" .
   "VALUES ('$last_name', '$first_name', '$last_name', '$payer_email')";

$result = mysqli_query($dbc,$query)
   or die ('Error querying database');

?>

 

Does that work?

[jasont]

cunoodle2 you are a legend!

 

I added the variable $username = $lastname so there weren't any conflicts with actual last name and used your code. Works perfectly!

 

Thanks very much for the help mate, there are piles of hair sitting next to my desk .

 

I also have put the code below as well:

 

$username = $last_name;

$query = "SELECT * FROM client_details WHERE username = '$username'";
$data = mysqli_query($dbc, $query);

//initialize counter outside of the loop
$i=1;

//set a temp value that stores the last name to later modify
$temp = $username;

while(mysqli_num_rows($data) != 0) 
{
   $i++;
   $username = $temp.$i;
   $query = "SELECT * FROM client_details WHERE username = '$username'";
   $data = mysqli_query($dbc, $query);
}

$query = "INSERT INTO client_details (username, fname, lname, email)" .
   "VALUES ('$username', '$first_name', '$last_name', '$payer_email')";

$result = mysqli_query($dbc,$query)
   or die ('Error querying database');

[cunoodle2]

I'm not much of legend on here yet ;-).  I started off in your boat awhile back.  Keep up the practice and you will be great at this.

 

It's not really a big deal but you don't really need the "temp" value any more then based upon how you are doing this.  This just cuts out a little code...

 

<?php
$username = $last_name;

$query = "SELECT * FROM client_details WHERE username = '$username'";
$data = mysqli_query($dbc, $query);

//initialize counter outside of the loop
$i=1;

while(mysqli_num_rows($data) != 0)
{
   $i++;
   $username = $last_name.$i;
   $query = "SELECT * FROM client_details WHERE username = '$username'";
   $data = mysqli_query($dbc, $query);
}

$query = "INSERT INTO client_details (username, fname, lname, email)" .
   "VALUES ('$username', '$first_name', '$last_name', '$payer_email')";

$result = mysqli_query($dbc,$query)
   or die ('Error querying database');
?>

 

Few things..

1.  If this topic is solved then hit that "solved" button at the bottom of the page to prevent other users from having to open it.

2.  Try to force yourself into php PDO statements.  They are sooo much more secure but a little harder to learn.

 

Good luck.