2. PHPRunner General questions
  3. How to create a Many-to-many database?
This topic is locked

How to create a Many-to-many database?

 		11/30/2007 5:34:26 PM
PHPRunner General questions
T
 toasty author

I'm trying to create a health supplement database, from what I've read so far about MySQL and relational databases I'm probably looking at a many-to-many structure. From toying around with the PHPRunner I'm still pretty fuzzy on how I would set this up.
Here's an example of what I'm trying to achieve:
supplement Y.
For this my database (created outside of and prior to using PHPRunner) has these three tables to handle these relationships:

[codebox]

Table Supplement:

sup_id (primary key)

sup_name

etc.
Table Condition:

cond_id (primary key)

cond_name

etc.
Table sup_cond (intermediary between the two):

sup_id (foreign key from supplement)

cond_id (foreign key from condition)[/codebox]
This sort of many-to-many structure is repeated with other relationship such as supplement and the price of popular manufactures. I'm wondering how I set this up with PHPRunner? Any help would be awesome!

Thanks!
J
 		Jane 12/3/2007

Hi,
you can create custom view for sup_cond table on the Datasource tables tab.

Then set up sup_cond as detail table for Supplement table, new custom view as detail table for Condition table on the Datasource tables tab.
T
 		toasty author 12/6/2007

Hi,

you can create custom view for sup_cond table on the Datasource tables tab.

Then set up sup_cond as detail table for Supplement table, new custom view as detail table for Condition table on the Datasource tables tab.



Thanks for responding Jane, but I'm still not sure what I need to do exactly. I'm in the Datasource Tables tab. I clicked "Ad View", selected "sup_cond" and went with the default suggested name (copy of sup_cond), and that's where I get lost <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=23954&image=1&table=forumreplies' class='bbc_emoticon' alt=':(' />
From what I've seen, the "detail" table seems as though it is limited to being the same as whatever table you're trying to configure. So I'm confused when you're saying to make the value for detail in the supplement table equal "sup_cond".


If you or anyone could explain a bit more I'd be really grateful, thanks! <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=23954&image=3&table=forumreplies' class='bbc_emoticon' alt=':)' />
Alexey admin 12/7/2007

Hi,
here is what you can do.

  1. click on sup_cond table in the list.
  2. select conditionsin Master table box.
  3. select cond_id in primary and foreign key boxes.
  4. click on supplementstable in the list
  5. select sup_condin Master table box.
  6. select sup_idin primary and foreign key boxes.
    After you build the pages you can browse conditions table, click on sup_cond link for each record to see the list of supplements.

    Click on supplements link to see supplement details.
    Set up sup_cond fields as Lookup wizards or add joined fields from supplements to make it look more informative.
    To browse a list of supplements and get a list of conditions for each supplement create a custom view for sup_cond table and setup master details relationships vice versa using this view instead of sup_cond table.
T
 		toasty author 12/18/2007

Hi,

here is what you can do.

  1. click on sup_cond table in the list.
  2. select conditionsin Master table box.
  3. select cond_id in primary and foreign key boxes.
  4. click on supplementstable in the list
  5. select sup_condin Master table box.
  6. select sup_idin primary and foreign key boxes.



Thanks for your suggestion and your help. Unfortunately for step #6 above, I'm unable to select sup_id as the Foreign key for Supplement, possibly because "sup_id" is the Primary key? Any other thoughts <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=24275&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
conditiontable.




sup_cond table.




supplement table.