Complex relationships in tables/displaying and editing them

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Frapster
Forum Newbie
Posts: 22
Joined: Thu Jan 06, 2005 8:42 am

Complex relationships in tables/displaying and editing them

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
Frapster
Forum Newbie
Posts: 22
Joined: Thu Jan 06, 2005 8:42 am

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