Creating a Sequential ID Number

[cwfent]

I was wondering if anyone could help me out with this problem. I have a script that has a bunch of code in it. For some reason the coder decided to use a hex for a id number (it's just a member number, nothing secret) anyway the code section that I want to change is this :
[code=php]
function createUniqueID( $table, $column)
{
$maxTries = 10;

while(1)
{
if($maxTries <= 0)
return false;

$uniqueID = substr(md5(uniq id(rand(), true)), 0, 8);

// check if this token does not exist in the table already
$sql = "select $column from $table where $column="._q($u niqueID);
$rs = (directory where code is located)::execu te($sql, __FILE__, __LINE__);
if(!$rs)
{
showMsg(L_G_DBE RROR, 'error');
return false;
}

if($rs->EOF)
return $uniqueID;

$maxTries--;
}

return false;
}
[/code]
I just want a 5 digit sequential number. Can someone tell me how to change this. I realize that auto_increment would work in the db, but, there is so much code that interrelates in the script I don't believe that removing this string would work properly. Thanks for any help.

[JeremyMiller]

Your coder didn't use hex directly -- that's coming from the hashing function md5 which changes substantially as the input changes. If it's not something that has to be hidden why not add a 5 digit number to the db id?

For example, if you consistently add 50000 to the id, the number will always be 5 digits (unless your number of members causes it to go to 6 digits, of course).

[cwfent]

Your coder didn't use hex directly -- that's coming from the hashing function md5 which changes substantially as the input changes. If it's not something that has to be hidden why not add a 5 digit number to the db id?

For example, if you consistently add 50000 to the id, the number will always be 5 digits (unless your number of members causes it to go to 6 digits, of course).

Hi Jeremy,

Thank you for your response. My problem is that my member ids are coming like "3e45jk1p" what I want is for them to start at 1 and proceed sequentially from there. I don't necessarily need each id to be 5 digits long. I would just expect that I would not need to exceed 5 digits.

[JeremyMiller]

Oh, then either:

1) Use the current autoincrement field's value, or
2) create a new autoincrement field and use it's value.

If you're just using the value of the field, then #1 is the preferred way as #2 creates unnecessary duplication.

[cwfent]

So do I do that by just saying "$uniqueID=auto _increment" ? If so What if I want to start at ID, say 1001, how would I do that?

[JeremyMiller]

Well, you have to get the autoincrement field from the database and use that. If you want to start at some number, then I'd recommend using the addition that I recommended above in case there are any DB tables which rely on the current values in your database.

[cwfent]

Ok, I'll just start at 1 then. I appreciate the help Jeremy, but, I'm about as lost as I can be. Given that I have the above code now, what do I do to change it?

[JeremyMiller]

If you provide the database table structure, I could be of greater help there. PHPMyAdmin is available (or installable) in most places and exports the DB structure very nicely.

[pbmods]

Heya, CW.

Have a look at this article.

[cwfent]

Thanks Jeremy. There are 32 tables on the db. The only 2 that I think that are relevant are the account table and the user table. The user table is basically the account table plus extra info for address, etc. Here is the structure for the account table:
[code=mysql]
CREATE TABLE `wd_g_accounts` (
`accountid` varchar(8) NOT NULL default '',
`name` varchar(100) NOT NULL default '',
`description` text,
`dateinserted` datetime NOT NULL default '0000-00-00 00:00:00',
`rstatus` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`accountid`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `IDX_wd_g_accou nts1` (`accountid`)
) TYPE=MyISAM;[/code]

[pbmods]

Heya, CW.

Please use CODE tags when posting source code:

&#91;CODE=My SQL]
MySQL code goes here.
&#91;/CODE]

[cwfent]

Heya, CW.

Please use CODE tags when posting source code:

[CODE=MySQL]
MySQL code goes here.
[/CODE]

Oops ,sorry, I forgot the tag on that one. What confuses me is that the table structure is already set up and has data in it. I am just trying to figure out what to do with the php code string that creates the id.

[pbmods]

Heya, CW.

Try running this SQL:
[code=mysql]
ALTER
TABLE
`wd_g_accounts`
MODIFY
`accountid`
SMALLINT(5)
UNSIGNED
ZEROFILL
NOT NULL
AUTO_INCREMENT;
[/code]

Then you don't ever have to worry about generating an account number; MySQL will take care of all the work for you.

[cwfent]

Heya, CW.

Try running this SQL:
[code=mysql]
ALTER
TABLE
`wd_g_accounts`
MODIFY
`accountid`
SMALLINT(5)
UNSIGNED
ZEROFILL
NOT NULL
AUTO_INCREMENT;
[/code]

Then you don't ever have to worry about generating an account number; MySQL will take care of all the work for you.

Ok, so if I do that what happens with the code? It is part of a sales tracking application. If I bypass the code will the tracking still work?

[pbmods]

Heya, CW.

Oops. My mistake; you need a MEDIUMINT:
[code=mysql]
ALTER
TABLE
`wd_g_accounts`
MODIFY
`accountid`
MEDIUMINT(5)
UNSIGNED
ZEROFILL
NOT NULL
AUTO_INCREMENT;
[/code]

At any rate, what will happen is MySQL will store a sequential ID number for each row. It's also zerofilled, so when you retrieve rows, the ID number will be padded to five digits:

00001,
00002,
00003
.
.
.
01336
01337
01338
.
.
.
99997
99998
99999