MySql do an increment based on country code

[Nordy]

Hi Guys,

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.

[StarLion]

You... could CASE it, but that would be a rather ugly looking query once you started getting more and more country codes.
It also limits your database rather heavily (10,000 articles max per country).
You shouldnt need multiple unique keys for a single item. Perhaps if you explain what you mean by a Stack ID....

[r937]

Thing is, is it somehow possible to assign a artcStackId based on the country of the user?

no

stop assigning multiple random ids to stuff

if you're going to assign an auto_increment, one (overall) should be enough -- if it isn't, i would do a re-think