SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql do an increment based on country code

    Hi Guys,

    I have this table built like this, which assigns a unique articleId starting with zero for each user.

    Code:
    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:

    Code:
    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):

    Code:
    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.




  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    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....
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Nordy View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •