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; |
|
Prihlásiť
Registrácia
English
