Jump to content


Photo

Need help with autoincrement question


  • Please log in to reply
5 replies to this topic

#1 BluePhoenixNC

BluePhoenixNC
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 28 April 2006 - 04:15 AM

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

#2 Darkness Soul

Darkness Soul
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts
  • LocationBrazil; São Paulo.

Posted 28 April 2006 - 01:03 PM

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
(If something is wrong, please tell me. I'm learning this language. Thank you)

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 April 2006 - 06:14 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 Darkness Soul

Darkness Soul
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts
  • LocationBrazil; São Paulo.

Posted 28 April 2006 - 06:51 PM

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
(If something is wrong, please tell me. I'm learning this language. Thank you)

#5 BluePhoenixNC

BluePhoenixNC
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 28 April 2006 - 07:55 PM

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 ?

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 April 2006 - 05:45 PM

[!--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) View Post[/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) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users