beatjost
Joined: 31 Mar 2004
Posts: 10
Posted: 2004-03-31 15:17
Back to top
Reply with quote
|
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
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2004-03-31 16:14
Back to top
Reply with quote
|
there are two possibilities to solve this:
1. use ['values']['description'] as array (see documentation)
2. create
| Code: |
$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
Joined: 31 Mar 2004
Posts: 10
Posted: 2004-04-01 15:03
Back to top
Reply with quote
|
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:
| Code: |
$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
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2004-04-01 19:21
Back to top
Reply with quote
|
'sql' should be user (FK of table1) (since this field is in the table you are using)
|
beatjost
Joined: 31 Mar 2004
Posts: 10
Posted: 2004-04-02 07:22
Back to top
Reply with quote
|
| michal wrote: |
| 'sql' should be user (FK of table1) (since this field is in the table you are using) |
* * * * * * * * * * * * * * *
i've tried this too...
| Code: |
$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
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2004-04-02 07:36
Back to top
Reply with quote
|
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
Joined: 31 Mar 2004
Posts: 10
Posted: 2004-04-02 08:15
Back to top
Reply with quote
|
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...
| Code: |
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
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2004-04-02 23:20
Back to top
Reply with quote
|
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
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2004-04-06 16:58
Back to top
Reply with quote
|
you can access fields from already join-ed table by
| Code: |
'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
Joined: 31 Mar 2004
Posts: 10
Posted: 2004-04-07 07:54
Back to top
Reply with quote
|
Thank you a lot! this works now :-)
| michal wrote: |
you can access fields from already join-ed table by
| Code: |
'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
Joined: 04 Apr 2004
Posts: 14
Posted: 2004-04-08 00:49
Back to top
Reply with quote
|
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:
| Quote: |
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
Joined: 31 Mar 2004
Posts: 10
Posted: 2004-04-08 06:53
Back to top
Reply with quote
|
Well, I've implemented as follows...
form_table => xmentor103_portraits
lookup_table => xmentor103_user
| Code: |
$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
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2004-04-08 09:59
Back to top
Reply with quote
|
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
Joined: 04 Apr 2004
Posts: 14
Posted: 2004-04-08 23:27
Back to top
Reply with quote
|
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
Joined: 17 Dec 2003
Posts: 236
Posted: 2004-04-09 19:07
Back to top
Reply with quote
|
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.
|
|
Post new topic
Reply to topic
Goto page 1, 2 Next
|
Login
Registration
Slovak
