BluePhoenixNC Posted April 28, 2006 Share Posted April 28, 2006 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 Quote Link to comment Share on other sites More sharing options...
Darkness Soul Posted April 28, 2006 Share Posted April 28, 2006 Maybe you make a table to use with the states counter.. like..id / state / value1 va 5so, 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted April 28, 2006 Share Posted April 28, 2006 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. Quote Link to comment Share on other sites More sharing options...
Darkness Soul Posted April 28, 2006 Share Posted April 28, 2006 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 Quote Link to comment Share on other sites More sharing options...
BluePhoenixNC Posted April 28, 2006 Author Share Posted April 28, 2006 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 ? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 29, 2006 Share Posted April 29, 2006 [!--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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.