handling duplicates not your normal data pull

[severndigital]

I have a database that i would like to generate a username for.

 

fields are

 

firstname

lastname

username

 

I want to pull the first initial and the lastname as the user name  (this is finished already)

 

But then I would like to check the username against the rest of the database to make sure the username is not in use already.

 

if it is in use i would like to generate a random 3 digit number and add it to the username

 

then instert the new username into the dbase.

 

I would also like to report on the following

 

1. how many records were processed

2. how many were duplicated and therefor changed.

 

The code I have written works perfectly for generating and inserting the usernames however, I can't seem to grasp the right way to check and handle the duplicate entries.

 

here is what I have so far. I know it's broken out quite a bit and I could consolidate some code, but this is the way my company requires me to code.

 

<?php

function duplicateEntry($username,$id){

$dups = 0; //counter for this function
//this function will handle duplicate entries and fix them as needed.

$sql = "SELECT id,username FROM origDbase WHERE username='$username'";
	$pull = mysql_query($sql)or die(mysql_error());
	$chk = mysql_num_rows($pull)or die(mysql_error());
if($chk > 0){ 
	while($r = mysql_fetch_array($pull)){
		//should be the info of the duplicate entry
		$id			= $r[0];
		$username 	= $r[1];

		$ext		= rand(100,999);

		$username 	= '' . $username . '' . $ext . '';

		//upadate the username

		$sql = "UPDATE origDbase SET username='$username' WHERE id='$id' LIMIT 1";
			$push = mysql_query($sql)or die(mysql_error());

		$dups = 1;
	}// end while loop
}else{
		$sql = "UPDATE origDbase SET username='$username' WHERE id='$id' LIMIT 1";
		$push = mysql_query($sql)or die(mysql_error());

		$dups = 2;
}//end if statement

$result = $dups;
return $result;
}//end duplicateEntry($username,$id)

function generateUsername(){

/*
This function will do the following

1. pull firstname and lastname from database
2. generate username and first initial and lastname
3. check for duplicate of username within the database and,
4. if duplicate is found, apply random 3 digit extension 
*/

$sql = "SELECT id,firstname,lastname FROM origDbase";

$pull = mysql_query($sql)or die(mysql_error()); 
$totalrecords = mysql_num_rows($pull);

//for reporting purposes, lets add some counters.
	$counter = 0;
	$dupcounter = 0;

while($r = mysql_fetch_array($pull)){

	$id	= $r['id'];
	$firstname	= $r['firstname'];
	$lastname	= $r['lastname'];

  	//generate username
	$initial = strtoupper(substr($firstname,0,1)); //grabs first initial and capitalized if not.

	$lastinitial = strtoupper(substr($lastname,0,1)); //grabs last initial and capitalized if not.
	$lastnamerest =	strtolower(substr($lastname,1)); //grabs last name and forces lowercase

	$lastname = '' . $lastinitial . '' . $lastnamerest . ''; //generate CAPS first Letter lower case the rest.

			//put them together
	$username = '' . $initial . '' . $lastname . '';

	//check for duplicates
	$dupcheck = duplicateEntry($username,$id);

		if($dupcheck == 1){
			$dupcounter = $dupcounter + 1;
		}elseif($dupcheck == 2){			
		}//end if statment
	$counter = $counter + 1;


}//end while loop

// now lets return some information.
	$result = array($totalrecords,$counter,$dupcounter);
return $result;
}//end generateUsername()

?>

 

any help would be great.

 

I get no errors from php or mysql (yes reporting is turned on). Just a blank page.

 

if i take out anything regarding duplicate entries (i.e. any reference to the first function) the script works perfect.

 

thanks in advance,

 

-c

[fenway]

There's NO WAY your company requires

 

}//end if statment

 

Anyway, you should get all "root" usernames that match first in an sql query, and then generate a random one, and check it against that list.

[severndigital]

Anyway, you should get all "root" usernames that match first in an sql query, and then generate a random one, and check it against that list.

 

That's what i thought to, but it doesn't seem to be working.

 

here is my function for check for duplicate entries.

 

function duplicateEntry(){

$dupcount = 0; // start counter

$sql = "SELECT id,username FROM origDbase";
	$pull = mysql_query($sql)or die(mysql_error());

while($r = mysql_fetch_array($pull)){

	$id = $r['id'];
	$username = $r['username'];


//This area seems to be the hang up

	$sql = "SELECT id,username FROM origDbase WHERE username='$username'";
		$pull = mysql_query($sql)or die(mysql_error());

	//check for duplicates
		$check = mysql_num_rows($pull)or die(mysql_error());
//this area above seems to be the hang up

	//do something if it's duplicated

	if($check > 1){

		//generate random string of 3 digit numbers
	$ext 	= rand(100,999);

		//apply random to username 

	$username = '' . $username . '' . $ext . '';

		//now update database

	$updatesql = "UPDATE origDbase SET username='$username' WHERE id='$id' LIMIT 1";
		$push = mysql_query($updatesql)or die(mysql_error());

	$dupcount = $dupcount + 1;			

	}//end if

}// end while
$result = $dupcount;	
return $result;
}//end duplicateEntry()

 

When i run this function the browser just loads forever and nothing ever happens to the database.

 

after a little be of debugging I seem to have narrowed down the hang up and i have commented in the php code. if i comment out that code the function runs

 

 

 

 

any suggestions on how to improve or correct this function would be greatly appreciated.

 

Thanks,

 

Chris

[severndigital]

***Update*** i got it work, but the duplicates query takes literally 4 minutes to process.

 

could be a server issue, I'm going to put it on a fast machine and see if that solved the speed problem