[SOLVED] insertion problem

[kr3m3r]

Hi guys, I'm trying to automate the way in which user ID's are generated for my site.  I want it to be done using the first initial of the User's First Name, the first 8 letters of their last name, and here is the tricky part, a number, that gets incremented if a UserID already exists with that combination.

i.e. John Doe -> JDoe1, if another John Doe were added to the table, they would go in as JDoe2, and the next would be JDoe3 and so on.

 

I know I can use

strlen(FirstName,0,1).strlen(LastName,0,

for the Name portion.  What I'm having a really hard time dealing with is how I can get the number portion to work.

 

I've tried if statements:

$TestUUserID = (substr($UFName,0,1).substr($ULName,0,."1");
$query = "select * from User where UserID = '".$TestUUserID."'";
$result = mysql_query($query);
echo '<br />query: '.$query.'<br />result: '.$result.'<br />';
if(result)
{
$queryb = "insert into User values ('".$TestUUserID."','".$UFName."','".$ULName."','".$UAddress."','".$UCity."','".$UState."','".$UZip."','".$UEmail."','".$UPhone."','".$UAdmin."','".$UPassword."')";
$resultb = mysql_query($queryb);
}

if(result)
{echo 'if 1<br />';
$TestUUserID = (substr($UFName,0,1).substr($ULName,0,."2");
$query = "select * from User where UserID = '".$TestUUserID."'";
$result = mysql_query($query);
echo '<br />query: '.$query.'<br />result: '.$result.'<br />';
$queryb = "insert into User values ('".$TestUUserID."','".$UFName."','".$ULName."','".$UAddress."','".$UCity."','".$UState."','".$UZip."','".$UEmail."','".$UPhone."','".$UAdmin."','".$UPassword."')";
$resultb = mysql_query($queryb);
}

 

and I've tried looping:

$i=1;
while(result || $i<9)
{
$TestUUserID = (substr($UFName,0,1).substr($ULName,0,."'".$i."'");
$query = "select * from User where UserID = '".$TestUUserID."'";
$result = mysql_query($query);

$i++;
}

and I'm stumped.

Any help would be really appreciated.

-Robb

[Ken2k7]

How about creating the table like this:

<?php
mysql_query("CREATE TABLE memberID (realName varchar(255), IDname varchar(255), IDnum mediumint()");
?>

 

Then you can just loop through there and see if the name John Doe exists:

 

<?php
$name = "John Doe";
$idNum = 1;
$sql = mysql_query("SELECT * FROM memberID") or die(mysql_error());
while ($row = mysql_fetch_assoc($sql))
   if ($row['realName'] == $name)
      if ($row['IDnum'] > $idNum) $idNum = $row['IDnum'];
// at the end, $idNum will have the next available ID number.

[zq29]

You could pull the highest one from the database with a regular expression:

SELECT `UserID` FROM `User` WHERE `UserID` REGEXP '^$name[0-9]*$' ORDER BY `UserID` DESC LIMIT 1

Then check if it has a number on the end, and then take it from there...

[Ken2k7]

You could pull the highest one from the database with a regular expression:

SELECT `UserID` FROM `User` WHERE `UserID` REGEXP '^$name[0-9]*$' ORDER BY `UserID` DESC LIMIT 1

Then check if it has a number on the end, and then take it from there...

arghs, I really need to study regular expressions. Those are so confusing! Would've made my codes much shorter.

[kr3m3r]

OK tried it:

New code is:

$UUserID = (substr($UFName,0,1).substr($ULName,0,);
$query = "Select UserID from User where UserID regexp '".$UUserID."' order by UserID desc limit 1";
$result = mysql_query($query);
echo '<br />Here is the query: '.$query;
echo '<br />Here is the result: '.$result;

 

here is what echos back to the browser:

Here is the query: Select UserID from User where UserID regexp 'RPark' order by UserID desc limit 1

Here is the result: Resource id #2

 

How do I get somthing other than Resource id #2?  It seems that the regexp is on the right track, but I can't get it to work.

Thanks again for the help.

-Robb

[xyn]

a function to use...

function  create_username($fname, $lname)
{
$part_1 = $fname{0};	# gets the first letter
$part_2 = $substr($lname, 0, ;	#gets the first 8 letter of last name

$limit = 3; #limit the alount of number added to a username
for($i=0; $i<$limit; $i++)	$key.=rand(0,9);

return $part_1 . $part_2 . uniqid($key);
}

 

use this by...

create_username($first_name, $last_name);

hope it works.