If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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.

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:

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?

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.

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.

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.