ginginca 0 Report post Posted August 22, 2006 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
ruben1405241511 0 Report post Posted August 22, 2006 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
doudou 0 Report post Posted August 22, 2006 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
ginginca 0 Report post Posted August 24, 2006 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
msabas 0 Report post Posted August 27, 2006 do you guys have any tutorials on this as id like to learn some of this Share this post Link to post Share on other sites
Hercco 0 Report post Posted August 27, 2006 (edited) 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 August 27, 2006 by Hercco (see edit history) Share this post Link to post Share on other sites
msabas 0 Report post Posted August 27, 2006 I was hoping you guys had posted some here.. i tried looking up a few thru the search.. but no luck.. and yeah i guess i will have to use th eweb to look themup.. thnx Share this post Link to post Share on other sites
SP Rao 0 Report post Posted August 29, 2006 (edited) 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? 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 August 30, 2006 by SP Rao (see edit history) Share this post Link to post Share on other sites
minnieadkins 0 Report post Posted August 29, 2006 So this is a one to many relationshipOne user can multiple credits records in the table.Here should be somewhat of an answer to your design:User Table-----------userid (primary key)Nameaddressphone(etc)Credit Table-------------creditID (primary key)userid (foreign key)Camp#CompletionDateAmountDatePmtSentPmtMethodCommentsSo 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
SP Rao 0 Report post Posted August 31, 2006 (edited) 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 August 31, 2006 by SP Rao (see edit history) Share this post Link to post Share on other sites
ginginca 0 Report post Posted August 31, 2006 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
c0kr3x 0 Report post Posted September 24, 2006 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
iGuest 3 Report post Posted May 2, 2008 How to link two tables in sql using primary and foriegn key-reply by shofiya Share this post Link to post Share on other sites