Cross-referencing multiple tables during INSERT...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lkp550
Forum Newbie
Posts: 5
Joined: Sat Dec 13, 2008 8:51 am

Cross-referencing multiple tables during INSERT...

Post by lkp550 »

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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Cross-referencing multiple tables during INSERT...

Post by josh »

No way to do this, not recommended either. It may be a hint that your data is not normalized properly
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Cross-referencing multiple tables during INSERT...

Post by jayshields »

josh wrote:It may be a hint that your data is not normalized properly
Really, how would you propose you normalise his table structure 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Cross-referencing multiple tables during INSERT...

Post by josh »

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 ;-) )
lkp550
Forum Newbie
Posts: 5
Joined: Sat Dec 13, 2008 8:51 am

Re: Cross-referencing multiple tables during INSERT...

Post by lkp550 »

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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Cross-referencing multiple tables during INSERT...

Post by josh »

lkp550 wrote: When you retrieve data from both, you type:
select Info.item1, Info.item2, Info.item3
Violates 1st NF
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Cross-referencing multiple tables during INSERT...

Post by Eran »

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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Cross-referencing multiple tables during INSERT...

Post by John Cartwright »

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).
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Cross-referencing multiple tables during INSERT...

Post by josh »

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.
lkp550
Forum Newbie
Posts: 5
Joined: Sat Dec 13, 2008 8:51 am

Re: Cross-referencing multiple tables during INSERT...

Post by lkp550 »

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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Cross-referencing multiple tables during INSERT...

Post by josh »

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
Post Reply