Platon Technologies
not logged in Login Registration
EnglishSlovak
open source software development celebrating 10 years of open source development! Friday, March 29, 2024
About Us
Magazine
Open Source
CVS
Services
Index  »  Projects  »  phpMyEdit  »  Forum  »  Advanced table lookup -- joining three tables

phpMyEdit Features     Advanced table lookup -- joining three tables
Post new topic   Reply to topic   Goto page Previous  1, 2, 3  Next  
 
GoG     Joined: 27 Mar 2005   Posts: 3  
Post Posted: 2005-03-27 16:21
Back to top  Reply with quote     

Hi all,

I'd like to thank you all for this easy to use script. Haven't posted before, but feel like I've read just about every post on this forum.

There is, however, still a problem when I try to implement it and although I have searched for an answer I can understand, I can't seem to be able to find one.
It might be because I keep jumping from bug report to forum message to bug report written for the last 3 years or so. I'm not sure what is current and what's not.

I have downloaded and the class written by Ben/Jestapher and by reading this thread I understood it is possible to edit fields from joined tables. Am I correct?

Have gotten it so far to be able to join a second table, but it seems restricted to only one column and the results aren't editable and only show in a dropdown list. Am fairly sure I'm doing something wrong, but will find out how to do it if the answer to the first question stated above (the edit a join question) is 'yes'.

Sorry to bother everyone. Have read the same question from others a number of times so it must be a drag right now. I just don't understand all of it.

Best regards,
GoG

 
GoG     Joined: 27 Mar 2005   Posts: 3  
Post Posted: 2005-03-28 18:23
Back to top  Reply with quote     

Hello again,

Before someone points to the manual, I'd like to state that I have read it - paid extra attention to 'Table Lookup' and 'Column Joining' - but was unable to find a conclusive answer.

I know tables can be joined, but I'm not sure if the joined table can be edited.

Gr,
GoG

 
michal     Joined: 17 Jun 2003   Posts: 537   Location: Slovakia
Post Posted: 2005-03-29 10:15
Back to top  Reply with quote     

you can edit joined tables in meaning that you can change the foreign key, however you cannot edit values in the joined table. you have to do an extra pme page for that purpose (two extra clicks for the user). have a look at bug #304 for an example

 
GoG     Joined: 27 Mar 2005   Posts: 3  
Post Posted: 2005-03-29 13:10
Back to top  Reply with quote     

Thanks! Will try. Am not sure extra clicking is desireable but will give it a try.

Gr,
GoG

 
gasman9     Joined: 02 Feb 2006   Posts: 1  
Post Posted: 2006-02-03 12:33
Back to top  Reply with quote     

Hi all!

Is that possible to apply these nice changes to v 5.5?

 
Sebzap     Joined: 05 Oct 2007   Posts: 5  
Post Posted: 2007-10-05 11:11
Back to top  Reply with quote     

...or to version 5.7.1?

 
anax     Joined: 19 Oct 2007   Posts: 6  
Post Posted: 2007-11-05 05:01
Back to top  Reply with quote     

I've been trying hard to implement this patch (which would really help me), but I'm having trouble working out the changes. Is there a PME class available with the patch included? or instructions about the syntax used in the patch?

 
thewitt     Joined: 23 Nov 2007   Posts: 4  
Post Posted: 2007-12-25 06:19
Back to top  Reply with quote     

Has anyone applied this successfully to 5.7.1 yet?

The ability to join 3 tables seems to be so fundamental to a normalized database.

Thanks,

-t

 
mebeling     Joined: 05 Nov 2007   Posts: 2  
Post Posted: 2008-04-19 13:20
Back to top  Reply with quote     

I could use also 5.7.1 multi-table lookup tweaked version..

It would be very nice if someone would patch 5.7.1 and release the tweaked version.

 
twilfong     Joined: 13 May 2008   Posts: 1  
Post Posted: 2008-05-14 00:57
Back to top  Reply with quote     

OK, below is a patch for 5.7.1

It is a modified version of Ben's (jestapher) patch, taking into account the changes to 5.7.1, and also using ['values']['description'] instead of 'columns' to allow for easier modifying later to allow for further nested joins.

The field definition will look like this:
Code:

$opts['fdd']['fk_t1id'] = array(
  'name'     => 'fk_t1id',
  'select'   => 'T',
  'values'   => array(
    'table'       => 't1',
    'column'      => 'id',
    'description' => array(
      'columns' => array('fk_t2id','fk_t3id'),
      'divs' => array('-'),
      'values' => array(
        array (
          'table'       => 't2',
          'column'      => 'id',
          'description' => 'name'
        ), array (
          'table'       => 't3',
          'column'      => 'id',
          'description' => 'name'
        )
      )
    )
  )
);

i.e. the difference from Ben's format is that

$opts['fdd']['fk_t1id']['values']['description']['values'][$i]['columns']

is replaced with

$opts['fdd']['fk_t1id']['values']['description']['values'][$i]['description']

