dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
872
share rss forum feed


Nordy

join:2007-10-20
kudos:1

MySql do an increment based on a php variable


Current results

Desired results
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.


cdru
Go Colts
Premium,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.



Nordy

join:2007-10-20
kudos:1

This proved to be rather cumbersome. So they whole things been changed.