473,320 Members | 1,840 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Creating a Sequential ID Number

14
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 :
Expand|Select|Wrap|Line Numbers
  1. function createUniqueID($table, $column)
  2. {
  3. $maxTries = 10;
  4.  
  5. while(1)
  6. {
  7. if($maxTries <= 0)
  8. return false;
  9.  
  10. $uniqueID = substr(md5(uniqid(rand(), true)), 0, 8);
  11.  
  12. // check if this token does not exist in the table already
  13. $sql = "select $column from $table where $column="._q($uniqueID);
  14. $rs = (directory where code is located)::execute($sql, __FILE__, __LINE__);
  15. if(!$rs)
  16. {
  17. showMsg(L_G_DBERROR, 'error');
  18. return false;
  19. }
  20.  
  21. if($rs->EOF)
  22. return $uniqueID;
  23.  
  24. $maxTries--;
  25. }
  26.  
  27. return false;
  28. }
  29.  
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.
Sep 10 '07 #1
24 9223
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).
Sep 10 '07 #2
cwfent
14
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.
Sep 10 '07 #3
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.
Sep 10 '07 #4
cwfent
14
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?
Sep 10 '07 #5
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.
Sep 11 '07 #6
cwfent
14
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?
Sep 11 '07 #7
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.
Sep 11 '07 #8
pbmods
5,821 Expert 4TB
Heya, CW.

Have a look at this article.
Sep 11 '07 #9
cwfent
14
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE `wd_g_accounts` (
  2.   `accountid` varchar(8) NOT NULL default '',
  3.   `name` varchar(100) NOT NULL default '',
  4.   `description` text,
  5.   `dateinserted` datetime NOT NULL default '0000-00-00 00:00:00',
  6.   `rstatus` tinyint(4) NOT NULL default '0',
  7.   PRIMARY KEY  (`accountid`),
  8.   UNIQUE KEY `name` (`name`),
  9.   UNIQUE KEY `IDX_wd_g_accounts1` (`accountid`)
  10. ) TYPE=MyISAM;
Sep 11 '07 #10
pbmods
5,821 Expert 4TB
Heya, CW.

Please use CODE tags when posting source code:

[CODE=MySQL]
MySQL code goes here.
[/CODE]
Sep 11 '07 #11
cwfent
14
Heya, CW.

Please use CODE tags when posting source code:

Expand|Select|Wrap|Line Numbers
  1. MySQL code goes here.
  2.  

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.
Sep 11 '07 #12
pbmods
5,821 Expert 4TB
Heya, CW.

Try running this SQL:
Expand|Select|Wrap|Line Numbers
  1. ALTER
  2.     TABLE
  3.         `wd_g_accounts`
  4.     MODIFY
  5.         `accountid`
  6.             SMALLINT(5)
  7.             UNSIGNED
  8.             ZEROFILL
  9.             NOT NULL
  10.             AUTO_INCREMENT;
  11.  
Then you don't ever have to worry about generating an account number; MySQL will take care of all the work for you.
Sep 11 '07 #13
cwfent
14
Heya, CW.

Try running this SQL:
Expand|Select|Wrap|Line Numbers
  1. ALTER
  2.     TABLE
  3.         `wd_g_accounts`
  4.     MODIFY
  5.         `accountid`
  6.             SMALLINT(5)
  7.             UNSIGNED
  8.             ZEROFILL
  9.             NOT NULL
  10.             AUTO_INCREMENT;
  11.  
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?
Sep 11 '07 #14
pbmods
5,821 Expert 4TB
Heya, CW.

Oops. My mistake; you need a MEDIUMINT:
Expand|Select|Wrap|Line Numbers
  1. ALTER
  2.     TABLE
  3.         `wd_g_accounts`
  4.     MODIFY
  5.         `accountid`
  6.             MEDIUMINT(5)
  7.             UNSIGNED
  8.             ZEROFILL
  9.             NOT NULL
  10.             AUTO_INCREMENT;
  11.  
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
Sep 11 '07 #15
cwfent
14
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. // insert account into db
  3. //    $aid = QCore_Sql_DBUnit::createUniqueID('wd_g_accounts', 'accountid');
  4.     $aid = QCore_Sql_DBUnit::generateID('seq_wd_g_accounts', 1);
  5.     $sql = 'insert into wd_g_accounts (accountid, name, description, dateinserted, rstatus) '.
  6.            'values '.
  7.            '('._q($aid).','._q($aname).','._q($adescription).','.sqlNow().','._q($arstatus).')';
  8.     $ret = QCore_Sql_DBUnit::execute($sql, __FILE__, __LINE__);
  9.  
  10.  
