Jump to content

Relating New Rows In 2 Different Tables?


Kristoff1875

Recommended Posts

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!

Link to comment
Share on other sites

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 by Kristoff1875
Link to comment
Share on other sites

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 by Kristoff1875
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Kristoff1875
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.