[Resolved] alpha-numberica auto increment

[datona]

I know people would say that this is not the proper way but....

I need to have:

bob100
bob101
bob102
...
...
...

Is there an easy way to do this?
I would like to do a query of the database to find 'user' do something like: $user++

Say if the last 'user' was bob104
What I would like is to be able to query the database to find 'bob105' available and use that as the next user to use.

Long winded BUT I just wanted everyone to understand what I am attempting to accomplish

[tekky]

$user = 'bob';
$num = 100;
$result = mysql_query("SELECT user FROM table WHERE user='$user$num'");
if (mysql_num_rows($result) < 1) { echo "$user$num is free<br>"; }

something like that should work...

[datona]

Thanks Karl but is there anyway to integrate this so that it will keep checking (adding one to the 100) until if finds a free 'user'

[tekky]

$user = 'bob';
$result = mysql_query("SELECT user FROM table");
while($row = mysql_fetch_array($result))
{
     $i++;
     if ($row['user'] < 1) { $echo "$user$i is free<br>"; break;}
}

something like that then maybe...

[datona]

It seems to constantly choose $i plus one as being FREE but it is not.

I changed the code slightly to add $i starting at 1001 but it says that 1002 is free when it is not.

$user = 'admin';
$i = '1001'; 
$query = "SELECT acct_user FROM clients";
$result = mysql_query($query); 
while($row = mysql_fetch_array($result)) 
{ 
     $i++; 
     if ($row['acct_user'] < 1) { echo "$user$i is free<br>"; break;} 
}

it will spit back:

admin1002 is free

BUT it should have looped all the way up till it found that: admin1009 is the first in the series to be free.

[tekky]

stupid question... but is acct_user an INT?

[datona]

No, actually is is: varchar(16)

[tekky]

ack, that would be the problem....
whats the default value if a column IS free then? NULL or "" ?

[datona]

The field would be: NULL
IF the column was left blank

[tekky]

$user = 'admin';
$i = 1001; 
$query = "SELECT acct_user FROM clients WHERE acct_user != NULL"; //is SQL NOT equal <>?
$result = mysql_query($query); 
while($row = mysql_fetch_array($result)) 
{ 
     if (!isset($row['$user$i'])) { echo "$user$i is free<br>"; break;} 
     $i++; 

}

This should loop through starting at admin1001 and if the column was NULL it shouldnt have been included int he $row[] array... therefor you KNOW its free if the key for it NOT set....

it will BREAK out after the first free space is found...

(since we been working on this, Im considering doing this to keep ID #'s down to managable # on my news page now... lol)

[Weedpacket]

Oh, the joys of using SQL functions to do SQL queries.

$user = 'bob';
$result = mysql_query(
"SELECT MAX(0 + SUBSTRING(user, LENGTH('".$user."')+1))
 FROM
	table
 WHERE
	user REGEXP '^".$user."[[:digit:]]+$'"
);
if (mysql_num_rows($result) < 1)
{ echo $user.'0 is free.'
}
else
{ list($num) = mysql_fetch_row($result);
  echo $user.($num+1)." is free";
}

Not that I've tested or even run any of that, so I don't even know if it works let alone how efficient it is. It's just what I cobbled together from a few minutes with the MySQL manual.

[datona]

As I just tried this and it does not seem to work still.
I have tried to change the default start number of '$i' but each time, it tells me it is available when in true fact, it is not.

$user = 'admin'; 
$i = 1001;  

$query = "SELECT acct_user FROM clients WHERE acct_user != NULL"; //is SQL NOT equal <>? 
$result = mysql_query($query);  

while($row = mysql_fetch_array($result))  

{  

     if (!isset($row['$user$i'])) { echo "$user$i is free<br>"; break;}  

     $i++;  

}

You gave me this to try but as it is not working, I an seeing something wrong in the area of:

     if (!isset($row['$user$i'])) { echo "$user$i is free<br>"; 

I am not a guru at this but from what I can tell, is it not looking for a field (column) named admin1001 ?

[tekky]

oke if you doubt its checking admin1001 then do this...

$user = 'admin';
$i = 1001; 
$query = "SELECT acct_user FROM clients WHERE acct_user != NULL"; //is SQL NOT equal <>?
$result = mysql_query($query); 
while($row = mysql_fetch_array($result)) 
{
     $rid = "$user$i";
     if (!isset($row[$rid])) { echo "$user$i is free<br>"; break;} 
     $i++; 

}

edit you could very well be right, not sure why I did the ''s around the $user$id earlier... should work without them, but this method is more certain...

[datona]

Tekky,

I am not sure if this is a lost cause or what because even though I have double and triple checked the coding and column names, it still tells me that the next available value is available.
Example:
If I set:
$i = 1001;
It tells me that 1002 is available but it is not.
Currently the following is taken:
admin1000
admin1001
admin1002
admin1004
admin1005
admin1006
admin1007
admin1008
admin1009

So if I set "$i" to 1001, then it should tell me that 'admin1003' is available but it tells me 'admin1002' is available.

If I set "$i" to 1004, then it should tell me that 'admin1010' is available but it tells me 'admin1005' is available.

[tekky]

damn....

hrm... Im at work on a supposed day off... so I'm boycotting "work" (like I ever do anything anyways)

I'll setup a test DB and tinker with it locally

[stolzyboy]

Originally posted by Weedpacket
Not that I've tested or even run any of that, so I don't even know if it works let alone how efficient it is. It's just what I cobbled together from a few minutes with the MySQL manual.

it seems that noone ever uses your ideas Weed, it's a shame 🙂

[datona]

As I am only interested in a solution to this problem, all input is welcome if it is coming from Weedpacket of Tekky.
I tried Weedpackets example but it did not work and as I am just a newbe, allot of his code seemed too in-depth for me to understand so I opted to try to work with Tekky.

Seems like Tekky is on the right track.

Anyways, I just want to say THANKS to all that are helping me now and all those brians which have helped me in the past. I find this forum to be the most helpfull as I have learned most of my php knowledge from here.

[stolzyboy]

this wasn't to your datona persay, it was just to show that Weed puts up these complicated things that n00bs can't/don't understand right away so they don't try them, sometimes it's like they don't even read his posts, i think he is invisible, may be time for the spinny hat to return...

[tekky]

dances finally after trying a ton of things locally (if testing on my webserver thats 1600 miles away could be considered locally.... :rolleyes: )

http://tekky.xnet.org/test.php <- working example

<?
$dbh = mysql_pconnect("localhost", "tekky", "10Xt92nE!");
if (!$dbh)
{
	echo "Error connecting to database.";
	exit;
}
mysql_select_db("tekky");
$user = 'admin';
$lastval = -1;
$query = "SELECT acct_user FROM clients ORDER BY acct_user ASC"; //is SQL NOT equal <>? 
$result = mysql_query($query) or die("Query failed! - " .mysql_error()); 
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) 
{
	$val = str_replace($user,'',$row['acct_user']);
	if ($lastval != -1)
	{
		if($lastval != $val)
		{
			echo "$user$lastval is free!<br>\n";
			$found = 1;
			break;
		}
	}
	$lastval = $val+1;
}
if (!$found)
{
	echo "No gaps in database $user$lastval is the next acct_user available<br>\n";
}
?>

[barand]

Beware the perils of reusing 'gaps' in key sequences, reallocating the code of a deleted record to another.