php/mysql multiple select statements

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

php/mysql multiple select statements

Post 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?
User avatar
manohoo
Forum Contributor
Posts: 201
Joined: Wed Dec 23, 2009 12:28 pm

Re: php/mysql multiple select statements

Post 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?
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: php/mysql multiple select statements

Post 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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: php/mysql multiple select statements

Post by Celauran »

Code: Select all

SELECT MAX(SUBSTRING(id, 5, 2)) FROM tablename
Just guessing.
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: php/mysql multiple select statements

Post 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.
User avatar
manohoo
Forum Contributor
Posts: 201
Joined: Wed Dec 23, 2009 12:28 pm

Re: php/mysql multiple select statements

Post 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
cjkeane
Forum Contributor
Posts: 217
Joined: Fri Jun 11, 2010 1:17 pm

Re: php/mysql multiple select statements

Post by cjkeane »

i figured it out. Thanks for the tip.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: php/mysql multiple select statements

Post by Weirdan »

manohoo wrote:In that case, before creating a new record:
0. lock the table
Post Reply