| MySql do an increment based on a php variable
Current results | 
Desired results |
I have this table built like this, which assigns a unique articleId starting with zero for each user.
CREATE TABLE `articles` (
`artcId` INT(10) NOT NULL AUTO_INCREMENT,
`artcUserId` INT(10) NOT NULL DEFAULT '0',
`artcStackId` INT(10) NOT NULL DEFAULT '0',
`artcTitle` VARCHAR(200) NULL DEFAULT NULL,
PRIMARY KEY (`artcUserId`, `artcId`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;
There also is a part which assigns a running serial number (artcStackId) to each article. You'll see in the code.
Thing is, is it somehow possible to assign a artcStackId based on the country of the user? That country code will come from php.
Eg: If it's US then start from 10001+1, if its UK start from 20001+1, if its CA then start from 30001+1 and on & on.
Will it be possible to do this?
My current SQL query goes as follows:
insert into articles (artcUserId,artcStackId,artcCountry,artcTitle) select 4,IFNULL((MAX(artcStackId)+1) ,0),'US','Hello World' FROM articles;
But the way I want it it'll have to go like (this is just an example sql):
insert into articles (artcUserId,artcStackId,artcCountry,artcTitle) select 4,IF artcCountry = 'US' then(selct MAX(artcStackId)+1 where artcCountry = 'US'),'US','Hello World' FROM articles;
Any idea how this can be done? Thanks. |
 cdruGo ColtsPremium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7 | Instead of the IF statement, just do something like SELECT MAX(artcStackID) + 1 WHERE artcCountry = inserted_values_country
Maybe there are other design considerations that aren't mentioned that are relevant, but such a ID scheme seems to be poorly suited. What happens when one of the countries has more than 10000 articles? Or if you have more than 9 countries? Why not just keep the field auto-incrementing across all rows? If you need to get a "stack" of articles based on a country, just restrict down the query with a WHERE clause.
By doing this, the table becomes more simplified. Inserts can be done quicker as you aren't having to do multiple lookups, comparisons, and computing the max value on every insert. |