which can ultimately be another array (though that isn't implemented in the patch below.)

The patch follows:

Code:

--- phpMyEdit.class.php.orig   2008-05-13 12:22:02.000000000 -0700
+++ phpMyEdit.class.php   2008-05-13 15:52:03.000000000 -0700
@@ -441,6 +441,12 @@
      $qparts['type'] = 'select';
      if ($table != $this->sd.$this->ed) {
         $qparts['select'] = 'DISTINCT '.$table.'.'.$this->sd.$key.$this->ed;
+         $qparts['from'] = $dbp.$table;
+         $ar = array(
+               'table'       => $table,
+               'column'      => $column,
+               'description' => $desc);
+         $qparts['where'] = $this->substituteVars($this->fdd[$field_num]['values']['filters'], $ar);
         if ($desc && is_array($desc) && is_array($desc['columns'])) {
            $qparts['select'] .= ',CONCAT('; // )
            $num_cols = sizeof($desc['columns']);
@@ -449,7 +455,18 @@
            }
            foreach ($desc['columns'] as $key => $val) {
               if ($val) {
-                  $qparts['select'] .= 'IFNULL(CAST('.$this->sd.$val.$this->ed.' AS CHAR),"")';
+
+// ** 3-table join patch -- set_values_from_table
+                  //$qparts['select'] .= 'IFNULL(CAST('.$this->sd.$val.$this->ed.' AS CHAR),"")';
+                  if ($desc['values'][$key]) {
+                     $qparts['select'] .= 'IFNULL(CAST('.$desc['values'][$key]['table'].'.'.$desc['values'][$key]['description'].' AS CHAR),"")';
+                     $qparts['from'] .= ", ".$dbp.$desc['values'][$key]['table'];
+                     if ($qparts['where'] != '') $qparts['where'] .= ' AND ';
+                     $qparts['where'] .= "$table.".$this->sd.$val.$this->ed." = " . $desc['values'][$key]['table'] . '.' . $desc['values'][$key]['column'];
+                  }
+                  else  $qparts['select'] .= 'IFNULL(CAST('.$this->sd.$val.$this->ed.' AS CHAR),"")';
+// ** end patch
+
                  if ($desc['divs'][$key]) {
                     $qparts['select'] .= ',"'.addslashes($desc['divs'][$key]).'"';
                  }
@@ -467,12 +484,6 @@
         } else if ($key) {
            $qparts['orderby'] = $this->sd.$key.$this->ed;
         }
-         $qparts['from'] = $dbp.$table;
-         $ar = array(
-               'table'       => $table,
-               'column'      => $column,
-               'description' => $desc);
-         $qparts['where'] = $this->substituteVars($this->fdd[$field_num]['values']['filters'], $ar);
         if ($this->fdd[$field_num]['values']['orderby']) {
            $qparts['orderby'] = $this->substituteVars($this->fdd[$field_num]['values']['orderby'], $ar);
         }
@@ -513,7 +524,16 @@
               }
               foreach ($desc['columns'] as $key => $val) {
                  if ($val) {
-                     $ret .= 'IFNULL(CAST('.$this->sd.'PMEjoin'.$field.$this->ed.'.'.$this->sd.$val.$this->ed.' AS CHAR),"")';
+
+// ** 3-table join patch -- fqn
+                     //$ret .= 'IFNULL(CAST('.$this->sd.'PMEjoin'.$field.$this->ed.'.'.$this->sd.$val.$this->ed.' AS CHAR),"")';
+                     // to ensure we get a unique table aliase name, add the current alias number to the total fields
+                     if ($desc['values'][$key]) {
+                          $ret .= 'IFNULL(CAST('.$this->sd.'PMEjoin'.(sizeof($this->fds)+$field).$this->ed.'.'.$desc['values'][$key]['description'].' AS CHAR),"")';
+                     }
+                     else  $ret .= 'IFNULL(CAST('.$this->sd.'PMEjoin'.$field.$this->ed.'.'.$this->sd.$val.$this->ed.' AS CHAR),"")';
+// ** end patch
+
                     if ($desc['divs'][$key]) {
                        $ret .= ',"'.addslashes($desc['divs'][$key]).'"';
                     }
@@ -639,6 +659,16 @@
               ? $this->substituteVars($this->fdd[$main_column]['values']['join'], $ar)
               : "$join_table.$join_column = $main_table.".$this->sd.$main_column.$this->ed;
            $join_clause .= ')';
+
+// ** 3-table join patch -- get_SQL_join_clause
+            $desc = $this->fdd[$main_column]['values']['description'];
+            if (is_array($desc['values'])) for ($i = 0; $i < sizeof($desc['values']); $i++) {
+               $join_table_new = $this->sd.'PMEjoin'.($numfds+$k+$i).$this->ed;
+               $join_clause .= " LEFT JOIN $dbp" .$desc['values'][$i]['table']. " AS $join_table_new";
+               $join_clause .= " ON $join_table_new." .$desc['values'][$i]['column']. " = $join_table." .$desc['columns'][$i];
+            }
+// ** end patch
+
         }
      }
      return $join_clause;

--Tim

 
vstsjfh     Joined: 23 Mar 2006   Posts: 6  
Post Posted: 2008-06-28 11:23
Back to top  Reply with quote     

I've used this patch it's great and it works fine, but after adding the following sentence: look for "vstsjfh 2008/06/25" (increment $field into the loop) thanks and very nice work!!


@@ -513,7 +524,16 @@
}
foreach ($desc['columns'] as $key => $val) {
if ($val) {
- $ret .= 'IFNULL(CAST('.$this->sd.'PMEjoin'.$field.$this->ed.'.'.$this->sd.$val.$this->ed.' AS CHAR),"")';
+
+// ** 3-table join patch -- fqn
+ //$ret .= 'IFNULL(CAST('.$this->sd.'PMEjoin'.$field.$this->ed.'.'.$this->sd.$val.$this->ed.' AS CHAR),"")';
+ // to ensure we get a unique table aliase name, add the current alias number to the total fields
+ if ($desc['values'][$key]) {
+ $ret .= 'IFNULL(CAST('.$this->sd.'PMEjoin'.(sizeof($this->fds)+$field).$this->ed.'.'.$desc['values'][$key]['description'].' AS CHAR),"")';
+ $field +=1; // vstsjfh 2008/06/25
+
}
+ else $ret .= 'IFNULL(CAST('.$this->sd.'PMEjoin'.$field.$this->ed.'.'.$this->sd.$val.$this->ed.' AS CHAR),"")';
+// ** end patch
+
if ($desc['divs'][$key]) {
$ret .= ',"'.addslashes($desc['divs'][$key]).'"';
}

 
vstsjfh     Joined: 23 Mar 2006   Posts: 6  
Post Posted: 2008-06-28 18:28
Back to top  Reply with quote     

I've used the "twilfong" patch patched again with "vstsjfh" patch, it works fine not only with 3 tables but with four, you

can see it in the example that follows.
It works fine while I use only one field (fk_t1_id) for a drop down menu selection using table look up join, as I said

with a four tables join.
When I add a second field (fk_t1_id2) for a drop down menu selection using table look up join with two new tables join,

the alias name of the last two tables step on the last two alias of the first field, then MySQL sets an error of

duplicate alias.

What I'm doing wrong?, Is there a bug in the code for this approach or I'm trying to do something that is not possible?.

Example:

$opts['fdd']['fk_t1_id'] = array(
'name' => 'fk_t1_id',
'select' => 'T',
'values' => array(
'table' => 't1',
'column' => 'id',
'description' => array(
'columns' => array('fk_t2id','fk_t3id','fk_t4id',fk_t5id'), <==== works fine when no more fields use join feature
'divs' => array('-'),
'values' => array(
array (
'table' => 't2',
'column' => 'id',
'description' => 'name_t2'
),
array (
'table' => 't3',
'column' => 'id',
'description' => 'name_t3'
),
array (
'table' => 't4',
'column' => 'id',
'description' => 'name_t4'
),
array (
'table' => 't5',
'column' => 'id',
'description' => 'name_t5'
)
)
)
)
);
$opts['fdd']['fk_t1_id2'] = array(
'name' => 'fk_t1_id2',
'select' => 'T',
'values' => array(
'table' => 't1',
'column' => 'id2',
'description' => array(
'columns' => array('fk_t6id','fk_t7id'), <==== doesn't work, duplicate alias for the two tables, same alias as t4

and t5 .
'divs' => array('-'),
'values' => array(
array (
'table' => 't6',
'column' => 'id',
'description' => 'name_t6'
),
array (
'table' => 't7',
'column' => 'id',
'description' => 'name_t7'
)
)
)

 
Kev     Joined: 02 Jul 2008   Posts: 2  
Post Posted: 2008-07-02 20:28
Back to top  Reply with quote     

Hi everyone,

Sorry for the bother, but how do we apply the patch to the phpMyEdit.class.php file and/or our system? I'm a newbie to phpMyEdit, but my project requires joining tables.

If possible, could you upload the new php files and/or instructions on how to go about setting up the files to enable joining?

Thanks so much!!! Keep up the good work, guys!!

 
airam.mateos     Joined: 18 Aug 2008   Posts: 4  
Post Posted: 2008-08-18 15:57
Back to top  Reply with quote     

Hi all, i'm a new user of phpmyedit and i want to know where i have to write de code of the patch of 5.7.1. Thanks for all.

 
sagiben     Joined: 27 Jan 2009   Posts: 15  
Post Posted: 2009-01-27 20:49
Back to top  Reply with quote     

Hi all,

Does anyone know if there is a similar patch for
phpMyEdit-mce-cal.class.php ?
I need to use the "multi join" option and a calender
in the same form.

Thank you.

 
Post new topic   Reply to topic   Goto page Previous  1, 2, 3  Next  

Copyright © 2002-2006 Platon Group
Site powered by Metafox CMS
Go to Top · Feedback form · Application form
Report bug on PLATON.SK website · Terms of use · Privacy policy