Jump to content


Photo

Relating New Rows In 2 Different Tables?


  • Please log in to reply
15 replies to this topic

#1 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 227 posts
  • LocationBirmingham UK

Posted 28 December 2012 - 07:47 PM

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!

#2 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 28 December 2012 - 07:48 PM

It doesn't make sense. Job ID should be an auto-increment primary key on the jobs table.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#3 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 227 posts
  • LocationBirmingham UK

Posted 28 December 2012 - 07:53 PM

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, 28 December 2012 - 07:53 PM.


#4 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 28 December 2012 - 08:00 PM

That seems more confusing than useful.

SELECT MAX(jobidfield) FROM jobs WHERE clientid = 7

Store the IDs as numeric only.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#5 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 227 posts
  • LocationBirmingham UK

Posted 28 December 2012 - 08:06 PM

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, 28 December 2012 - 08:12 PM.


#6 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 28 December 2012 - 08:24 PM

SELECT MAX(jobidfield) FROM jobs WHERE clientid = 7


My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#7 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 227 posts
  • LocationBirmingham UK

Posted 28 December 2012 - 08:30 PM

What does the MAX function call?

#8 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 28 December 2012 - 08:31 PM

Seriously?
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#9 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 227 posts
  • LocationBirmingham UK

Posted 28 December 2012 - 08:36 PM

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?

#10 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 28 December 2012 - 08:40 PM

Try it.
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#11 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 13,880 posts
  • LocationCheshire, UK

Posted 28 December 2012 - 08:52 PM

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

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#12 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 227 posts
  • LocationBirmingham UK

Posted 28 December 2012 - 08:53 PM

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

#13 Jessica

Jessica

    This is not my name.

  • Gurus
  • 8,982 posts
  • LocationDallas, TX
  • Age:26

Posted 28 December 2012 - 08:57 PM

:psychic:
My goal in replying to posts is to help you become a better programmer, including learning how to debug your own code and research problems. For that reason, rather than posting the solution, I reply with tips and hints on how to find the solution yourself. See below for useful links when you get stuck.

How to Get Good Help: How to Ask Questions | Don't be a help vampire
Debugging Your Code: Debugging your SQL | What does a php function do? | What does a term mean? | Don't see any errors?
Things You Should Do: Normalize Your Data | use print_r() or var_dump()
Lulz: "Functions should not have side effects." - trq

Please take a look at my new PHP/Web Dev blog: The Web Mason - Thanks!!

#14 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 227 posts
  • LocationBirmingham UK

Posted 28 December 2012 - 09:04 PM

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 by Kristoff1875, 28 December 2012 - 09:04 PM.


#15 Christian F.

Christian F.

    Advanced Member

  • Staff Alumni
  • 3,106 posts
  • LocationNorway

Posted 29 December 2012 - 09:06 AM

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.
Keeping it simple.

#16 Kristoff1875

Kristoff1875

    Advanced Member

  • Members
  • PipPipPip
  • 227 posts
  • LocationBirmingham UK

Posted 29 December 2012 - 09:50 PM

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, 29 December 2012 - 09:50 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com