JOIN, values from joined table to other columns

Discuss MySQL Ajax Table Editor Paid Version

JOIN, values from joined table to other columns

Postby makistar » Wed Dec 27, 2017 11:18 am

I try to join two tables
In the joined table (SellType) there are 3 columns
Name, FaktorA, FaktorB

In the table (Orders), where I like to join the 'SellType' table I have also 3 columns
Sell, A, B (A and B are hidden, they are just used for some calculations)

I now like to have a dropdown in the column 'Sell' with the values of 'Name' from 'SellType' (got that work with join)

When I add or edit a column to 'Orders' I now like to choose the column 'Sell' from dropdown and add the correct values based on 'Name' = 'Sell' from 'FaktorA', 'FaktorB' to the columns 'A' and 'B' from the joined table 'SellType'
makistar
 
Posts: 32
Joined: Mon Jan 07, 2013 10:48 am

Re: JOIN, values from joined table to other columns

Postby admin » Wed Dec 27, 2017 4:37 pm

I'm not sure I understand exactly what you are trying to accomplish but the select_query option may be what you are looking for.

https://mysqlajaxtableeditor.com/Docume ... lect_query
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: JOIN, values from joined table to other columns

Postby makistar » Wed Dec 27, 2017 5:04 pm

Even not sure, that The select solution is the right ohne :-)

Let me give you a example

SellType Table
|Name. |FaktorA|FaktorB|
–––––––––––––––––––––––––––––––––––––––
|Testname01| 3 | 5 |
–––––––––––––––––––––––––––––––––––––––
|Testname02| 9 | 1 |
–––––––––––––––––––––––––––––––––––––––
|Testname03| 4 | 6 |
–––––––––––––––––––––––––––––––––––––––

In Order Table I have the 3 columns
- SellType = Pull Down from Name Column in SellType Table
- A = Value from SellType Column FaktorA
- B = Value from SellType Column FaktorB

So, when I choose Testname02 from the pulldown
A should be filed with 9 and B should be filed with 1 (both values from the SellType Table)

Or, when I choose Testname03 from the pulldown
A should be filed with 4 and B should be filed with 6 (both values from the SellType Table)

For me it is not clear, how the select statement shoud look like
makistar
 
Posts: 32
Joined: Mon Jan 07, 2013 10:48 am

Re: JOIN, values from joined table to other columns

Postby makistar » Fri Dec 29, 2017 9:39 pm

OK, I tried it with the JOINTable example, but I could not bring it to work

