Page 1 of 1
php/mysql multiple select statements
Posted: Wed Nov 09, 2011 12:46 pm
by cjkeane
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?
Re: php/mysql multiple select statements
Posted: Sat Nov 12, 2011 2:17 am
by manohoo
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?
Re: php/mysql multiple select statements
Posted: Sat Nov 12, 2011 1:40 pm
by cjkeane
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
Posted: Sat Nov 12, 2011 4:35 pm
by Celauran
Code: Select all
SELECT MAX(SUBSTRING(id, 5, 2)) FROM tablename
Just guessing.
Re: php/mysql multiple select statements
Posted: Sun Nov 13, 2011 1:12 pm
by cjkeane
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
Posted: Mon Nov 14, 2011 5:55 pm
by manohoo
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
Re: php/mysql multiple select statements
Posted: Tue Nov 15, 2011 12:44 pm
by cjkeane
i figured it out. Thanks for the tip.
Re: php/mysql multiple select statements
Posted: Tue Nov 15, 2011 1:42 pm
by Weirdan
manohoo wrote:In that case, before creating a new record:
0. lock the table