Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: insert seqential number... based on FK?

    My dear, loving* boss has changed the requirements.

    So... there are clients, in the clients table, and jobs, in the jobs table. A job has a client ID. A job also has a job number.

    Previously, the job number included the auto-increment ID from the jobs table. So client "A" may have job numbers 1, 2, 3 and 5, and client "B" would have job numbers 4 and 6. Everyone was happy.

    Except, it seems, the boss. He wants clients to have jobs that are numbered sequentially according to the client. So client "A" will have jobs 1, 2, 3, 4, 5, etc - and client "B" will also have jobs 1, 2, 3, 4, 5, etc. Of course, this new job number will be a new field and will bear no relation to the actual job ID in the database.

    I have no idea how to do this.

    When I'm adding a new job, I've now got to find the highest job number for that client and add one to it, (or use "1" if there aren't any) and insert that new number into the job number field.

    Is that easy? What's the most efficient way to do it?

    Thank you


    * pronounced "cruel and sadistic"

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in mysql, this is terribly easy

    it's in da manual under "Using AUTO_INCREMENT"

    the example given has "grp" (fish, mammal, bird) where you have client
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Sorry... I've read the manual and I still don't get it.

    This is my table:

    Code:
    CREATE TABLE `studioProjects` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `project_name` varchar(50) NOT NULL,
      `description` text NOT NULL,
      `company_id` int(10) unsigned NOT NULL,
      `project_active` tinyint(3) unsigned NOT NULL default '1',
      PRIMARY KEY  (`id`),
      KEY `company_id` (`company_id`)
    )
    I still need each project to have an auto_increment ID. How do I alter the table? Like:

    Code:
    ALTER TABLE studioProjects(
    PRIMARY KEY  (company_id, id)
    )
    ?

    And then... how do I get it out? Like...

    Code:
    SELECT id, project name, ???? AS projectCode FROM studioProjects

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that ALTER won't work, because the table already has a primary key

    i would simply re-create the table properly

    if you have data that you cannot lose, create a separate table, and load it from your existing table, then drop your existing table and rename the new one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Rudy: I think that your suggestion will simply get back to:
    Quote Originally Posted by Spudhead
    Previously, the job number included the auto-increment ID from the jobs table. So client "A" may have job numbers 1, 2, 3 and 5, and client "B" would have job numbers 4 and 6. Everyone was happy.
    Spudhead: Based on the revised update to the altered changes:
    Quote Originally Posted by Spudhead
    Except, it seems, the boss. He wants clients to have jobs that are numbered sequentially according to the client. So client "A" will have jobs 1, 2, 3, 4, 5, etc - and client "B" will also have jobs 1, 2, 3, 4, 5, etc. Of course, this new job number will be a new field and will bear no relation to the actual job ID in the database.
    Now, it would appear that you realize just how bad an idea this is, so I won't belabor that point.

    How important is it to prevent "gaps" in the numbering when a job is deleted? If it is important, how does the boss want to deal with those gaps?

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    Rudy: I think that your suggestion will simply get back to...
    my suggestion was intended to move towards the scenario where client "A" will have jobs 1, 2, 3, 4, 5... and client "B" will also have jobs 1, 2, 3, 4, 5...

    ... as requested

    i do not see why my suggestion will not work, pat

    perhaps you could explain it to me?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry Rudy, I keep forgetting that you use the ISAM data engine (MyISAM) of MySQL instead of Inno-Db. The ISAM file handler in MySQL does allow AUTO_INCRMENT to behave the way that Rudy seems to be thinking.

    -PatP

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "seems to be thinking" indeed

    thanks for making me sound so flakey
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No problem!

    I had to guess at what code you meant to imply, which is why I hadn't realized that you were using MyISAM. Since I was guessing at what you meant to type as an example, I also had to guess at what you were thinking... I guess.

    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you need to improve your reading skills

    i was very specific in post #2 -- the manual, under "Using AUTO_INCREMENT"

    the example is very clear, and uses "grp" where spudhead wants to use client
    For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

    CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
    );

    INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale '),
    ('bird','ostrich');

    SELECT * FROM animals ORDER BY grp,id;

    Which returns:

    +--------+----+---------+
    | grp | id | name |
    +--------+----+---------+
    | fish | 1 | lax |
    | mammal | 1 | dog |
    | mammal | 2 | cat |
    | mammal | 3 | whale |
    | bird | 1 | penguin |
    | bird | 2 | ostrich |
    +--------+----+---------+
    "seems to be thinking", indeed

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The part that I had problems interpreting was from post #4
    Quote Originally Posted by r937
    i would simply re-create the table properly
    -PatP

  12. #12
    Join Date
    Jan 2002
    Posts
    189
    Um... pardon?

    So... I make my table like this:

    Code:
    CREATE TABLE 'studioProjects2' (
      'id' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
      'client_id' INTEGER UNSIGNED NOT NULL,
      'project_name' VARCHAR(45) NOT NULL,
      'description' TEXT,
      'project_active' INTEGER UNSIGNED NOT NULL DEFAULT 1,
      PRIMARY KEY ('id', 'client_id')
    )
    ENGINE = MyISAM;
    If I then stick some data in it:

    Code:
    INSERT INTO studioProjects2 (client_id, project_name) VALUES (1, 'Client 1 Project 1')
    INSERT INTO studioProjects2 (client_id, project_name) VALUES (1, 'Client 1 Project 2')
    INSERT INTO studioProjects2 (client_id, project_name) VALUES (2, 'Client 2 Project 1')
    INSERT INTO studioProjects2 (client_id, project_name) VALUES (2, 'Client 2 Project 2')
    ... and look at the resulting data, my id field is sequential, irrespective of client_id.

    Project 1 for client 2 needs to have... somewhere... a field with a value of 1, because it's the first project for that client.

    I guess what I'm back to asking is, how do I do this:

    Code:
    SELECT id, project name, ???? AS projectCode FROM studioProjects

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you couldn't possibly have created that table with that CREATE TABLE statement, because you used single quotes instead of backticks around all the identifiers

    also, you have the order of columns in the the PK wrong

    here, try this:
    Code:
    CREATE TABLE studioProjects2
    ( client_id INTEGER UNSIGNED NOT NULL
    , id        INTEGER UNSIGNED NOT NULL AUTO_INCREMENT
    , PRIMARY KEY ( client_id , id )
    , project_name VARCHAR(45) NOT NULL
    , description TEXT
    , project_active TINYINT UNSIGNED NOT NULL DEFAULT 1
    ) ENGINE = MyISAM
    ;
    INSERT INTO studioProjects2 (client_id, project_name) VALUES
     (1, 'Client 1 Project 1')
    ,(1, 'Client 1 Project 2')
    ,(2, 'Client 2 Project 1')
    ,(2, 'Client 2 Project 2')
    ;
    SELECT * FROM studioProjects2
    ;
    /* RESULTS */
    client_id  id  project_name        description  project_active
       1        1  Client 1 Project 1  {Memo}            1
       1        2  Client 1 Project 2  {Memo}            1
       2        1  Client 2 Project 1  {Memo}            1
       2        2  Client 2 Project 2  {Memo}            1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2002
    Posts
    189
    Busted Yeah, I went through the CREATE statement and replaced all the backticks before posting it. Didn't know if they were HTML-friendly so I thought I'd take 'em out.

    Anyway, thank you. So the order of the PK fields matters. Another day, another learning experience

Posting Permissions

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