Creating a Sequential ID Number

[cwfent]

And this solution will work? This is an affiliate sales script. When a member signs up the signup form calls up this code which assigns an id then a seperate code inserts it into the db. If I alter the db structure the code still won't produce the hash id?

I think this is the code that inserts it:

[code=php]

// insert account into db
// $aid = QCore_Sql_DBUni t::createUnique ID('wd_g_accoun ts', 'accountid');
$aid = QCore_Sql_DBUni t::generateID(' seq_wd_g_accoun ts', 1);
$sql = 'insert into wd_g_accounts (accountid, name, description, dateinserted, rstatus) '.
'values '.
'('._q($aid).', '._q($aname).', '._q($adescript ion).','.sqlNow ().','._q($arst atus).')';
$ret = QCore_Sql_DBUni t::execute($sql , __FILE__, __LINE__);

[/code]

[pbmods]

Heya, CW.

If you want something a bit more random, you can hash the ID.

Does the referral code have to be 5 characters?

[cwfent]

No, I don't want a random number at all. It was set up by the programmer to be random. I want it sequential. And it doesn't have to be five digits. I would like the id to start at 1 and proceed from there. My question is how do I change the script from assigning a random hash id and make it produce a sequential id.

[JeremyMiller]

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.

NOTE: If you do this, you will destroy all current ids.

Your table does not include an auto_increment field, so you should add one in there and use that as the id. Then, it's not necessary for you to insert the id. Given that you've mentioned a couple of times that other stuff depends on this id, changing it should probably be done only by someone with full access to the code who can go through and make sure that all code is properly updated. Otherwise, you may break bits and pieces of the code and then have to go through and fix the errors as you discover them.

[cwfent]

NOTE: If you do this, you will destroy all current ids.

Your table does not include an auto_increment field, so you should add one in there and use that as the id. Then, it's not necessary for you to insert the id. Given that you've mentioned a couple of times that other stuff depends on this id, changing it should probably be done only by someone with full access to the code who can go through and make sure that all code is properly updated. Otherwise, you may break bits and pieces of the code and then have to go through and fix the errors as you discover them.

That is the whole problem. The programmer is now "too busy" to help me out, so I really heave now way of doing a db change. That is why I asked about php code. I know I can have the db do a sequential id, what I am asking for is php code to do it, to change the code that sets up the hash random to just do sequential. Is there no way to do this?

[pbmods]

Heya, CW.

To implement the sequential ID, make sure that you never specify an `accountid` when you are INSERTing rows. If you specify a NULL value for the `accountid` field, MySQL will use the AUTO_INCREMENT value.

As an example:
[code=php]
$sql = "
INSERT
INTO
`wd_g_accounts`
(
`accountid`,
`name`,
`description`,
`dateinserted`,
`rstatus`
)
VALUES
(
NULL,
'{$name}',
'{$description} ',
NOW(),
'{$rstatus}'
)";
[/code]

Note that by specifying NULL as the value for the `accountid` field, you are telling MySQL to use the sequential ID.

[cwfent]

Heya, CW.

To implement the sequential ID, make sure that you never specify an `accountid` when you are INSERTing rows. If you specify a NULL value for the `accountid` field, MySQL will use the AUTO_INCREMENT value.

As an example:
[code=php]
$sql = "
INSERT
INTO
`wd_g_accounts`
(
`accountid`,
`name`,
`description`,
`dateinserted`,
`rstatus`
)
VALUES
(
NULL,
'{$name}',
'{$description} ',
NOW(),
'{$rstatus}'
)";
[/code]

Note that by specifying NULL as the value for the `accountid` field, you are telling MySQL to use the sequential ID.

Ok, soooo, if I change line 10 of the code to say "$uniqueid=NULL " it will then auto increment?

[pbmods]

Heya, CW.

Ok, soooo, if I change line 10 of the code to say "$uniqueid=NULL " it will then auto increment?

That should work; when the SQL query runs, you will be setting `accountid` to '', which is valid and will use the AUTO_INCREMENT value.

[cwfent]

Heya, CW.



That should work; when the SQL query runs, you will be setting `accountid` to '', which is valid and will use the AUTO_INCREMENT value.

BINGO, thanks pb & Jeremy for all of your help.

[pbmods]

Heya, CW.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)