insert seqential number... based on FK?

Spudhead · #1 (**permalink**) 09-22-08, 08:34

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"

r937 · #2 (**permalink**) 09-22-08, 09:41

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

Spudhead · #3 (**permalink**) 09-22-08, 12:45

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

r937 · #4 (**permalink**) 09-22-08, 12:52

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

Pat Phelan · #5 (**permalink**) 09-22-08, 13:50

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

r937 · #6 (**permalink**) 09-22-08, 14:31

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?

Pat Phelan · #7 (**permalink**) 09-22-08, 17:23

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

r937 · #8 (**permalink**) 09-22-08, 19:40

"seems to be thinking" indeed

thanks for making me sound so flakey

Pat Phelan · #9 (**permalink**) 09-23-08, 07:17

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

r937 · #10 (**permalink**) 09-23-08, 07:36

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

Quote:

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

Pat Phelan · #11 (**permalink**) 09-23-08, 10:10

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

Spudhead · #12 (**permalink**) 09-24-08, 10:43

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

r937 · #13 (**permalink**) 09-24-08, 10:56

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

Spudhead · #14 (**permalink**) 09-25-08, 04:28

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

Internet Services

Web Development

Digital Marketing

Consumer Tech