| Index
» Projects
» phpMyEdit
» Forum
» m:n relationship |
 |
Luetti
Joined: 28 Feb 2009
Posts: 1
Posted: 2009-02-28 14:44
Back to top
Reply with quote
|
Hi,
first of all nice forum with many good advices!
I have on table containing data of a firm, one for contact persons and one to combine them which is called working_for. So its possible that a contact person works or better is the contact for two ore more firms and a firm has more than one contact person.
So I want to give the possibilty to add a contact person and in the same step allocate it to one ore more firms. I do not want to change the sql structure if possible (so no dummy fields). Has anyone an idea?
Thank you in advance and sorry for the bad english (German)!
|
doug
Joined: 10 Feb 2003
Posts: 1013
Location: Denver, Colorado (USA)
Posted: 2009-02-28 18:08
Back to top
Reply with quote
|
phpMyEdit cannot handle tables consisting of (for example) a Doctor ID and Location ID where those two columns comprise the UNIQUE KEY. I am currently trying to determine how to manage this for a very small project.
| Code: |
CREATE TABLE IF NOT EXISTS `my_doctor_location` (
`locationid` mediumint(6) NOT NULL default '0',
`doctorid` mediumint(6) NOT NULL default '0',
`active` tinyint(1) NOT NULL default '1',
UNIQUE KEY `unique_key` (`locationid`,`doctorid`),
KEY `location_id_key` (`locationid`),
KEY `doctor_id_key` (`doctorid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; |
I wrote a PHP script that takes the above ID's and looks up enough data from the related tables to identify individual records and to permit toggling the Active status between 1 and 0. Output from my PHP script resembles the pic at
http://www.phpvs.com/misc/_dr_location_index.jpg
The PHP script outputting the above screenshot can be viewed at
http://www.phpvs.com/misc/_dr_location_index.txt
I can use phpMyEdit to maintain a Doctor table and a Location table. The above referenced script allows me to de-activate records. Another (regular) PHP was written to Add records to the my_doctor_location table. For my project, I did not need the ability to Change, coPy, or Delete records (just toggle their Active status using the above linked script; many consider it a bad idea to delete records and better to toggle their status to inactive or hidden).
Scripts used to Add records can be viewed at
http://www.phpvs.com/misc/_my_doctor_location.add.txt
http://www.phpvs.com/misc/_my_doctor_location.add.validate.txt
In order to publicly display the records from these 3 tables, additional (regular) PHP scripts use a LEFT JOIN query to output a series of links that allow the user to drill down by geographic region to view Locations in a particular City, along with Doctors associated with each Location.
Custom functions exist in the above scripts, to abort() or display_errors() ... comment them out or devise your own error handling alternative.
In the initial public display of records, the user is presented with a list of City links which are passed to yet another script which queries 3 tables and displays all records found for the selected City. The query to do this resembles the following.
| Code: |
$tb1 = 'my_doctor_location';
$tb2 = 'my_location';
$tb3 = 'my_doctor';
$qry = <<<HEREDOC_VAR
SELECT $tb2.*, $tb3.*, $tb1.locationid, $tb1.doctorid
FROM $tb1
LEFT JOIN $tb2 ON $tb1.locationid = $tb2.locid
LEFT JOIN $tb3 ON $tb1.doctorid = $tb3.docid
WHERE $tb1.active = '1' AND $tb2.city = '{$city}'
ORDER BY $tb1.locationid, $tb3.last
LIMIT 0, 20
HEREDOC_VAR; |
|
|
Post new topic
Reply to topic
|
|
|
|
Login
Registration
Slovak
