Jump to content

Archived

This topic is now archived and is closed to further replies.

BluePhoenixNC

Need help with autoincrement question

Recommended Posts

After getting so much help here, I am again trustfully posting a question here, please keep in mind I am not a php / mysql pro by no means, so be patient with me.
Here is the scenario: In MySql I would like to create a column called D_ID, which is supposed to be a concated value with a prefix of 'BRG" then the State and then an autoincrement value. Alltogether it should read: 'BRGVA001' and then just add a number to VA, if a second entry has been made. With another state it would start with 001 again.
This column is supposed to be unique and no duplicates allowed (naturally). Since I have the state within the db it's easy to concate that for me, but how would I make PHP count all entries starting with a particular state, like VA, find the highest Number and add one to it ?
Sounds in theory very simple but I have no clue as to how to realize that.

Thank you

Share this post


Link to post
Share on other sites
Maybe you make a table to use with the states counter.. like..

id / state / value
1 va 5

so, you read this table before insert the new entry, filter by state limit 1, take the value, sum it with 1, make the text entry (pref.state.(value+1)), record.. if sucessfult, go to your state table and do something like UPDATE table SET value=value+1 WHERE state='va' LIMIT 1..

Do you get my idea?? it may be useful ^^! but i don't sure it is the better way..

D.Soul

Share this post


Link to post
Share on other sites
First, I question the need to store "BRG" -- or any other hard-coded prefix -- in the DB. Second, the "proper" way to do this is to have the D_ID field as AUTO_INCREMENT, and then make the PK of the table a multi-column index containing ( d_id, state ). That way, the DB will hand the counter for you, as desired. You can always prepend the prefix on the way out, either in MySQL or PHP.

Share this post


Link to post
Share on other sites
Yeah fen, I agree..

But, the problem here is the states.. this way will need one table for each state.. If I understood right... is wanted to be one id_auto_inc for each state.. if I'm wrong, sorry ^^"!

So, with many auto_inc like many states.. i can't think in other way different from my way..

D.Soul

Share this post


Link to post
Share on other sites
Darkness Soul: yes you are correct, I need it for each state starting at one and counting up.
The reason why I hardcode BRG and the state into the ID is: that this is a designator for an offline program which is still being used, and to make bot records easier to read and compare.
I need in my table some way to verify the uniqueness of each record. But as far as the Counter goes, I came up with this Idea (if everything works correctly) before saving the record, count the records for the state in question, take that number and add one to it. How reliable that is, I am not sure, that's why I am here.
I believe that would bypass the need for me to actually store that "increment" number in a seperate table. The whole reason, why I came up with putting BRG into the "autoincrement" field was, that I need an incrementing Number for each state, so I figured I do it from the beginning and don't parse it into it upon display, I think that's what you sugessted, Fen..right ?

Share this post


Link to post
Share on other sites
[!--quoteo(post=369660:date=Apr 28 2006, 01:51 PM:name=Darkness Soul)--][div class=\'quotetop\']QUOTE(Darkness Soul @ Apr 28 2006, 01:51 PM) [snapback]369660[/snapback][/div][div class=\'quotemain\'][!--quotec--]
But, the problem here is the states.. this way will need one table for each state..
[/quote]
I don't understand what you're getting it -- no extra tables are required.

[!--quoteo(post=369691:date=Apr 28 2006, 02:55 PM:name=Blue Phoenix)--][div class=\'quotetop\']QUOTE(Blue Phoenix @ Apr 28 2006, 02:55 PM) [snapback]369691[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I believe that would bypass the need for me to actually store that "increment" number in a seperate table. The whole reason, why I came up with putting BRG into the "autoincrement" field was, that I need an incrementing Number for each state, so I figured I do it from the beginning and don't parse it into it upon display, I think that's what you sugessted, Fen..right ?
[/quote]
Sort of... if you're looking for a FK, my suggestion will not work, since you'd then have to store "both" pieces of the key.

You can have an incrementing number for each state either in the output, or by having a separate states table with UIDs as desired.

In general, it's a bad idea to store anything output related in the DB.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.