Topic awaiting preservation: myPHPadmin and mySQL (Page 1 of 1)

I am using myPHPadmin to administer a mySQL DB and currently I have one table designed and filled with the following:

friendID
name
address
ph_number
email

I now want to create a table that lists all of the DVD's the friends listed in table 1 (shown above) own. How do I reference the other table? What do I do about duplicate DVD? I assume I am going to use a join or union but I don't know how to do this properly. BTW I am using friendID as the primary key in the first table.

you just want either a 2 or 3 column table with friendID, (some sort of DVD ID), and maybe a number to tell you how many of a DVD they own.

I could give you the sql but I'm not sure how you are setting up the second table but using a 2 column second table and say you wanted someones name, and the dvd's they own. I'd do this

select distinct table1.name, table2.dvd from table1, table2 where table2.friendID = table1.friendID

the distinct should prevent any duplicates.




.:[ Never resist a perfect moment ]:.

i want the ability to do duplicates,

so as far as the IDs go should I print a list of IDs from my friends table as a reference when I'm entering the DVDs in the second table? Is there anyway to set up a lookup so that myPHPadmin will present me with a drop down field poplulated with the list of available friends so that at the time I'm entering the records in table two I can just easily select from the drop down box.

keep in mind that myPHPadmin is a GUI interface for mySQL config. I'm not entering statements manually as you've suggeseted below...I'm very new to this. I've got a rough understanding of how this works and I'm gradually learning it as I go. Thanks for your help BTW.

Yeah, you want one link between the second table and the first usually this is some sort of ID. Then for every DVD that is owned by someone you put a row in the DVD table with 2 cols, the friendID and the DVD name.

Now ideally (and a bit easier to make expandable) if you want to have a table with information about each DVD (outside of who owns it) this is one of those areas where you may want actually 3 tables.
1 table would be the friend info, with each friend getting a Unique friend ID, Table 2 would be the DVD table Which has all the DVD info (run time, title rating etc). where each DVD gets a unique DVD id. Then the third table would be just be rows with 2 columns, friendID - DVDId and you could get the friend info from the friend info table and the DVD info from the DVD table.





.:[ Never resist a perfect moment ]:.