Relating New Rows In 2 Different Tables?

[Kristoff1875]

Season's Greetings guys,

 

I'm not sure if this is possible, i'm a semi noob so go easy on me!

 

I have 2 tables, Clients & Jobs. When I post a new Job, i've got a page that adds jobs as a new row, with the chosen Client ID, so for example if the Client ID is 'CLIENT007' and the job number is '007', In my Jobs table, I get "ClientID: CLIENT007; JobID: 007;"

 

Hope this makes sense so far. Anyway is there a way of me finding the last job number and incrementing the next job? So instead of having to enter a JobID it will do something like:

 

Client ID = CLIENT007 last JobID = 007, so next JobID is 008.

 

Bare in mind that I can't simply auto increment the job ID as I could have CLIENT002 with JobID 123.

 

Hope this makes some sort of sense!!

 

Many thanks in advanced!

[Jessica]

It doesn't make sense. Job ID should be an auto-increment primary key on the jobs table.

[Kristoff1875]

Thanks for the quick reply.

 

I have an auto-increment primary key, but the ID I want to relate is related to my filing system. For example I have client ID's, and then job id's that follow. For example CLIENT007's first job id is job 1, then the next job is job 2. If I use the auto-increment on the jobs table, then I have a situation where it could go:

 

JobID: 1; Client: CLIENT007;

JobID: 2; Client: CLIENT002;

JobID: 3; Client: CLIENT007;

 

Where CLIENT007 would never have had a Job 2.

Edited December 29, 2012 by Kristoff1875

[Jessica]

That seems more confusing than useful.

 

SELECT MAX(jobidfield) FROM jobs WHERE clientid = 7

 

Store the IDs as numeric only.

[Kristoff1875]

As I said to begin with, I'm sure it was going to seem confusing, and very difficult to explain. The basic reason for the way of storing them this way is because I already have a client job order in place and as each client has jobs in numerical order, I can relate to them really quickly. They can tell me it's job number 26 and then I go to their client folder, and locate job 26 in a list of jobs 1-30 for example. At the moment i'm using:

 

"INSERT INTO
Jobs (ClientID, JobNumber)
values
'{$_POST['ClientID']}',
'{$_POST['JobNumber']}'"

 

And simply adding the job number in a field, so i'm just trying to simplify that part slightly so it automatically gives me the next number for the JobNumber field for that ClientID

 

(I have changed that code slightly as I actually select a field from the database to find the ClientID)

 

Infact, just thinking about it, is it possible to find the highest number in a column where ClientID = ClientID and then to use a + 1 value in PHP? Just thinking I have a dropdown of all the Client's and then could pre-populate the JobID field on select of the Client dropdown, with the next number before I send it to the form?

Edited December 29, 2012 by Kristoff1875

[Jessica]

SELECT MAX(jobidfield) FROM jobs WHERE clientid = 7

[Kristoff1875]

What does the MAX function call?

[Jessica]

Seriously?

[Kristoff1875]

Very possibly a noob/blonde/tired (delete as you wish) moment, but I was assuming that it'd be used to call the maximum value of a load of choices? But seeing as I have no choices, i'm guessing it will call the highest value relating to the client ID?

 

If so how would I I go about echoing the value in a field, but + 1?

[Jessica]

Try it.

[Barand]

I'd make the primary key (clientID, JobID) and make the jobID auto_increment. That will give unique jobIDs for each client but clients can have the same jobIds

[Kristoff1875]

Getting empty values when using the MAX function, when removed it displays all the job numbers related to that client.

[Jessica]

[Kristoff1875]

You're an absolute legend Barand, that works perfectly, just what I was after. Thanks again. Is that now 2 or 3 beers I owe you when you're next in Birmingham!?

Edited December 29, 2012 by Kristoff1875

[Christian F.]

I'm just wondering what'll happen if your auditor calls you up, and tells you that after going over last years bills he noticed that job ID 1 was missing a customer number..?

 

That's one of the reasons you should consider redoing that system of yours. You could always find out what number in line a certain job was, if you have either the customer ID or the job ID, by simply counting the previous jobs in front of it.

It's a lot harder to figure out which customer the order above belongs to, if all customers have a job with ID 1.

[Kristoff1875]

Every client has a number though, they're assigned a number before a sample is even assigned. This script that i've put together is just for client's to be able to login and check progress of a job along with leaving messages so i've got a record of all interactions, rather than having some Skype, Facebook, Voicemail messages etc, they're all in one place.

Edited December 30, 2012 by Kristoff1875