JammerPro
Založený: 07.12.2006
Príspevky: 33
Zaslal: 2006-12-16 17:46
Návrat hore
Odpovedať s citátom
|
I need to show a different table's column value in another table's phpMyEdit page.
My data base contains two tables. One table has account information (name, address, etc.), the other billing transaction information (price, quantity, client number, etc.).
The billing table knows the client by a number (billing.client) for transaction purposes. The account table has billing.client number as its table id with the client's actual name in a row on the table client in a column called client.client.
I would like to show client.client (the client's name) on my phpMyEdit billing table as a virtual column so that I'm not just looking at the client's number (billing.client).
I read all docs, but I'm not sure how to set this up. In fact, I'm clueless.
For example,
What I have now on my phpMyEdit billing table sheet is:
Client | Qty. | Price
1______10__10.00
What I want to see is:
Client | Virtual Col. | Qty. | Price
1______John Doe___10___10.00
...WHERE John Doe FROM another table called client in column client (client.client) HAVING client.id = billing.client...
Any help would be greatly appreciated.
Thanks in advance.
|
ajh
Založený: 17.12.2003
Príspevky: 236
Zaslal: 2006-12-17 18:54
Návrat hore
Odpovedať s citátom
|
It's not documented, but in the CVS version, there is 'V' option on the 'input' option which creates a virtual field (one which is not linked to the database). You need to supply an 'sql' string to load the value.
|
JammerPro
Založený: 07.12.2006
Príspevky: 33
Zaslal: 2006-12-17 23:33
Návrat hore
Odpovedať s citátom
|
| Kód: |
$opts['fdd']['virtual_01'] = array(
'name' => 'AName',
'select' => 'V',
'options' => 'L', // Only list
'maxlen' => 1,
'sort' => false,
'sql' => 'client.client having billing.client = client.id',
'display' => 'V',
); |
Note, the above virtual column is on PMEtable0 (billing). phpMyEdit will not recognize the second table's column (client.client) on billing! Here's the error.
MySQL error 1064
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'having billing.client = client.id AS qf3,PMEtable0.employee AS
--------------------------------------------------------------------------------
phpMyEdit error: invalid SQL query
--------------------------------------------------------------------------------
SELECT PMEtable0.id AS qf0,PMEtable0.date AS qf1,PMEtable0.client AS qf2,client.client having billing.client = client.id AS qf3,PMEtable0.employee AS qf4,PMEtable0.job_type AS qf5,PMEtable0.time_spent AS qf6,PMEtable0.wage AS qf7,PMEtable0.charge AS qf8,PMEtable0.payment AS qf9,PMEtable0.description AS qf10 FROM billing AS PMEtable0 ORDER BY PMEtable0.id DESC LIMIT 0,1020.704 miliseconds
phpMyEdit is not recognizing the second table "client". First table is billing (which contains the above virtual column).
Any suggestions?
Naposledy upravil JammerPro dňa 2006-12-19 01:43, celkom upravené 2 krát
|
ajh
Založený: 17.12.2003
Príspevky: 236
Zaslal: 2006-12-18 10:36
Návrat hore
Odpovedať s citátom
|
I think it should be:
but in any case, I think that to get the join working you are going to need to use the 'values' option which allows you to do a lookup to another table. That table will then be coded into to SQL statement which allows you to use it in the virtual column.
The other point is the code that you put into the 'sql' line is only the bit that generates the column you want, in your case 'client.client'.
There are some fairly complex options available with 'values' that allow you to pretty well customise the SQL line as you need.
|
JammerPro
Založený: 07.12.2006
Príspevky: 33
Zaslal: 2006-12-18 15:43
Návrat hore
Odpovedať s citátom
|
| Kód: |
$opts['fdd']['virtual_01'] = array(
'name' => 'Virtual_01',
'input' => 'V',
'values' => Array(
'table' => 'client', //joined table
'column' => 'id', //the joined key
'description' => 'ID'),
'join' => '$main_table.billing = $join_table.client',
'options' => 'L', // Only list
'maxlen' => 1,
'sort' => false,
'sql' => 'client'
);
|
Produces the following error:
MySQL error 1054
Unknown column 'PMEtable0.virtual_01' in 'on clause'
--------------------------------------------------------------------------------
TID# Date Account Virtual_01 Staff Transaction Time Rate Charge Payment Description
MySQL error 1052
Column: 'client' in field list is ambiguous
--------------------------------------------------------------------------------
phpMyEdit error: invalid SQL query
--------------------------------------------------------------------------------
SELECT PMEtable0.id AS qf0,PMEtable0.date AS qf1,PMEtable0.client AS qf2,client AS qf3,client AS qf3_idx,PMEtable0.employee AS qf4,PMEtable0.job_type AS qf5,PMEtable0.time_spent AS qf6,PMEtable0.wage AS qf7,PMEtable0.charge AS qf8,PMEtable0.payment AS qf9,PMEtable0.description AS qf10 FROM billing AS PMEtable0 LEFT OUTER JOIN floridae_billing.client AS PMEjoin3 ON (PMEjoin3.id = PMEtable0.virtual_01) ORDER BY PMEtable0.id DESC LIMIT 0,107.226 miliseconds
THIS SHOULD BE AN EASY THING TO DO.
In MySQL, the query is simple:
"SELECT billing.client, client.id, client.client FROM billing, client WHERE billing.client = client.id HAVING billing.client = client.id"
I can't believe that this can't be accomplished in phpMyEdit easily...
Naposledy upravil JammerPro dňa 2006-12-19 13:58, celkom upravené 3 krát
|
ajh
Založený: 17.12.2003
Príspevky: 236
Zaslal: 2006-12-18 17:15
Návrat hore
Odpovedať s citátom
|
It can be done easily....but reading the documentation helps as well. Firstly the 'values' clause needs to be on the field that you are joining, not the virtual field. Secondly, the SELECT statement that PME generates renames all of the tables and columns so that it can predict them better, so if you look at the SELECT line you can see what the table names should be:
billing table becomes PMEtable0
billing.id becomes PMEtable0.qf0
billing.date becomes PMEtable0.qf1
billing.client becomes PMEtable0.qf2
etc
So you need to add the 'values' clause to the 'client' field:
| Kód: |
'values' => Array(
'table' => 'client', //joined table
'column' => 'id', //the joined key
'description' => 'ID'),
|
Then have a look at the generated SELECT statement to see what the name of the joined table ('client') is.
So then the 'sql' clause on the virtual field becomes :
| Kód: |
| 'sql' => 'PME<tablename>.client |
This might not be absolutely correct, but it should give you the gist...
|
JammerPro
Založený: 07.12.2006
Príspevky: 33
Zaslal: 2006-12-18 20:07
Návrat hore
Odpovedať s citátom
|
| Kód: |
$opts['fdd']['client'] = array(
'name' => 'Account',
'options' => 'LACPVDR',
'colattrs' => 'align="center"',
'select' => 'T',
'maxlen' => 11,
'default' => '0',
'sort' => true
);
$opts['fdd']['client'] = array(
'name' => 'Account Name',
'input' => 'V',
'values' => Array(
'table' => 'client', //joined table
'column' => 'id', //the joined key
'description' => 'ID'),
'join' => '$main_table.billing = $join_table.client',
'options' => 'LACPVDR',
'maxlen' => 1,
'sort' => false,
'sql' => 'PMEjoin2.client'
); |
First, thanks for getting me this far. I think I'm close. The above results in the new client info (client.client or PMEjoin2.client) showing up in the original client field (PMEtable0.client or billing.client <grin>). The original field client (billing.client) is gone (joined to the new client field). I wanted to show the new field as a virtual field yet still show the old field in another column so it can be changed.
Any ideas on this... Trust me, I have reviewed the documentation, but either I don't understand it, or it is not so clear on virtual columns.
Naposledy upravil JammerPro dňa 2006-12-19 14:01, celkom upravené 3 krát
|
ajh
Založený: 17.12.2003
Príspevky: 236
Zaslal: 2006-12-19 00:02
Návrat hore
Odpovedať s citátom
|
Slightly confused about your schema...could you give me an excerpt of the two table with the appropriate fields that we are talking about here, noting which two are joined.
Also...you have called the real field ('client') and the virtual field ('client') the same thing, so that's not going to work. You can essentially call the virtual field anything you want, just not anything that is a column in the table.
I'm still pretty sure that the 'value' clause should be on the real field, not the virtual field (the first 'client' in your example).
|
JammerPro
Založený: 07.12.2006
Príspevky: 33
Zaslal: 2006-12-19 01:29
Návrat hore
Odpovedať s citátom
|
Actually, the above is working quite well. My schema is:
DB floridae_billing consists of 2 tables, billing and client. Table billing has a client and id field, billing.client (client number id) and billing.id (table key autonumber). Table client also has a client and id field, client.id (client number id) and client.client (client name).
I made another field which is virtual and takes the info from the first client field (billing.client or should I say PMEtable0.client <grin>). The second client field above is referring to client.client (PMEjoin2.client).
Again, thanks for your help!...
JammerPro
|
JammerPro
Založený: 07.12.2006
Príspevky: 33
Zaslal: 2006-12-19 06:03
Návrat hore
Odpovedať s citátom
|
AJH, HERE's THE FINAL VERSION:
| Kód: |
$opts['fdd']['client'] = array(
'name' => 'Account Name',
'values' => Array(
'table' => 'client', //joined table
'column' => 'id', //the joined key
'description' => 'ID'),
'join' => '$main_table.billing = $join_table.client',
'options' => 'LVD',
'maxlen' => 1,
'sort' => false,
'sql' => 'PMEjoin2.client'
);
$opts['fdd']['virtual_1'] = array(
'name' => 'Account Number',
'options' => 'LACPVDR',
'colattrs' => 'align="center"',
'sql' => 'PMEtable0.client',
'select' => 'T',
'maxlen' => 11,
'default' => '0',
'sort' => true
); |
Turns out that my prior code with two fields for "client" was wrong. The first field (billing.client) was being overwritten by the subsequent code with the field client. Just as you predicted, can't have "client" twice. Although the code worked to display the client name (client.client), I still did not have the column with the Account Number (the original billing.client without the join).
Above is the final version that works perfectly... Note, I did not need the INPUT 'V'.
I guess there is a bit of a learning curve <grin>, but I think I have it now! Thanks again for your time, effort, and patience...
Best Wishes.
JammerPro
|
fefe64
Založený: 11.12.2006
Príspevky: 13
Zaslal: 2007-01-24 23:36
Návrat hore
Odpovedať s citátom
|
JammerPro
Hello JammerPro, i have a question for you!
i have 3 table
film(code_film,name_film)
person(code_person, name_person)
actor (code_actor, film_id, person_id)
i would like to do something that i can't find help so i hope you will help me.
I would like on my actor form to edit a virtual field
Fisrt:
Form actor:
code_actor |
virtual1(name_person) |
vitual2(name_film|
SEcond i don't know if it's possible
on my FILM form to :
1) Edit for a film every actor of the film
2) Add a film and select person for actor and with trigger add actor in the actor table!!
I don't know if you understand my english
excuse me but i'm french so my english is not very good.
Hope to ear from you soon
|
|
Odoslať novú tému
Odpovedať na tému
|
|
Prihlásiť
Registrácia
English