public function updateLogin($empId,$instanceName)
{
$query = "select first_name, last_name from employees where id = :empId";
$queryParams = array('empId' => $empId);
$result = $this->Editor->doQuery($query,$queryParams);
if($row = $result->fetch())

The difference to the example is, that $empId in my case is not a number, it is a string and so the if($row = $result->fetch()) seems to be true and the suggested value is returned.

How can I fix that to work with $empId is a string
makistar
 
Posts: 32
Joined: Mon Jan 07, 2013 10:48 am

Re: JOIN, values from joined table to other columns

Postby admin » Fri Dec 29, 2017 10:33 pm

Could you post the code you have so far?
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: JOIN, values from joined table to other columns

Postby makistar » Sat Dec 30, 2017 8:00 am

Code: Select all
<?php
/*
 * Mysql Ajax Table Editor
 *
 * Copyright (c) 2014 Chris Kitchen <info@mysqlajaxtableeditor.com>
 * All rights reserved.
 *
 * See COPYING file for license information.
 *
 * Download the latest version from
 * http://www.mysqlajaxtableeditor.com
 */
require_once('DBC.php');
require_once('Common.php');
require_once('php/lang/LangVars-en.php');
require_once('php/AjaxTableEditor.php');
class JoinExample extends Common
{
   protected $mateInstances = array('mate1_');

   protected function displayHtml()
   {
      $html = '
         
         <br />
         
         <div class="mateAjaxLoaderDiv"><div id="ajaxLoader1"><img src="images/ajax_loader.gif" alt="Loading..." /></div></div>
         
         <br /><br />
         
         <div id="'.$this->mateInstances[0].'information">
         </div>

         <div id="mateTooltipErrorDiv" style="display: none;"></div>

         
         <div id="'.$this->mateInstances[0].'titleLayer" class="mateTitleDiv">
         </div>
         
         <div id="'.$this->mateInstances[0].'tableLayer" class="mateTableDiv">
         </div>
         
         <div id="'.$this->mateInstances[0].'updateInPlaceLayer" class="mateUpdateInPlaceDiv">
         </div>
         
         <div id="'.$this->mateInstances[0].'recordLayer" class="mateRecordLayerDiv">
         </div>      
         
         <div id="'.$this->mateInstances[0].'searchButtonsLayer" class="mateSearchBtnsDiv">
         </div>';
      echo $html;
      // Set default session configuration variables here
      $defaultSessionData['orderByColumn'] = 'employee_id';
      
      $defaultSessionData = base64_encode($this->Editor->jsonEncode($defaultSessionData));
      
      
      $javascript = '   
         <script type="text/javascript">
            var ' . $this->mateInstances[0] . ' = new mate("' . $this->mateInstances[0] . '");
            ' . $this->mateInstances[0] . '.setAjaxInfo({url: "' . $_SERVER['PHP_SELF'] . '", history: true});
            ' . $this->mateInstances[0] . '.init("' . $defaultSessionData . '");
         </script>';
      echo $javascript;
   }

   public function updateLogin($empId,$instanceName)
   {
      $query = "select vaa_Kategorie from V_ArtikelAktiv where vaa_Artikel_Bewegungen = :empId";
      $queryParams = array('empId' => $empId);
      $result = $this->Editor->doQuery($query,$queryParams);
      if($row = $result->fetch())
      {
         $errKategorie = $query . $queryParams;
         $this->Editor->setHtmlValue('sbw_Kategorie',$errKategorie);
      }
   }

   
   protected function initiateEditor()
   {
      $tableColumns['ID'] = array(
         'display_text' => 'ID',
         'perms' => 'QSXO'
      );
      $tableColumns['sbw_Datum'] = array(
         'display_text' => 'Datum',
         'perms' => 'EVCTAXQSHO',
         'display_mask' => 'date_format(sbw_Datum,"%d.%m.%Y")',
         'order_mask' => 'date_format(sbw_Datum,"%Y-%m-%d %T")',
         'range_mask' => 'date_format(sbw_Datum,"%Y-%m-%d %T")',
         'calendar' => array('js_format' => 'dd.mm.yy')
      );
      $tableColumns['sbw_Artikel'] = array(
         'display_text' => 'Artikel',
         'perms' => 'EVCTAXQS',
         'join' => array(
            'table' => 'V_ArtikelAktiv',
            'column' => 'vaa_Artikel_Bewegungen',
            'display_mask' => "vaa_Artikel_Bewegungen",
            'type' => 'left',
         ),
         'input_info' => 'onchange="'.$this->mateInstances[0].'.toAjaxTableEditor(\'update_login\',$(this).val());"'
      );
      $tableColumns['sbw_Kategorie'] = array(
         'display_text' => 'Kategorie',
         'perms' => 'EVCTAXQS'
      );
      $tableColumns['sbw_Bewegung'] = array(
         'display_text' => 'Bewegung',
         'perms' => 'EVCTAXQSHO',
         'join' => array(
            'table' => 'shop_bewegungsart',
            'column' => 'sba_Name',
            'display_mask' => "sba_Name",
            'type' => 'left',
         ),
      );
      $tableColumns['sbw_Anzahl'] = array(
         'display_text' => 'Anz.',
         'perms' => 'EVCTAXQSHO'
      );
      $tableColumns['sbw_Bemerkung'] = array(
         'display_text' => 'Bemerkung',   
         'perms' => 'EVCTAXQSHO'                               
      );
        $tableColumns['sbw_PreisTotal'] = array(
         'display_text' => 'Preis Total',                       
         'perms' => 'EVCTAXQSHO'
      );
        $tableColumns['sbw_BezahlArt'] = array(
          'display_text' => 'Bezahlung',                       
          'perms' => 'EVCTAXQSHO'
      );
        $tableColumns['sbw_BezahlungOK'] = array(
          'display_text' => 'bezahlt (Datum/Pfadiname)',
          'perms' => 'EVCTAXQSHO'
      );

      $tableName = 'shop_bewegungen';
      $primaryCol = 'ID';
      $errorFun = array(&$this,'logError');
      $permissions = 'EAVDQCSIXUM';
      
      require_once('php/AjaxTableEditor.php');
      $this->Editor = new AjaxTableEditor($tableName,$primaryCol,$errorFun,$permissions,$tableColumns);
      $this->Editor->setConfig('tableInfo','cellpadding="1" width="900" class="mateTable"');
      $this->Editor->setConfig('tableTitle','Multiple Edit / User Action / Join Example');
      $this->Editor->setConfig('addRowTitle','Add Login Info');
      $this->Editor->setConfig('editRowTitle','Edit Login Info');
      $this->Editor->setConfig('viewRowTitle','View Login Info');
      $userActions = array('update_login' => array(&$this,'updateLogin'));
      $this->Editor->setConfig('userActions',$userActions);
      $this->Editor->setConfig('editInPlace',true);
      $this->Editor->setConfig('addInPlace',true);
      //$this->Editor->setConfig('viewQuery',true);
   }
   
   
   function __construct()
   {
      session_start();
      ob_start();
      $this->initiateEditor();
      if(isset($_POST['json']))
      {
         if(ini_get('magic_quotes_gpc'))
         {
            $_POST['json'] = stripslashes($_POST['json']);
         }
         $this->Editor->data = $this->Editor->jsonDecode($_POST['json'],true);
         $this->Editor->setDefaults();
         $this->Editor->main();
      }
      else if(isset($_GET['mate_export']))
      {
         $this->Editor->data['sessionData'] = $_GET['session_data'];
         $this->Editor->setDefaults();
         ob_end_clean();
         header('Cache-Control: no-cache, must-revalidate');
         header('Pragma: no-cache');
         header('Content-type: application/x-msexcel');
         header('Content-Type: text/csv');
         header('Content-Disposition: attachment; filename="'.$this->Editor->tableName.'.csv"');
         // Add utf-8 signature for windows/excel
         echo chr(0xEF).chr(0xBB).chr(0xBF);
         echo $this->Editor->exportInfo();
         exit();
      }
      else
      {
         $this->displayHeaderHtml();
         $this->displayHtml();
         $this->displayFooterHtml();
      }
   }
}
$page = new JoinExample();
?>
makistar
 
Posts: 32
Joined: Mon Jan 07, 2013 10:48 am

Re: JOIN, values from joined table to other columns

Postby admin » Sat Dec 30, 2017 4:17 pm

Because you have editInPlace and addInPlace set, a row# is appended to the input ids. This means you will have to modify the data that gets passed to the callback function to include the row #. To do this change the code on line 103
from
Code: Select all
         'input_info' => 'onchange="'.$this->mateInstances[0].'.toAjaxTableEditor(\'update_login\',$(this).val());"'

to
Code: Select all
   'input_info' => 'onchange="'.$this->mateInstances[0].'.toAjaxTableEditor(\'update_login\',{rowNum: $(this).closest(\'tr\').attr(\'row_num\'), value: $(this).val()});"'


This will pass an array to your callback function with a rowNum and a value. Then you can modify your callback function to something like:

Code: Select all
   public function updateLogin($info,$instanceName)
   {
      $rowNum = $info['rowNum'];
      $empId = $info['value'];
      $query = "select vaa_Kategorie from V_ArtikelAktiv where vaa_Artikel_Bewegungen = :empId";
      $queryParams = array('empId' => $empId);
      $result = $this->Editor->doQuery($query,$queryParams);
      if($row = $result->fetch())
      {
         $errKategorie = $query . $queryParams;
         $this->Editor->setHtmlValue('sbw_Kategorie_' . $rowNum,$errKategorie);
      }
   }


Notice how I've appended the row # onto the element id when setting the value.

Hope this helps.
admin
Site Admin
 
Posts: 1502
Joined: Fri Jul 11, 2008 1:34 am

Re: JOIN, values from joined table to other columns

Postby makistar » Sun Dec 31, 2017 10:11 am

Thank you very much...
I implemented your changes to the code, but unfortunately it is still the same... I only get the $errKategorie and not the correct Kategorie value...

V_ArtikelAktiv is a view

Code: Select all
SQL SECURITY DEFINER VIEW `V_ArtikelAktiv`  AS  select `sak`.`ID` AS `ID`,`sak`.`sak_Kategorie` AS `vaa_Kategorie`,`sak`.`sak_Artikel` AS `vaa_Artikel`,`sak`.`sak_Preis` AS `vaa_Preis`,concat(`sak`.`sak_Artikel`,' (CHF ',`sak`.`sak_Preis`,')') AS `vaa_Artikel_Bewegungen` from `shop_artikel` `sak` where (`sak`.`sak_Aktiv` > 0) ;


vaa_Artikel_Bewegungen is a string (example: test0 (CHF 0.00)
vaa_Kategorie is also a string (example: clothes


Structure of table shop_bewegungen

Code: Select all
CREATE TABLE `shop_bewegungen` (
  `ID` int(30) NOT NULL,
  `sbw_Datum` date NOT NULL,
  `sbw_Kategorie` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `sbw_Artikel` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `sbw_Bewegung` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `sbw_Anzahl` int(4) NOT NULL,
  `sbw_PreisTotal` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sbw_Bemerkung` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sbw_BezahlArt` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sbw_BezahlungOK` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indizes der exportierten Tabellen
--

--
-- Indizes für die Tabelle `shop_bewegungen`
--
ALTER TABLE `shop_bewegungen`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `IDX_BEWEGUNGEN` (`ID`) USING BTREE;

--
-- AUTO_INCREMENT für exportierte Tabellen
--

--
-- AUTO_INCREMENT für Tabelle `shop_bewegungen`
--
ALTER TABLE `shop_bewegungen`
  MODIFY `ID` int(30) NOT NULL AUTO_INCREMENT;
makistar
 
Posts: 32
Joined: Mon Jan 07, 2013 10:48 am

Re: JOIN, values from joined table to other columns

Postby makistar » Wed Jan 03, 2018 7:13 pm

In some cases, your code changes tries to select the Kategorie:
- when I edit a existing line and change the value for Artikel
--> I get a message window: "#mate1_sbw_Kategorie1" does not exist (where the number at the end represents the line number)
- when I try to add a new line, I get all times the value $errKategorie
makistar
 
Posts: 32
Joined: Mon Jan 07, 2013 10:48 am


Return to Paid Version

Who is online

Users browsing this forum: No registered users and 1 guest

cron