Master - Detail Relationships

This topic is locked

Master - Detail Relationships	2/7/2008 3:13:25 PM
PHPRunner General questions
M mrphp author Hello, For some reason I am really struggling with the Master - Detail table relationships concept. I have 2 tables in a database. One is "bussiness" which has the fields business_id (auto incr), businessname, street, city, state and zip. The other is "contact" which has the fields contact_id (auto incr), contactname, cellphone and email. What I'm after is a link between the 2 so that if I'm on the bussiness page I can select contacts to see all the contacts for that business and if I'm on the contacts page I can see what business the contact works for. I seem to be blindly trying different settings on the datasource tables tab of phpRunner without understanding what I'm doing and none of my selections are working. Is there a good example somewhere of how this works? I've tried the tutorial but that's not helping. I'm thinking my contacts table table needs a business_id field to link to but don't understand how it gets populated. Thanks for any help.

Mark as Solved Mark as not solved

7 replies


J	Jane 2/8/2008
	Hi, you need to add new business_id field (integer, not auto-increment) to the contact table. Use this field as foreign key in the master-detail relationships on the Datasource tables tab. Then when you add new record to the contact table go to the bussiness table first, proceed to the detail table (using detail link) and add new record. In this case business_id field will fill automatically. Also I recommend you to have a look at the following article: http://www.xlinesoft.com/phprunner/docs/ma...elationship.htm


M	mrphp author 2/8/2008
	Hi, you need to add new business_id field (integer, not auto-increment) to the contact table. Use this field as foreign key in the master-detail relationships on the Datasource tables tab. Then when you add new record to the contact table go to the bussiness table first, proceed to the detail table (using detail link) and add new record. In this case business_id field will fill automatically. Also I recommend you to have a look at the following article: http://www.xlinesoft.com/phprunner/docs/ma...elationship.htm OK. I added business_id to the contact table. Now I can select a contact and that works. However, when I search contacts I don't have a link back to the company.As an example, I search for Smith on the contacts page and 5 records are shown. I'm unable to determine what company they each are associated with. I don't seem to have an option to create the same link from contact to company. If I select the business table as the detail, the business_id field is not available. Am I missing something? Thanks for your help.


H	HMIS 2/9/2008
	OK. I added business_id to the contact table. Now I can select a contact and that works. However, when I search contacts I don't have a link back to the company.As an example, I search for Smith on the contacts page and 5 records are shown. I'm unable to determine what company they each are associated with. I don't seem to have an option to create the same link from contact to company. If I select the business table as the detail, the business_id field is not available. Am I missing something? Thanks for your help. I just did what you're looking to do Create a custom view then edit then edit the SQL query using this format SELECT survey.Legal_Problems, survey.Valid_License, survey.Hospital_Stays, survey.Given_Up_Work, survey.Same_Employer, survey.Have_Auto, clients.First_Name, clients.Last_Name FROM clients INNER JOIN survey ON (clients.business_id = survey.business_id)


M	mrphp author 2/11/2008
	I just did what you're looking to do Create a custom view then edit then edit the SQL query using this format SELECT survey.Legal_Problems, survey.Valid_License, survey.Hospital_Stays, survey.Given_Up_Work, survey.Same_Employer, survey.Have_Auto, clients.First_Name, clients.Last_Name FROM clients INNER JOIN survey ON (clients.business_id = survey.business_id) I'm getting incorrect syntax messages. Are your 2 tables "survey" and "client"? I assume you are not using MySQL. Thanks for your help.


H	HMIS 2/13/2008
	I'm getting incorrect syntax messages. Are your 2 tables "survey" and "client"? I assume you are not using MySQL. Thanks for your help. I am using MySQL with PHP ..if you submit the tables and fields Ill write the sql that works ... also you need to follow what jane wrote ...its quite simple really. Hopefully your using phpmyadmin to manage your MySQL database


M	mrphp author 2/14/2008
	I am using MySQL with PHP ..if you submit the tables and fields Ill write the sql that works ... also you need to follow what jane wrote ...its quite simple really. Hopefully your using phpmyadmin to manage your MySQL database Here are my tables and fields: Table: business Fields: business_id contacts_id company street city state zip cophone fax type trade comment date Table: contacts_name Fields: contact_id business_id contact position extension cell email pager comment My "main" list is based on the business table. Clicking on "contact" then takes me to the Contacts_name table list with the list limited to contacts that work for that particular business. Where I'm struggling is with the link to go back to the business that relates to these contacts. In particullar, if I search the contacts_name list for a person's name I have no way to know what company he works for. And yes, I do use phpmyadmin. Thanks for taking the time to help me with this. Thanks to everyone who takes the time to help in this forum.


H	HMIS 2/18/2008
	Here are my tables and fields: Table: business Fields: business_id contacts_id company street city state zip cophone fax type trade comment date Table: contacts_name Fields: contact_id business_id contact position extension cell email pager comment My "main" list is based on the business table. Clicking on "contact" then takes me to the Contacts_name table list with the list limited to contacts that work for that particular business. Where I'm struggling is with the link to go back to the business that relates to these contacts. In particullar, if I search the contacts_name list for a person's name I have no way to know what company he works for. And yes, I do use phpmyadmin. Thanks for taking the time to help me with this. Thanks to everyone who takes the time to help in this forum. Im assuming that your using business business_id for your master SELECT business.business_id, business.contacts_id, business.company, business.street, business.city, business.state, business.zip, business.cophone, business.fax, business.type, business.trade, business.comment, business.date, contacts_name, contacts_name.contact_id, contacts_name.business_id, contacts_name.contact, contacts_name.position, contacts_name.extension, contacts_name.cell, contacts_name.email, contacts_name.pager, contacts_name.comment FROM contacts_name INNER JOIN business ON (business.business_id = contacts_name.business_id)

7 replies

Mark as Solved Reply