[RESOLVED] MySql Field Setup

[teamfox20]

Hi there,

Can someone please exaplain to me how to link different tables as to UPDATE and PULL information when needed for a specific ID.

Example

DATABASE: OCBB

TABLES:
USERS
ID | NAME | COMPANY | ADDRESS | CITY | STATE |
1 DAN TEST INC 123 R RD ANY ANY
2 BOB ABC INC 123 L RD ANY ANY
3 TED RED INC 123 P RD ANY ANY
BILLING
ID | AMOUNT | DATE DUE |
1 12.00 1/1/2008
2 15.00 1/15/2008
3 20.00 1/31/2008

What do I need to do to connect the USERS and BILLING fields so that when I pull up USERS ID1 it will show BILLING ID1 info.

Also how do I go about updating all the fields at one time. If we add another USER ID4 how do we update BILLING to add ID4 and the amount and due date.

Any help is much appreciated.

I can be reached via AIM as well. Username: CEBCSDanny

[Rodney_H_]

It looks like your billing table needs a foreign key that relates the information within it back to the users table.

So, if your USERs table has all the clients in it, and the BILLING table has all the amounts that the users must pay, it might be as easy as adding a new field to your BILLING table: user_id.

Then, you can do a search such as:

"SELECT *
FROM billing
WHERE the user_ID = 2"

And the results will be all of BOB's bills... for example.

That is simple, but once you get the key working, you can start doing table-joins, where you query two (or more) tables at the same time.

Have a look here: http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php

It is a fantastic online tutorial that deals with this.

HTH

[teamfox20]

Thank you for the link...thats a great tutorial!!

One more question, using the first example...

Example

DATABASE: OCBB

TABLES:
USERS
ID | NAME | COMPANY | ADDRESS | CITY | STATE |
1 DAN TEST INC 123 R RD ANY ANY
2 BOB ABC INC 123 L RD ANY ANY
3 TED RED INC 123 P RD ANY ANY
BILLING
ID | USER_ID | AMOUNT | DATE DUE |
1 1 12.00 1/1/2008
2 2 15.00 1/15/2008
3 3 20.00 1/31/2008

I know how to UPDATE and HOW to INSERT...but how do you do both?

Like say USERS ID1 owers 13.00...How do you check if USER ID1 has a record in BILLING USER_ID and if they do update it or else INSERT a new record?

[Rodney_H_]

You may have to do two queries in that case, which is fine. The important thing is that your DB is normalized and that you are starting to build the relationships between bits of data in the different tables. Doing multiple queries, one to find something out, and then another to either delete or update a record is fine...

Oh, and I suggest adding another field to your BILLING table: paid_date

So, that way, if you do collect a payment, you can mark the exact payment date in your DB and then, when looking for outstanding bills, you can check to make sure that the ones in your result-set have not been paid/do not have a value in the paid_date field.

hope that helps...