| Index
» Projects
» phpMyEdit
» Forum
» A column for variables to determine output from two tables |
 |
reber
Joined: 23 Jun 2003
Posts: 10
Posted: 2004-05-25 23:23
Back to top
Reply with quote
|
Hello all,
Can a value1 in table_a serve as a trigger to determine whether value2 is selected from table_a or table_b?
I've recently been successful at joining tables, but here's the challenge:
Two tables.
table_a has numerous columns including addresses. Unfortunately, some of the addresses are incorrect. Table_a is replaced regularly, is managed by somebody else, and is quite large. Making corrections to table_a should be avoided.
Table_b has a few columns with attributes assigned to people listed in the directory table_a. Can one of those attributes be a variable that determines whether the address comes from table_a or a custom address in table_b?
Thanks for any feedback. Maybe there is an easier solution!
cheers/Reber
|
michal
Joined: 17 Jun 2003
Posts: 537
Location: Slovakia
Posted: 2004-05-26 10:07
Back to top
Reply with quote
|
so if i understand right, you have table:
id, ... , join_table_name, join_table_id
join_table_name - which table to join (in this row)
join_table_id - id in that table
i don't know how sql handels this, and if it can handle it. in a similar problem i've created extra table as a shadow of all possible tables (but that's very ugly).
any ideas from sql gurus ?
|
reber
Joined: 23 Jun 2003
Posts: 10
Posted: 2004-05-26 18:54
Back to top
Reply with quote
|
Let's say the database has two tables with a key
-----
table_a
column.id
column.key
column.address
column.email
table_b
column.id
column.key
column.customaddress
column.change
-----
I'm was hoping the PME code would support a conditional statement while joining the tables.
Something like, if table_b.change = "yes" then use table_b.customaddress. Else use table_a.address.
----
Possible output for three column PME display:
| a.key | a.email | if b.change=yes then b.customaddress, else a.address |
-----
Does this make sense?
Maybe the value trigger isn't required. Example: if b.customaddress exists use it, else use a.address.
Thanks for your interest in my question, Michal.
cheers/Reber
|
hbernard
Joined: 23 Mar 2003
Posts: 159
Location: FRANCE
Posted: 2004-06-11 09:21
Back to top
Reply with quote
|
1. Have an hidden virtual field that is doing the custom join :
$opts['fdd']['dummy_field'] = array(
'input' => 'VR',
'options' => '',
'values' => Array(
'table' => 'table_b', //put the joined table here
'column' => 'id',
'description' => 'id', //useless, but required by the implementation
'join' => '$main_table.id = $join_table.id')
);
2. then, use 'sql' field option to display correct value
$opts['fdd']['address'] = array(
'name' => 'custom or initial address',
'sql' => 'if(table_b.address is null, table_a.address, table_b.address)'
);
But this will work only in LVF mode.
Does it fit your need ?
_________________
hbernard - phpMyEdit updater.
|
reber
Joined: 23 Jun 2003
Posts: 10
Posted: 2004-08-19 22:18
Back to top
Reply with quote
|
Here's how I tackled this.
| Code: |
# Ask whether to use substitute address
# Show only during Add, Change, or View
$opts['fdd']['use_address_update'] = array (
'name' => 'X/A',
'select' => 'D',
'sort' => true,
'options' => 'ACV'
);
$opts['fdd']['use_address_update']['values'] = array('','Yes');
#
#
# Address from uneditable source
# Then check if substutition necessary
$opts['fdd']['placeholder'] = array (
'name' => 'Address',
'sql' => 'PMEjoin1.streetAddress',
'options' => 'RCPDVL',
'sort' => true
);
$opts['fdd']['placeholder']['sql'] = 'IF(TRIM(use_address_update) = "Yes", address_update, PMEjoin1.streetAddress)';
#
#
# Column for substitute address
# Show only during Add, Change, or View
$opts['fdd']['address_update'] = array(
'name' => 'Updated Address',
'select' => 'T',
'maxlen' => 60,
'sort' => true,
'options' => 'ACV'
);
|
My follow up questions is this: Can parameters be modified by the data contained within a column?
For example, I'd like to hide the address_update field in Add/Change/View if the record in 'use_address_update' is false.
Cheers,
Reber
|
|
Post new topic
Reply to topic
|
|
|
|
Login
Registration
Slovak
