Cross-referencing multiple tables during INSERT...
Moderator: General Moderators
Cross-referencing multiple tables during INSERT...
I have a User table that it's primary key is their user ID. I have a second table that stores additional information about the user that also uses the user ID. I have a form where the user enters the additional info and I need the programming to reference the ID in the User table so that the Info table will associate the ID with the info being stored. The issue is I'm not sure if this should be done with the session I have set up for each user after they've signed in or if I need to do the cross-reference during the INSERT.
User Table elements:
id name username password email
Info Table elements:
id item1 item2 item3
When you retrieve data from both, you type:
select Info.item1, Info.item2, Info.item3
from User, Info
where User.id = Info.id;
This is where I need help. How do you INSERT data from a form into the table where User.id = Info.id?
User Table elements:
id name username password email
Info Table elements:
id item1 item2 item3
When you retrieve data from both, you type:
select Info.item1, Info.item2, Info.item3
from User, Info
where User.id = Info.id;
This is where I need help. How do you INSERT data from a form into the table where User.id = Info.id?
Re: Cross-referencing multiple tables during INSERT...
No way to do this, not recommended either. It may be a hint that your data is not normalized properly
- jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
Re: Cross-referencing multiple tables during INSERT...
Really, how would you propose you normalise his table structure properly?josh wrote:It may be a hint that your data is not normalized properly
On topic, run the first query, then grab the ID with mysql_last_id() and run the second query using the returned ID as the ID.
Edit: Yeah your table structure is silly, you could have both those tables in one table (id, name, username, password, email, item1, item2, item3). At first glance I thought it was a one-to-many relationship (where you'd have the same ID occuring many times in the second table), not (what seems) one-to-one. Anyway, my above reply still applies.
Re: Cross-referencing multiple tables during INSERT...
I didn't even bother studying his structure to be honest, but if 100% of your use cases involve the same 2 tables, those 2 tables should be the same table, in most cases ( unless you specifically separated them to denormalize for other reasons, which still means its denormalized, not necessarily bad but if you're doing denormilization you should understand databases enough to know insert operates on one table at a time
)
Re: Cross-referencing multiple tables during INSERT...
I guess I'm not seeing why it's "silly" to have more than one table. I have one for users, one for their input, and I plan to add a couple others once I'm done with the design. To have all this information in one database may make sense, but it's much more organized to have related data stored in their own tables and call what you need later. If you have it all stored in one table, what good is coding in my SELECT example. If you can call the data from multiple tables, you should be able to INSERT it as well...and that is what I am asking anybody for advice on.
Re: Cross-referencing multiple tables during INSERT...
Violates 1st NFlkp550 wrote: When you retrieve data from both, you type:
select Info.item1, Info.item2, Info.item3
Re: Cross-referencing multiple tables during INSERT...
One-to-one relationship is perfectly acceptable. You can't insert two rows with user input in one query though, it would have to be two queries.
Last edited by Eran on Fri Feb 13, 2009 4:12 pm, edited 1 time in total.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: Cross-referencing multiple tables during INSERT...
A properly normalized structure can most definitely have a 1-1 relationship between tables. In fact, you are supposed to only group related data in a table, and not on the basis of it's relationship.
For instance,
I usually have a accounts table (specific to their account credentials), and an accounts details table (personal information).
For instance,
I usually have a accounts table (specific to their account credentials), and an accounts details table (personal information).
Re: Cross-referencing multiple tables during INSERT...
Yeah his schema is using sequential columns instead of a self joining table tho, which makes it in violation of 1nf, if you have "multiple" of something you need it to be in another table. You will have to perform 1 join, get the result of the auto increment column and use it as a foreign key in the other table.
Re: Cross-referencing multiple tables during INSERT...
John and Josh...I like how you seem to be solving this for me.
I've seen tutorials instruct similar ideas as mine, but they are always missing something.
John, it seems as though you know where I'm going with this. Is it possible you have developed something similar and could you at least guide me to the solution without giving me the code?
Josh, you seem to have an idea also. Could it be possible that you see where John is going with this? You said I'd need to do a join...all the tutorials are vague on how to implement this...could you please explain? Also, I'm fairly new to PHP...what is "violating 1 nf"?
I do appreciate you two offering your opinions and I'm not looking for anyone to solve this for me, but any advice you can give would really help.
I've seen tutorials instruct similar ideas as mine, but they are always missing something.
John, it seems as though you know where I'm going with this. Is it possible you have developed something similar and could you at least guide me to the solution without giving me the code?
Josh, you seem to have an idea also. Could it be possible that you see where John is going with this? You said I'd need to do a join...all the tutorials are vague on how to implement this...could you please explain? Also, I'm fairly new to PHP...what is "violating 1 nf"?
I do appreciate you two offering your opinions and I'm not looking for anyone to solve this for me, but any advice you can give would really help.
Re: Cross-referencing multiple tables during INSERT...
Well you need to normalize the table. Instead of having columns named N1, N2, N3, you'd just have 1 column N with 3 separate rows. replace 'N' with the name of your column