phpMyEdit

phpMyEdit - Rozšírené detaily chyby			[ Späť ]

ID:	Kategória:	Dôležitosť:	Reprodukovateľnosť:	Dátum vloženia:	Posledná zmena:
272	documentation	veľká	vždy	2004-10-29 22:31	2004-12-19 02:53

Autor:	pilavdzic	Platforma:
Priradené:		OS:
Priorita:	normálna	Verzia:
Stav:	potvrdený	Verzia produktu:	current from CVS
Build produktu:		Riešenie:	otvorený
Projection:	žiadny	Duplicitné ID:
ETA:	žiadny

Zhrnutie:	Viewing multiple tables
Popis:	I am a new phpMyEdit user. Maybe I am missing something but I cannot get phpMyEdit to work with more than one table. I only need to DISPLAY fields from 3 different tables. The database structure is as follows: Tables: Trips - TripsTravellers - Travellers and one called TripsRequesters Trips, TripsTravellers and TripsRequesters have a TDYRefNum in common. TripsRequesters has a 1to1 relationship with Trips. I cannot make it into one table for other reasons. Trips has a many to one relationship with Travellers. Each traveller may have more than one Trip. I want to display for a particular TripRefNum some information from the Trips, Travellers, and TripsRequesters tables. Travellers table does not have a TripRefNum, but which traveller I want to display can be found by looking in the TripsTravellers table under that TripRefNum. I posted this in the forums a few days ago but still could not figure out how to do this with the information provided there. Is there a hack I can do to get this to work for now? I decided to put this down as a bug because I am convinced that if there was some way to display multiple tables easily that the user base of phpMyEdit would grow very substantially. It is very critical to be able to show items from multiple tables. All of the databases I have ever worked with would required displaying information from multiple tables. I am sure that other php-mysql developers would also very much appreciate having a tool like that available. Thank you very much for you help, Enis Pilavdzic
Kroky k vyvolaniu:	I am unable to find any documentation that describes how to do this, or definively state that phpMyEdit cannot do this. I found some references to that phpMyEdit only works with one table.. but I find this hard to believe because of the other things I have read on the forums and elsewhere. How do you get phpMyEdit to work with multiple tables? If it does not, how do you hack it so that it does? I have worked with databases for a few years and have never seen one with just one table. So, there should be clearer documentation that uses real world examples on how to use multiple tables. If phpMyEdit really cannot handle more than one table, only a tiny percentage of the php-mysql developer popuplation would be able to use it, and that should at least be made clear, so that people don't spend tons of time looking into it only to discover that it requires a lot of work before it can be used. Thanks very much, Enis
Dalšie informácie:
Pripojené súbory:	0000272-trips.sql.txt (2 KB) 1970-01-01 01:33

Poznámky k chybe

[Quicknote : I'm not sure you're going to get more help if you continue to post everywhere]

1. YES, PME handles "multiple" tables.
This is called table lookup in the documentation.
Please read it. it IS in the documentation.

This is basic support, which means it has mainly been designed to fill a dropdown of possible values, and store the foreign-key in the field in place of the value.

2. what you want is "advanced use" of PME, which is just arising because of PME popularity.
This requires currently use of :
- virtual field
- custom join
- sql feature

You have to use the cvs version for this, this was not complete in 5.4.
Please get the sgml documentation out of the cvs too (it's just text with some tags)

3. Virtual field
PME principle is :
Each hash-entry of the $opts['fdd'] table in the calling script is a field from the main table.
But, in some case, you may want to display something LIKE a field which isn't a field in the main table
(computed field, joined field, ...)
That's why we add the 'V' flag to 'input' option.

4. custom join
Even for basic "table lookup", you may require to do some custom join.
this is the purpose of this.
Custom join is required when you use inside a virtual field because PME can't guess how to join. (the joining "field" doesn't exist)

5. sql feature.
(See the documentation)
It's especially useful for virtual fields (else PME won't know what to display)

6. your sample :

See the sql script uploaded to see the model I used.

<?

$debug_query = true; //useful trick to know

$opts['hn'] = 'localhost';
$opts['un'] = 'root';
$opts['pw'] = '';
$opts['db'] = 'trip';
$opts['tb'] = 'trips'; // main table

$opts['key'] = 'tdyrefnum'; // Name of field which is the unique key
$opts['options'] = 'ACPVDF';
$opts['navigation'] = 'DUGB';

$opts['fdd']['tdyrefnum'] = array( //'primary key' of 'trips'
'name' => 'Tdyrefnum',
'required' => true,
'sort' => true
);
$opts['fdd']['label'] = array( //sample field of 'trips'
'name' => 'Label',
'maxlen' => 45,
'required' => true,
'sort' => true
);

//travellers' table part

//do the join
$opts['fdd']['join_to_travellers'] = array( //join_to_travellers or foo or dummy or...
'input' => 'VR', // ........ because field is V(irtual), it don't exist in the main table
'options' => '', //just do the join, don't display anything
'values' => Array( //API for currently making a join in PME.
'table' => 'travellers', //the joined table
'column' => 'idtraveller', //the joined key
'description' => 'idtraveller', //the joined value - here, it's only required to force the join
'join' => '$main_table.idtraveller = $join_table.idtraveller') //custom join because PME can't guess the joining field because it's virtual.
);

//sample of display of a field in the joined table
$opts['fdd']['a_field_from_traveller'] = array(
'name' => 'FirstName',
'input' => 'VR',
'options' => 'LFV',
'sql' => 'PMEjoin2.label'
//joined table are aliased and numbered starting from 0 and using the field rank
// join to travellers is the field number 2 so the join performed is PMEjoin2
);

$opts['fdd']['join_to_requesters'] = array(
'input' => 'VR',
'options' => '',
'values' => Array(
'table' => 'tripsrequesters',
'column' => 'tdyrefnum',
'description' => 'tdyrefnum',
'join' => '$main_table.tdyrefnum = $join_table.tdyrefnum')
);

$opts['fdd']['a_field_from_tripsrequesters'] = array(
'name' => 'label from tripsrequester',
'input' => 'VR',
'options' => 'LFV',
'sql' => 'PMEjoin4.label'
);

$opts['fdd']['join_to_tripstravellers'] = array(
'input' => 'VR',
'options' => '',
'values' => Array(
'table' => 'tripstravellers',
'column' => 'tdyrefnum',
'description' => 'tdyrefnum',
'join' => '$main_table.tdyrefnum = $join_table.tdyrefnum')
);

$opts['fdd']['a_field_from_tripstravellers'] = array(
'name' => 'label from tripstraveller',
'input' => 'VR',
'options' => 'LFV',
'sql' => 'PMEjoin6.label'
);

// Now important call to phpMyEdit
require_once 'phpMyEdit.class.php';
new phpMyEdit($opts);

?>

This is not perfect, and only usable for displaying, but this is what PME handles currently.

And please note I wrote several applications used by hundreds users, and PME fits my need.

I am discovering that I will need more time before I can add to the doc's. I can't even figure out how to get the latest CVS docs... I went into the CVS browser on the right, and into the phpmyedit directory then into the docs directory. looked at everything, but couldn't find the main doc file... is there supposed to be one there?

P.S. Also, I don't understand this format the documentation is expressed in: for example I was trying to look up how to make a field hidden again and found this in the docs:
$opts['fdd']['field_name']['input'] = 'H';
I used it to try to make the field named "other" hidden, like this:
$opts['fdd']['other'] = array (
'name' => 'SubscriberID',
'options|ACPDF' => '',
'options|LV' => 'LV',
'sql' => 'PMEtable0.SubscriberID',
'input' => 'H'
);
and it does not work. This is the case with most things I try... What am I missing? Thanks.
Of course I also tried:
$opts['fdd']['other'] = array (
'name' => 'SubscriberID',
'options|ACPDF' => '',
'options|LV' => 'LV',
'sql' => 'PMEtable0.SubscriberID'
);
$opts['fdd']['other']['input'] = 'H';
This doesnt' work either. So, if anyone has a moment to tell me how to make fields invisible again and how to go about finding this in the docs that would be great. I still can't find this new hints and tips section, or anything about joins or anything.

I managed to do:
$opts['fdd']['SubscriberID'] = array(
'name' => 'Expiry issue',
'maxlen' => 11,
'required' => true,
'sort' => true,
'virtual' => true,
'values' => array(
'table' => 'Transactions',
'column' => 'SubscriberID',
'description' => 'Last',
)
);
Which will display a field form another table by looking it up by SubscriberID, but I have not been successfully in doing more than 1 extra field like this? What if i need two?

The stuff shown in the "complex table lookup example" in the "input restrictions" section of the manual mentioned above does not work for me. First of all, how do I get an extra column to use instead of 'col_name' there if i am already using all my columns for something already? If I use the one called 'other' the example still does not work for me, am I the only one? Thanks.

edited on: 2004-12-19 07:24