beatjost
Založený: 31.03.2004
Príspevky: 10
Zaslal: 2004-03-31 15:17
Návrat hore
Odpovedať s citátom
|
It's possible to translate a foreign key with one or more fields of another table...
But is it also possible to show some attributes of this foreign table as well (display only), even though they doesn't exist in on my "main"-table?
EXAMPLE
- Table1(Id, Desc, Name, Email)
- Table2(Id, Tab1Id, Desc)
My Form is now based on table2. In Tab1Id I display
the Name of table1. But I want also to show the attribute email from table1.
Is that possible? And how?
Thank you!
|
michal
Založený: 17.06.2003
Príspevky: 537
Bydlisko: Slovakia
Zaslal: 2004-03-31 16:14
Návrat hore
Odpovedať s citátom
|
there are two possibilities to solve this:
1. use ['values']['description'] as array (see documentation)
2. create
| Kód: |
$opts['fdd']['dummy_field'] = array (
'sql' => 'Table1id',
'values' => as normal
'options' => 'VLFD'
);
|
a dummy field. difference from normal is 'sql' option and permissions which won't allow adding or changing field
|
beatjost
Založený: 31.03.2004
Príspevky: 10
Zaslal: 2004-04-01 15:03
Návrat hore
Odpovedať s citátom
|
Hello again
I've tried your code example. Unfortunately I still wasn't able to run my form correctly :-(
Thanks for your help...
Problem:
I want to show attributes of another table (Join)...
Tables:
TABLE1:
userid
username
realname
userpwd
useremail
userphone
useraddress
...
TABLE2:
(my form is based on this table)
id
user
(FK of table1)
bestclassing
status
...
Code:
| Kód: |
$opts['fdd']['dummy1'] = array (
'name' => 'Email',
'sql' => 'userid',
'select' => 'T',
'options' => 'RACPDVL', //VLFD
'sort' => true,
'values' => array(
'table' => 'table1',
'column' => 'userid',
'description' => 'useremail'
)
);
|
Error:
MySQL error 1146
Tabelle 'xmentor.table1' existiert nicht
----------------------------------------------
Benutzer Email Status TT Startjahr Motto
MySQL error 1146
Tabelle 'xmentor.table1' existiert nicht
----------------------------------------------
phpMyEdit error: invalid SQL query
----------------------------------------------
SELECT PMEtable0.id AS qf0,PMEjoin1.realname AS qf1,PMEtable0.user AS qf1_idx,userid AS qf2,userid AS qf2_idx,PMEtable0.status AS qf4,PMEtable0.start AS qf8,PMEtable0.start AS qf8_idx,PMEtable0.motto AS qf14 FROM xmentor103_portrait AS PMEtable0 LEFT OUTER JOIN xmentor.xmentor103_user AS PMEjoin1 ON PMEjoin1.userid = PMEtable0.user LEFT OUTER JOIN xmentor.table1 AS PMEjoin2 ON PMEjoin2.userid = PMEtable0.dummy1 ORDER BY PMEjoin1.realname,userid LIMIT 0,15
|
michal
Založený: 17.06.2003
Príspevky: 537
Bydlisko: Slovakia
Zaslal: 2004-04-01 19:21
Návrat hore
Odpovedať s citátom
|
'sql' should be user (FK of table1) (since this field is in the table you are using)
|
beatjost
Založený: 31.03.2004
Príspevky: 10
Zaslal: 2004-04-02 07:22
Návrat hore
Odpovedať s citátom
|
| michal Napísal: |
| 'sql' should be user (FK of table1) (since this field is in the table you are using) |
* * * * * * * * * * * * * * *
i've tried this too...
| Kód: |
$opts['fdd']['dummy1'] = array (
'name' => 'Email',
'sql' => 'user',
'select' => 'T',
'options' => 'RACPDVL', //VLFD
'sort' => true,
'values' => array(
'table' => 'xmentor103_user',
'column' => 'userid',
'description' => 'useremail'
)
); |
ERROR:
MySQL error 1054
Unbekanntes Tabellenfeld 'PMEtable0.dummy1' in on clause.
---------------------------------------------------
Benutzer Email Status TT Startjahr Motto
MySQL error 1054
Unbekanntes Tabellenfeld 'PMEtable0.dummy1' in on clause.
---------------------------------------------------
phpMyEdit error: invalid SQL query
--------------------------------------------------------------------------------
SELECT PMEtable0.id AS qf0,PMEjoin1.realname AS qf1,PMEtable0.user AS qf1_idx,user AS qf2,user AS qf2_idx,PMEtable0.status AS qf4,PMEtable0.start AS qf8,PMEtable0.start AS qf8_idx,PMEtable0.motto AS qf14 FROM xmentor103_portrait AS PMEtable0 LEFT OUTER JOIN xmentor.xmentor103_user AS PMEjoin1 ON PMEjoin1.userid = PMEtable0.user LEFT OUTER JOIN xmentor.xmentor103_user AS PMEjoin2 ON PMEjoin2.userid = PMEtable0.dummy1 ORDER BY PMEjoin1.realname LIMIT 0,15
|
michal
Založený: 17.06.2003
Príspevky: 537
Bydlisko: Slovakia
Zaslal: 2004-04-02 07:36
Návrat hore
Odpovedať s citátom
|
well you have to honour to be the first one to use the totally new 'join' variable in the configuration. :-) see current CVS versions of core class and documentation (doc/sgml/something). and comment whether documentation is good.
(the 'sql' stuff is used in creating of the field list, but not in joining).
'join'=> 'PMEjoin2.userid = qf2'
should do renaming stuff when joining.
or
'sql' => 'PMEtable0.user'
'join' => 'PMEjoin2.userid = PMEtable0.qf2'
if it does not work, try disabling the first join.
bevare, the numbers in qf? might change unexpectedly.
|
beatjost
Založený: 31.03.2004
Príspevky: 10
Zaslal: 2004-04-02 08:15
Návrat hore
Odpovedať s citátom
|
sorry to bother you again... ;-)
i've tried now several variations with this 'join' parameter, but it still doesn't work.
i'm using the newest downloadable version (5.4).
there seems to be a problem in the from clause...
| Kód: |
SELECT
PMEtable0.id AS qf0,PMEjoin1.realname AS qf1,PMEtable0.user AS qf1_idx,user AS qf2,user AS qf2_idx,
PMEtable0.status AS qf4,PMEtable0.start AS qf8,PMEtable0.start AS qf8_idx,PMEtable0.motto AS qf14
FROM
xmentor103_portrait AS PMEtable0 LEFT OUTER JOIN xmentor.xmentor103_user AS PMEjoin1 ON PMEjoin1.userid = PMEtable0.user
LEFT OUTER JOIN xmentor.xmentor103_user AS PMEjoin2 ON PMEjoin2.userid = PMEtable0.dummy1
ORDER BY
PMEjoin1.realname LIMIT 0,15
* * * * * * * * * * * * * * * * * * * * * * * * *
SELECT
PMEtable0.id AS qf0,PMEjoin1.realname AS qf1,PMEtable0.user AS qf1_idx,PMEtable0.user AS qf2,PMEtable0.user AS qf2_idx,
PMEtable0.status AS qf4,PMEtable0.start AS qf8,PMEtable0.start AS qf8_idx,PMEtable0.motto AS qf14
FROM
xmentor103_portrait AS PMEtable0 LEFT OUTER JOIN xmentor.xmentor103_user AS PMEjoin1 ON PMEjoin1.userid = PMEtable0.user
LEFT OUTER JOIN xmentor.xmentor103_user AS PMEjoin2 ON PMEjoin2.userid = PMEtable0.dummy1
ORDER BY
PMEjoin1.realname LIMIT 0,15
|
do you have any further hints or examples for me?
the joining part in the documentation refers only
to foreign-key translation... there would be some
examples very usefully.
thx
|
michal
Založený: 17.06.2003
Príspevky: 537
Bydlisko: Slovakia
Zaslal: 2004-04-02 23:20
Návrat hore
Odpovedať s citátom
|
well, 5.4 version of pme is way too old. you have to use current CVS version ( on your left you see CVS / browser then phpMyEdit / ..class ) (documentation in .../doc/sgml/...)
this feature was introduced only two days ago ....
|
michal
Založený: 17.06.2003
Príspevky: 537
Bydlisko: Slovakia
Zaslal: 2004-04-06 16:58
Návrat hore
Odpovedať s citátom
|
you can access fields from already join-ed table by
| Kód: |
'sql' => 'PMEjoin1.usermail',
|
in a dummy field definition.(PMEjoin1 is an alias for already joined table, so there must already be one join, variable 1 can change)
so there is no need for another joining or values options.
after first glance looks like working. report possible filter/sorting/... problems.
|
beatjost
Založený: 31.03.2004
Príspevky: 10
Zaslal: 2004-04-07 07:54
Návrat hore
Odpovedať s citátom
|
Thank you a lot! this works now :-)
| michal Napísal: |
you can access fields from already join-ed table by
| Kód: |
'sql' => 'PMEjoin1.usermail',
|
in a dummy field definition.(PMEjoin1 is an alias for already joined table, so there must already be one join, variable 1 can change)
so there is no need for another joining or values options.
after first glance looks like working. report possible filter/sorting/... problems. |
|
willirl
Založený: 04.04.2004
Príspevky: 14
Zaslal: 2004-04-08 00:49
Návrat hore
Odpovedať s citátom
|
I'm not sure what you are trying to tell me. I have explained (clearly i think) what the problem is that I am trying to solve.
It should be obvious by now that I am not an "experienced" user of the product with in-depth knowledge.
I need a clear concrete example of how to define the field I have described.
Can you show me all of the 'fdd' code required to make this work?
Here is a description again:
| Citácia: |
Two tables. Columns of interest:
cost.id
items.id
items.name
When showing the cost table I also want to the items.name in a new column. (id is the related field)
|
[/code]
|
beatjost
Založený: 31.03.2004
Príspevky: 10
Zaslal: 2004-04-08 06:53
Návrat hore
Odpovedať s citátom
|
Well, I've implemented as follows...
form_table => xmentor103_portraits
lookup_table => xmentor103_user
| Kód: |
$opts['fdd']['id'] = array(
'name' => 'Id',
'select' => 'T',
'maxlen' => 11,
'required' => true,
'sort' => true,
'options' => 'H'
);
$opts['fdd']['user'] = array(
'name' => 'Benutzer',
'select' => 'D',
'maxlen' => 30,
'required' => true,
'sort' => true,
'options' => 'ACPDVL'
);
//JOIN... (fky lookup userid_to_realname)
$opts['fdd']['user']['values']['table'] = 'xmentor103_user';
$opts['fdd']['user']['values']['column'] = 'userid';
$opts['fdd']['user']['values']['description'] = 'realname';
//dummyfield of 2nd table (xmentor103_user)
//join alread exists of field above...
$opts['fdd']['dummy1'] = array (
'name' => 'Email',
'sql' => 'PMEjoin1.useremail',
'options' => 'RACPDVL',
'escape' => false,
'URL' => 'mailto:$value',
'sort' => true
); |
For your need's this should be similar...
|
michal
Založený: 17.06.2003
Príspevky: 537
Bydlisko: Slovakia
Zaslal: 2004-04-08 09:59
Návrat hore
Odpovedať s citátom
|
a typical db schema would be:
cost.id
cost.item_id
item.id
item.name
where cost.item_id = item.id
you want it to look like
cost.id | item.id | item.name
so user => item_id (cost.item_id )
user_id => id (item.id)
real_name => id (item.id or blank)
PMEjoin1.useremail => PMEjoin1.name
For dummy/virtual fields i would not recommend AC options, it could create either SQL errors or questions from users.
|
willirl
Založený: 04.04.2004
Príspevky: 14
Zaslal: 2004-04-08 23:27
Návrat hore
Odpovedať s citátom
|
You know, I am determined to get an answer to my question otherwise I think at this point I would just give up. But...
I need an example using the problem I have. I am having GREAT difficulty translating someone elses solution to the problem I presented.
Can you show me the code to solve MY problem using MY tables and MY fields? This can't be that hard?
|
ajh
Založený: 17.12.2003
Príspevky: 236
Zaslal: 2004-04-09 19:07
Návrat hore
Odpovedať s citátom
|
If you'd like people to help you, then I suggest that you start by not shouting at them. Everyone on this forum helps out of goodness. OK - that off my chest...
Please will you post the
complete
schema for these two tables - you haven't given us enough information yet. Also please note which are the two joining fields on these tables.
|
|
Odoslať novú tému
Odpovedať na tému
Choď na stránku 1, 2 Ďalší
|
Prihlásiť
Registrácia
English
