In order to get this working with a combination of fields from the linked table as well as the linked-linked table I had to further modify the code to use unique aliases for the different tables
| Code: |
...
/**/ $tbl_count = 0;
foreach ($desc['columns'] as $key => $val) {
if ($val) {
$tbl_count++;
// $qparts['select'] .= 'IFNULL(CAST('.$this->sd.$val.$this->ed.' AS CHAR),"")';
/**/ //$qparts['select'] .= 'IFNULL(CAST('.$this->sd.$val.$this->ed.' AS CHAR),"")';
/**/ if ($desc['values'][$key]) {
/**/ $qparts['select'] .= 'IFNULL(CAST(PGtbl'.$field_num.'_'.$tbl_count.'.'.$desc['values'][$key]['description'].' AS CHAR),"")';
/**/ $qparts['from'] .= ", ".$dbp.$desc['values'][$key]['table']." AS PGtbl".$field_num."_".$tbl_count;
/**/ if ($qparts['where'] != '') $qparts['where'] .= ' AND ';
/**/ $qparts['where'] .= "$table.".$this->sd.$val.$this->ed." = PGtbl".$field_num."_".$tbl_count.".".$desc['values'][$key]['column'];
...
|
I have to use the linked table as the name for the second link in a rather ugly and unnecessary join. i.e. dividends.holdingID -> sh_holding.ID, sh_holding.StockID -> sh_stock.ID I have to link sh_holding to sh_holding in order to display the two fields (purchasedate and quantity) along with the description from sh_stock.name (!!) like:
| Code: |
$opts['fdd']['holdingID'] = array(
'name' => 'HoldingID',
'select' => 'T',
'values' => array(
'table' => 'sh_holding',
'column' => 'ID',
'description' => array(
'columns' => array('ID', 'ID', 'StockID'),
'divs' => array(' [', '] ',''),
'values' => array(
array(
'table' => 'sh_holding',
'column' => 'ID',
'description' => 'Purchdate'
),
array(
'table' => 'sh_holding',
'column' => 'ID',
'description' => 'quantity'
),
array(
'table' => 'sh_stock',
'column' => 'ID',
'description' => 'name'
)
)
)
),
|
|
The simplest solution that I have found is not to use this wonderful patch at all, not to use phpMyEdit joins at all, and use SQL subquery in SELECT, with a dummy phpMyEdit field:
To show one-to-many values via 'device_to_recipes':
| Code: |
// Turn on $debug_query, and replace 'PMEtable0'.`id` with the renamed table name and column that you want from the SQL shown.
$opts['fdd']['dummy'] = array (
'name' => 'Recipes',
'sql' => "(SELECT GROUP_CONCAT(`name` SEPARATOR ', ') FROM `recipe` AS r JOIN `device_to_recipes` AS dr ON (r.`id` = dr.`recipe_id`) WHERE dr.`device_id` = PMEtable0.`id`)",
'options' => 'RACPDVL',
'escape' => false,
// 'URL' => 'mailto:',
'sort' => true
);
|
To show one-to-many values with ckickable links to edit this mapping:
| Code: |
// Turn on $debug_query, and replace 'PMEtable0'.`id` with the renamed table name and column that you want from the SQL shown.
// Link to edit the mapping.
$href_link = 'device_to_recipes.php?device_id=';
$opts['fdd']['dummy'] = array (
'name' => 'Recipes',
'sql' => "(SELECT GROUP_CONCAT(CONCAT('<a href=\"" . $href_link . "', `r`.`id` , '\">', '[', `r`.`id`, ']' ,`r`.`name`, '</a>') SEPARATOR ', ') FROM `recipe` AS r JOIN `device_to_recipes` AS dr ON (r.`id` = dr.`recipe_id`) WHERE dr.`device_id` = PMEtable0.`id`)",
'options' => 'RACPDVL',
'escape' => false,
// 'URL' => 'mailto:',
'sort' => true
);
|
And implement the $opts['filters'] in the link target (e.g. "device_to_recipes.php?device_id=").
Otherwise
, you can do simple SQL subquery select and implement the link in a php file:
| Code: |
$opts['fdd']['device_link'] = array(
'name' => 'Device',
'options' => 'VLF', // show only in view, list and filter.
'sql' => "(SELECT CONCAT('[', `id`, ']' , `name`) FROM `device` AS d WHERE d.`id` = PMEtable0.`device_id` ORDER BY d.`id` LIMIT 1 )",
'php' => 'desc_to_device_link.php',
'select' => 'T',
'sort' => true
);
|
'desc_to_device_link.php' file that generates an http link from the SQL result above:
| Code: |
<?php
preg_match_all('/^\[\d+\].*/', $value, $matches);
if ($matches){
$id = preg_replace('/\[(\d+)\].*/', "\\1", $value, 1);
echo "<a href=device.php?id=" . $id . "'>" . $value . "</a>";
} else {
echo $value;
}
?>
|
NOTICE:
SQL dummy fields are not editable. However for three level joins, you can define standard phpMyEdit join fields and enable on editable pages with the following options:
| Code: |
'options' => 'ACPD'
|
And enable the unediable SQL field on View, LIST and FILTER pages with the following options:
|
Login
Registration
Slovak
