Hi everyone,
I have an id number in a table shown as 000111-AK. 0001 is the 4 digit counter, 11 is the year (2011) and -AK is random.
whats the best way to find the the highest year in the id, then increment the counter by 1? Any ideas?
php/mysql multiple select statements
Moderator: General Moderators
Re: php/mysql multiple select statements
Why not break it down into 3 fields:
id (INT(4), AUTO INCREMENT, primary key)
year(INT(4))
code(varchar(2))
Much cleaner and more manageable, don't you think?
id (INT(4), AUTO INCREMENT, primary key)
year(INT(4))
code(varchar(2))
Much cleaner and more manageable, don't you think?
Re: php/mysql multiple select statements
yes it would be more manageable, however the problem with that is, once a new year rolls around, the counter needs to be reset to 0001, so it can't be autonumber. I have tried that route. Any other suggestions?
Re: php/mysql multiple select statements
Code: Select all
SELECT MAX(SUBSTRING(id, 5, 2)) FROM tablenameRe: php/mysql multiple select statements
i've tried that already. yes it gets the max of the year, but i also need to get the max of the first 4 digits and increment it.
Re: php/mysql multiple select statements
In that case, before creating a new record:
1. get the last record from the table
2. in PHP explode the field into 3 pieces
3. Prepare each piece as desired
4. Insert the new record
1. get the last record from the table
2. in PHP explode the field into 3 pieces
3. Prepare each piece as desired
4. Insert the new record
Re: php/mysql multiple select statements
i figured it out. Thanks for the tip.
Re: php/mysql multiple select statements
0. lock the tablemanohoo wrote:In that case, before creating a new record: