Jump to content
xisto Community
ginginca

Linking Two Tables

Recommended Posts

I have two tables in a database. userid and usercreditsIn userid I have all the fields that identify the user (name, address etc.)In my usercredits table I have the following fields:IDNUMProgramNameCamp#CompletionDateAmountDatePmtSentPmtMethodCommentscreditIDWhat I would like to understand about my table structure, is how do I associate my credits tothe correct user. If I have done this correctly, there is also a field in the first table that is called IDNUM where each user has a unique number to identify who they are.When someone is logged into the site I need to display the values from the usercredits table that are only THEIR credits.Am I starting this off correctly?

Share this post


Link to post
Share on other sites

Yes, you're perfectly right.In your user-table you would probably have one entry per user only, so that the ID is a unique value.Then, when the user logs in you could get all the entries from the usercredits table that contain his userid ("SELECT Amount FROM usercredits WHERE IDNUM = 'user'") and sum them up or whatever. It might be a good idea to keep count of the current total credit number in a separate table and rename the current "usercredit" table to "usertransactions" or so. This would save you the extra effort of summing up the credits total.Seems fine to me otherwise,Ruben

Share this post


Link to post
Share on other sites
You need one field unique field (where the value is unique) in both table so that they can be linked together. For example you can use IDNUM as your primary key in both tables and link the tables using that field.

Share this post


Link to post
Share on other sites

You need one field unique field (where the value is unique) in both table so that they can be linked together. For example you can use IDNUM as your primary key in both tables and link the tables using that field.

 


Yes I set it up as the primary key in both. So it sounds like I'm on the right track here.

Share this post


Link to post
Share on other sites

do you guys have any tutorials on this as id like to learn some of this


There's tons of them in the web. Type in your favourite search engine: "SQL tutorial" and you'll get bunch of really good tutorials with lots of examples. Once you know the basics the DBMS's developer's online reference manual is probably the most handy tool to use. I've been doing database applications for years now but still need to check things from dev.mysql.com/doc regularly.
Edited by Hercco (see edit history)

Share this post


Link to post
Share on other sites

I have two tables in a database. userid and usercredits
In userid I have all the fields that identify the user (name, address etc.)

In my usercredits table I have the following fields:

IDNUM
ProgramName
Camp#
CompletionDate
Amount
DatePmtSent
PmtMethod
Comments
creditID
What I would like to understand about my table structure, is how do I associate my credits tothe correct user. If I have done this correctly, there is also a field in the first table that is called IDNUM where each user has a unique number to identify who they are.

When someone is logged into the site I need to display the values from the usercredits table that are only THEIR credits.

Am I starting this off correctly?


Ok. Your table structure looks fine. You say that you IDNUM as the common field between two tables. As long as it is unique in bothe the tables your table structure will alllow you to join the tables perfectly. Are you planning to have a row in "usercredits" for every user in userid? May be it doesn't matter, but if you don't have any row in usercredits for a particular userid, you'll have to handle the exception of "Now Rows Found".

Yes, you are starting off correctly. You can use IDNUM for joining the tables.
Edited by SP Rao (see edit history)

Share this post


Link to post
Share on other sites

So this is a one to many relationship
One user can multiple credits records in the table.

Here should be somewhat of an answer to your design:

User Table
-----------
userid (primary key)
Name
address
phone
(etc)

Credit Table
-------------
creditID (primary key)
userid (foreign key)
Camp#
CompletionDate
Amount
DatePmtSent
PmtMethod
Comments


So in order to call an entry from the credit table you must have both keys (foreign and partial primary key).

SQL would look something like this

SELECT U.name, C.completiondate, C.amount, C.datepmtsent, C.pmtmethod, C.commentsFROM users UJOIN credit C ON U.userid=C.useridORDER BY C.datepmtsent ASC

I guess that would give u all the payments made by a user. Obviously if you wanted a single payment you would have to identify the appropriate where clauses and give the correct arguements.

Lots of reading on this topic but I guess this would be as good of a place as any. I suggest that you pay particular attention to the ERD's as they will probably explain most of the text if you understand them.

http://forums.xisto.com/no_longer_exists/

Share this post


Link to post
Share on other sites

Well, as ginginca was asking for some guidance, there are a lot of stuff in the web. Somehow I feel that you don't need to search particularly for MySQL tutorials alone. It's better you learn the basica of SQL first. (May be little bit about RDBMS concepts).

 

Now, there are million sites on net which will provide you information about SQL. The one which is particularly good for beginners is http://www.sqlcourse.com/. This site offers online interactive tutorial where you can test what you are learning too.

 

Moreover, you will get info and updates on almost all well known databases MySQL, Oracle, MS SQL etc.

I think this site will definitely help you in learning the SQL basics.

 

You may also try W3Schools tutorials for SQL SQL Tutorial

 

For RDBMS concepts and data modelling, any google search will help you.

Happy programming :)

Edited by SP Rao (see edit history)

Share this post


Link to post
Share on other sites

Well, as ginginca was asking for some guidance, there are a lot of stuff in the web. Somehow I feel that you don't need to search particularly for MySQL tutorials alone. It's better you learn the basica of SQL first. (May be little bit about RDBMS concepts).

 

Now, there are million sites on net which will provide you information about SQL. The one which is particularly good for beginners is http://www.sqlcourse.com/. This site offers online interactive tutorial where you can test what you are learning too.

 

Moreover, you will get info and updates on almost all well known databases MySQL, Oracle, MS SQL etc.

I think this site will definitely help you in learning the SQL basics.

 

You may also try W3Schools tutorials for SQL SQL Tutorial

 

For RDBMS concepts and data modelling, any google search will help you.

Happy programming :)

 

Thanks for all the feedback!

Share this post


Link to post
Share on other sites

Hi guys, I have some question for you. From many articles that I've read. I only found about joining two tables. But I didn't find about joing more than two tables, three tables, and even more!.

Is joining more than two tables same as joining more that 2 tables?. I little confuse about it. If joining 2 tables we simply using query like this:

SELECT table1.column1, table1.column2, table1.column3 FROM table1, table2 WHERE table1.column2='VALUE'

Help is very appreciate! :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | 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.