| Index
» Projekty
» phpMyEdit
» Fórum
» m:n relationship |
 |
Luetti
Založený: 28.02.2009
Príspevky: 1
Zaslal: 2009-02-28 14:44
Návrat hore
Odpovedať s citátom
|
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
Založený: 10.02.2003
Príspevky: 1013
Bydlisko: Denver, Colorado (USA)
Zaslal: 2009-02-28 18:08
Návrat hore
Odpovedať s citátom
|
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.
| Kód: |
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.
| Kód: |
$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; |
|
|
Odoslať novú tému
Odpovedať na tému
|
|
|
|
Prihlásiť
Registrácia
English
