Page 1 of 1

Complex relationships in tables/displaying and editing them

Posted: Thu Jan 20, 2005 4:37 pm
by Frapster
Ok - I am wanting to code this but I genuinely don't know where to begin. I have 3 tables. Table 1 contains 4 fields - ID, LampName, URL1, URL2. Table 2 contains 3 fields - ID, LampNameID, LampType. Table 3 contains 4 fields - ID, LampNameID, LampTypeID, Preferred. The database is a 'lamp compatibility' database that allows you to choose a lamp model and display other lamps that are compatible with it. Table 2 is associated with Table 1 in that you can have multiple lamp types for one LampName. Table 3 is associated with Table 1 and 2 in that it uses both to establish lamp compatibilities. For example: Lamp A is compatible with Lamps G and H. Lamp G is compatible with Lamps A and B.

I want to create a screen that displays all of the LampNames twice. Once in a drop-down list populated from Table 1. Again in a list box populated from Table 1. When I select a LampName from the drop down I want it to highlight (or in some fashion show) the lamps it's associated with in the list box or some sort of list.

I also want to be able to add and remove compatibilities contained in Table 3 without modifying Tables 1 and 2.

When I first came up with the idea of building an interface to manage the database I knew this one would be the most complex and I thought I could figure it out. But frankly I need help.

If you've read this far and think you can help me - please let me know. I'm going to go ahead and code as far as I can assuming that I have to do this on 1 or 2 pages at most.

I'll post my progress in the morning if no one has anything to offer me to help get me started. I genuinely believe that if someone points me in the right direction I can figure it out. We'll see. lol

Posted: Thu Jan 20, 2005 4:48 pm
by timvw
1-) you want to display all the lampnames. easy because you can simply select them from table1 and show.

2-) you want to display all the lampnames and the related lamptypes. easy because all you have to is perform a join on t2.lampnameid=t1.id and order by lampname.

3-) if i get it right... you are willing to do something as explained on http://www.tonymarston.net/php-mysql/many-to-many.html

Posted: Fri Jan 21, 2005 8:02 am
by Frapster
wow! great article - thanks for pointing me to it. And thanks for your tips. It's given me a great place to start reading.