Sep 11 '07 #16
pbmods
5,821 Expert 4TB
Heya, CW.

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

Does the referral code have to be 5 characters?
Sep 11 '07 #17
cwfent
14
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.
Sep 11 '07 #18
Heya, CW.

Try running this SQL:
Expand|Select|Wrap|Line Numbers
  1. ALTER
  2.     TABLE
  3.         `wd_g_accounts`
  4.     MODIFY
  5.         `accountid`
  6.             SMALLINT(5)
  7.             UNSIGNED
  8.             ZEROFILL
  9.             NOT NULL
  10.             AUTO_INCREMENT;
  11.  
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.
Sep 11 '07 #19
cwfent
14
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?
Sep 11 '07 #20
pbmods
5,821 Expert 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. $sql = "
  2. INSERT
  3.     INTO
  4.         `wd_g_accounts`
  5.         (
  6.             `accountid`,
  7.             `name`,
  8.             `description`,
  9.             `dateinserted`,
  10.             `rstatus`
  11.         )
  12.     VALUES
  13.         (
  14.             NULL,
  15.             '{$name}',
  16.             '{$description}',
  17.             NOW(),
  18.             '{$rstatus}'
  19.         )";
  20.  
Note that by specifying NULL as the value for the `accountid` field, you are telling MySQL to use the sequential ID.
Sep 11 '07 #21
cwfent
14
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:
Expand|Select|Wrap|Line Numbers
  1. $sql = "
  2. INSERT
  3.     INTO
  4.         `wd_g_accounts`
  5.         (
  6.             `accountid`,
  7.             `name`,
  8.             `description`,
  9.             `dateinserted`,
  10.             `rstatus`
  11.         )
  12.     VALUES
  13.         (
  14.             NULL,
  15.             '{$name}',
  16.             '{$description}',
  17.             NOW(),
  18.             '{$rstatus}'
  19.         )";
  20.  
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?
Sep 12 '07 #22
pbmods
5,821 Expert 4TB
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.
Sep 12 '07 #23
cwfent
14
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.
Sep 12 '07 #24
pbmods
5,821 Expert 4TB
Heya, CW.

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

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Jenn L | last post by:
I have a database that is pre-populated with sequential part numbers. As people reserve the parts I update a flag to show the # is no longer available. Now they want the ability to take out a...
0
by: allyn44 | last post by:
Hello, I have a situation where I have to create 20 labels for each instance of an ID-each of the 20 labels has a different test number on it but needs the same id and one of 3 project names--for...
7
by: GAVO. | last post by:
Hello every one I have a database with a form called "frmOrders" on that for I need to create a sequential number for each city apart from the original autonumber. So the table "tblorders" would...
4
by: James | last post by:
Hello there, Does anyone know how to create a sequential record number field in a query?? Thanks, James
15
by: NomoreSpam4Me | last post by:
Hi there i have a little problem with my invoice. Here it is: i have a main menu with buttons, one of my button is "Create new invoice", when click on it a form pop up so i can enter my...
2
by: Mike Kingscott | last post by:
Hi all, I'd building an app that posts to a web service. One of the things that is required in the soap header is a sequential number appended to a ref, i.e. "IGI1001", "IGI1002", etc. ...
9
by: Nooby | last post by:
New to Access here. I inherited a db that has the first column as an automatically generated sequential number. I want to bump it up, but for the life of me I can't figure out how to do it. Is...
1
maxamis4
by: maxamis4 | last post by:
Hello folks, I have two forms a parent form and a subform. The parent form is an unbound form while the subform is a form that contains all a list of what I like to call 'in stock ' phone...
9
by: Axxe | last post by:
I have searched high and low for cogent, well-explained coding to complete a project on which I have spent six months of work. I stumbled across something on this site that is close to what I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.