Platon Technologies
not logged in Login Registration
EnglishSlovak
open source software development celebrating 10 years of open source development! Thursday, March 28, 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 1, 2, 3  Next  
 
jestapher     Joined: 09 Jun 2003   Posts: 5  
Post Posted: 2003-06-20 19:01
Back to top  Reply with quote     

I was looking for the same feature requested in this bug report:

http://opensource.platon.sk/projects/bug_view_advanced_page.php?f_bug_id=41

I am making a scheduling app for a large all-volunteer festival and I have the following tables:

Code:
staff: id, name
crew: id, name
shift: id, crew, start_time
staff_to_shift: id, staff, shift


When I view/edit the staff_to_shift table, I can replace the staff column with data from the staff table. And I can replace the shift column with data from the shift table. What I can't do is replace the shift column with data from the shift AND crew table. I can get the shift column to say "2 - 2003-08-12 07:00:00" but I can't get it to say "Main Stage - 2003-08-12 07:00:00" -- to replace shift.crew with crew.name.

By editing two functions -- fqn() and create_join_clause() -- and adding three new variables to the configuration, I have added the ability to do joins on three tables. Here is how I did it:

In my staff_to_shift.php, here is the definition of the shift column. The last three variable are the additions:

Code:
$opts['fdd']['shift'] = array(
  'name'     => 'Shift',
  'select'   => 'T',
  'maxlen'   => 10,
  'default'  => '0',
  'required' => true,
  'sort'     => true,
  'values'   => array(
    'table'       => 'shift',
    'column'      => 'id',
  )
);
$opts['fdd']['shift']['values']['description']['columns'][0] = 'crew';
$opts['fdd']['shift']['values']['description']['columns'][1] = 'start';
$opts['fdd']['shift']['values']['description']['divs'][0] = ' - ';
// new variables
$opts['fdd']['shift']['values']['description']['values'][0]['table'] = 'crew';
$opts['fdd']['shift']['values']['description']['values'][0]['column'] = 'id';
// column to display
$opts['fdd']['shift']['values']['description']['values'][0]['columns'] = 'name';


In phpMyEdit.class.php make the following changes. In the function fqn() replace the line:

Code:
$ret .= 'PMEjoin'.$field.'.'.$val;


with:

Code:
if ($desc['values'][$key])
/* to ensure we get a unique table aliase name, add the current alias number to the total fields. */
  $ret .= 'PMEjoin'.(sizeof($this->fds)+$field).'.'.$desc['values'][$key]['columns'];
else
  $ret .= 'PMEjoin'.$field.'.'.$val;


In the function create_join_clause() after this bit of code:

Code:
if (!in_array($alias,$tbs)) {
  $join .= " LEFT OUTER JOIN $db.$table AS $alias";
  $join .= " ON $alias.$id = PMEtable0.$field";
  $tbs[]=$alias;
}


add:

Code:
if (is_array($desc['values'])) {
  $alias_new = 'PMEjoin'.(sizeof($this->fds)+$k);
  $join .= " LEFT JOIN $db." . $desc['values'][0]['table'] . " AS $alias_new";
  $join .= " ON $alias_new." . $desc['values'][0]['column'] . " = $alias." . $desc['colu
mns'][0];
}


Notice the comment in the fqn() function. As I understand it, phpMyEdit uses the current table's field number to generate unique table alias names. If you are replacing field 1 with data from another table, that table will be aliased as PMEjoin1. As I understand it, there shouldn't be any table alias names with a number higher than the total number of fields in the current table.

To get a unique table alias name for my third table, I add the current field number to the total number of fields in the table. In my case I have three columns (0, 1, 2) and I'm doing this three table join on the third column (2). The joined third table is thus called PMEjoin5 (3+2).

This only works while VIEWING records, not while editing. I hope to update the edit code soon. It also doesn't do any error checking, so make sure those three variables are defined in your config file.

-- Ben


Last edited by jestapher on 2003-06-20 20:58, edited 1 time in total

 
jestapher     Joined: 09 Jun 2003   Posts: 5  
Post Posted: 2003-06-20 20:39
Back to top  Reply with quote     

I just finished the code to make this work when modifying data. All of the changes are to the function set_values_from_table().

First, I moved the following two lines up a bit:

Code:
$qparts['from'] = "$db.$table";
$qparts['where'] = $this->fdd[$field_num]['values']['filters'];


These need to go somewhere above the foreach() statement. I put them after this line:

Code:
$qparts['type'] = 'select';


Second, change this line:

Code:
$qparts['select'] = 'DISTINCT '.$key;


to:

Code:
$qparts['select'] = "DISTINCT $table.$key";


Third and last, change this line:

Code:
$qparts['select'] .= $val . "";


to:

Code:
if ($desc['values'][$key]) {
  $qparts['select'] .= $desc['values'][$key]['table'] . '.' . $desc['values'][$key]['columns'];
  $qparts['from'] .= ", $db." . $desc['values'][$key]['table'];
  if ($qparts['where'] != '')
    $qparts['where'] .= ' AND ';
  $qparts['where'] .= "$table.$val = " . $desc['values'][$key]['table'] . '.' . $desc['values'][$key]['column'];
}
else
  $qparts['select'] .= $val . "";


As mentioned in my previous post, this doesn't do any error checking so make sure you have the three variables defined in your config file.

Hope that's useful to some people.

-- Ben

 
bernie95     Joined: 03 Sep 2003   Posts: 10  
Post Posted: 2003-09-06 11:27
Back to top  Reply with quote     

jestapher wrote:

I just finished the code to make this work when modifying data. All of the changes are to the function set_values_from_table().


Hello,

Continuing on the above path, I just made a more thorough implementation of the idea of jestapher . My implementation allows arbitrary nesting of table references.

What I've done was improving a bit the syntax proposed by jestapher:
In the 'description' part of the 'values' definition for a field, one can include a new 'values' entry, which must then be an array indexed by field names that appear in the 'columns' array; each of this array elements is then composed exactly as a normal 'values' field definition entry, and thus recursively as needed.

Of course, some possibilities of the basic 'values' entry are useless but the general pattern of 'table', 'column', 'description' is fully supported.

Also 'orderby' clauses are propagated so that if we request ordering on the ID field, and the corresponding 'values' entry specifies an ordering on the replacement fields, the output is ordered on the replacement fields, not on the original ID field.

Now how is this done? This is simpler than it seems (thanks to jestapher to have the first idea, I just improve it):

My table structure was, simplified, as follows:

Books: ID, Title, AuthorID
Authors: ID, FirstName, LastName
Shelf: ID, Position
ShelfContents: ShelfID, BookID

The problem is, when displaying, or changing, the content of a shelf is displaying books as 'Author.FirstName Author.LastName - Title"; Moreover, when selecting a book to place on a shelf, I want books sorted by "Author.LastName, Author.FirstName, Title".

My field definitions are then these:
Code:

$tocs_opts['fdd']['bookID'] = array(
  'name'     => 'Book',
  'select'   => 'T',
  'maxlen'   => 8,
  'required' => true,
  'sort'     => true,
  'values'     => array(
    'table'      => 'books',
    'column'     => 'ID',
    'description'=> array(
      'columns'    => array('AuthorID', 'Title'),
      'divs'       => array(' - '),
      'orderby'    => "AuthorID, Title",
      'values'     => array(
        'AuthorID'   =>   array(
          'table'      => 'authors',
          'column'     => 'ID',
          'description'=> array(
            'columns'    => array('FirstName', 'LastName'),
            'divs'       => array(' '),
            'orderby'    => "LastName, FirstName"
          )
        )
      )
    )
  )
);


One see above the 'values' subfield of the first-level 'description' field. In fact in my real application there is also a LanguageID field in the Author structure that I may have to display in some cases, that refers to a language table rather than a fixed language set, so I would have to add a level more of 'values' in the inner 'description' field.

The change in the phpMyEdit-5.3 code is as follows:


1) I added (just before set_values ) two functions to qualify field names in the orderby clauses and replace ID field names by the sub orderby clauses:
Code:

   function qualify_fields($fields, $db, $table) /* {{{ */
   {
      $flds = explode(',', $fields);
      $new_flds = array();
      foreach ($flds as $field) {
         $field = trim($field);
         $fld = explode('.', $field);
         if (sizeof($fld) == 1) {
            $field = "$db.$table.$field";
         } elseif (sizeof($fld) == 2) {
            $field = "$db.$field";
         }
         $new_flds[] = $field;
      }
      $fields = implode(',', $new_flds);
      return $fields;
   } /* }}} */

   function replace_field($fields, $field, $replace) /* {{{ */
   {
      $flds = explode(',', $fields);
      $new_flds = array();
      foreach ($flds as $fld) {
         $fld = trim($fld);
         if ($fld == $field) {
            $new_flds[] = $replace;
         } else {
            $new_flds[] = $fld;
         }
      }
      $fields = implode(',', $new_flds);
      return $fields;
   } /* }}} */


2) For enhancing set_values_from_table I add (just before it) two new functions, needed to recursively walk through the nested 'values'/'description' fields:
Code:

   function set_subvalues_from_valdesc(&$valdesc, $key, &$qparts, &$tbs) /* {{{ */
   {
      $db    = ($valdesc['db']) ? $valdesc['db'] : $this->db;
      $table = $valdesc['table'];
      $id    = $valdesc['column'];
      $desc  = &$valdesc['description'];
      if ($table && $id && $desc && is_array($desc)) {
         $dbtable="$db.$table";
         $this->set_subvalues_from_table($dbtable, $desc, $qparts, $tbs);
         if (!in_array($dbtable,$tbs)) {
            $tbs[] = "$dbtable";
            $qparts['from'] .= ",$dbtable";
         }
         if ($qparts['where'] != '')
            $qparts['where'] .= ' AND ';
         $qparts['where'] .= "$key = $dbtable.$id";
         if ($desc['orderby']) {
            $qparts['orderby'] =
               $this->replace_field(
                  $qparts['orderby'],
                  $key,
                  $this->qualify_fields($desc['orderby'], $db, $table)
               );
         }
         return TRUE;
      }
      return FALSE;
   } /* }}} */

   function set_subvalues_from_table($dbtable, &$desc, &$qparts, &$tbs) /* {{{ */
   {
      $num_cols = sizeof($desc['columns']);
      if (isset($desc['divs'][-1])) {
         $qparts['select'] .= '"'.addslashes($desc['divs'][-1]).'",';
      }
      $separ='';
      foreach ($desc['columns'] as $idx => $val) {
         if ($val) {
            $valdesc = &$desc['values'][$val];
            if (!is_array($valdesc)
            ||  !is_array($valdesc['description'])
            ||  !is_array($valdesc['description']['columns'])
            ||  !$this->set_subvalues_from_valdesc($valdesc, "$dbtable.$val", $qparts, $tbs)) {
               $qparts['select'] .= "$separ$dbtable.$val";
            }
            if ($desc['divs'][$idx]) {
               $qparts['select'] .= ',"'.addslashes($desc['divs'][$idx]).'"';
            }
            $separ = ',';
         }
      }
   } /* }}} */

This could be a single function, but it seems more comprehensible like this.

3) Then I made some small modifications to set_values_from_table :
Code:

   $table = &$this->fdd[$field_num]['values']['table'];
   $key   = &$this->fdd[$field_num]['values']['column'];
   $desc  = &$this->fdd[$field_num]['values']['description'];
   $qparts['type'] = 'select';
   if ($table) {
+      $tb = "$db.$table";
+      $tbs[] = $tb;
+      $qparts['from'] = "$db.$table";
+      $qparts['where'] = $this->fdd[$field_num]['values']['filters'];
-      $qparts['select'] = 'DISTINCT '.$key;
+      $qparts['select'] = 'DISTINCT '.$table.'.'.$key;
      if ($desc && is_array($desc) && is_array($desc['columns'])) {
+         $qparts['orderby'] =
+            empty($desc['orderby'])
+               ? 'select_alias_'.$field_num
+               : $this->qualify_fields($desc['orderby'], $db, $table);
         $qparts['select'] .= ',CONCAT('; // )
+         $this->set_subvalues_from_table($tb, $desc, $qparts, $tbs);
-         $num_cols = sizeof($desc['columns']);
-         if (isset($desc['divs'][-1])) {
-            $qparts['select'] .= '"'.addslashes($desc['divs'][-1]).'",';
-         }
-         foreach ($desc['columns'] as $key => $val) {
-            if ($val) {
-               $qparts['select'] .= $val;
-               if ($desc['divs'][$key]) {
-                  $qparts['select'] .= ',"'.addslashes($desc['divs'][$key]).'"';
-               }
-               $qparts['select'] .= ',';
-            }
-         }
+         $qparts['select'] .= ')';
-         $qparts['select']{strlen($qparts['select']) - 1} = ')';
         $qparts['select'] .= ' AS select_alias_'.$field_num;
-         $qparts['orderby'] = empty($desc['orderby'])
-            ? 'select_alias_'.$field_num : $desc['orderby'];
      } else if ($desc && is_array($desc)) {
         // TODO


In fact the code suppressed was moved in the function, or in one of the two functions created in step 2.

This concludes the changes needed to have the add and modify modes work with multiple joins.

The work to display them is not a lot more difficult, and is described now.

4) I added a new function ( sub_fqn ) just before fqn :
Code:

   /*
    * recursively get subtable references
    */
   function sub_fqn($desc, $field) /* {{{ */
   {
      $num_cols = sizeof($desc['columns']);
      if (isset($desc['divs'][-1])) {
         $ret = '"'.addslashes($desc['divs'][-1]).'",';
      } else {
         $ret = '';
      }
      $separ = '';
      foreach ($desc['columns'] as $key => $val) {
         if ($val) {
            $ret .= $separ;
            if ($desc['values'][$val]
               && is_array($desc['values'][$val]['description'])
               && is_array($desc['values'][$val]['description']['columns'])) {
               $ret .= $this->sub_fqn($desc['values'][$val]['description'], $field . '_' . $key);
            } else {
               $ret .= 'PMEjoin'.$field.'.'.$val;
            }
            if ($desc['divs'][$key]) {
               $ret .= ',"'.addslashes($desc['divs'][$key]).'"';
            }
            $separ = ',';
         }
      }
      return $ret;
   }


5) I changed fqn to use the just created function:
Code:

   if (is_array($desc) && is_array($desc['columns'])) {
      $ret      = 'CONCAT('; // )
+      $ret .= $this->sub_fqn($desc, $field);
-      $num_cols = sizeof($desc['columns']);
-      if (isset($desc['divs'][-1])) {
-         $ret .= '"'.addslashes($desc['divs'][-1]).'",';
-      }
-      foreach ($desc['columns'] as $key => $val) {
-         if ($val) {
-            $ret .= 'PMEjoin'.$field.'.'.$val;
-            if ($desc['divs'][$key]) {
-               $ret .= ',"'.addslashes($desc['divs'][$key]).'"';
-            }
-            $ret .= ',';
-         }
-      }
+      $ret .= ')';
-      $ret{strlen($ret) - 1} = ')';
   } else if (is_array($desc)) {
      // TODO

Again, the code suppressed was in fact moved in the previous function.

6) I added create_join_sub_clause before create_join_clause :
Code:

   function create_join_sub_clause($db, $table, $id, $desc, $idfield, $alias, &$tbs) /* {{{ */
   {
      $join = '';
      if ($table != '' && $desc != '' && $id != '') {
         if (!in_array($alias,$tbs)) {
            if(!$db) { $db = $this->db; }
            $join .= " LEFT OUTER JOIN $db.$table AS $alias";
            $join .= " ON $alias.$id = $idfield";
            $tbs[]=$alias;
         }
         if (is_array($desc['values'])) {
            $idxval = 0;
            foreach ($desc['values'] as $col_name => $desc_def) {
               $join .= $this->create_join_sub_clause(
                        $desc_def['db'],
                        $desc_def['table'],
                        $desc_def['column'],
                        $desc_def['description'],
                        $alias . '.' . $col_name,
                        $alias . '_' . $idxval,
                        $tbs
                  );
               $idxval += 1;
            }
         }
      }
      return $join;
   }


7) and finally change create_join_clause to use the just created function:
Code:

   for ($k = 0,$numfds = sizeof($this->fds); $k<$numfds; $k++) {
      $field = $this->fds[$k];
+         $join .= $this->create_join_sub_clause(
+                  $this->fdd[$field]['values']['db'],
+                  $this->fdd[$field]['values']['table'],
+                  $this->fdd[$field]['values']['column'],
+                  $this->fdd[$field]['values']['description'],
+                  $alias . '.' . $field,
+                  'PMEjoin'.$k,
+                  $tbs
+               );
-      if($this->fdd[$field]['values']['db']) {
-         $db = $this->fdd[$field]['values']['db'];
-      } else {
-         $db = $this->db;
-      }
-      $table = $this->fdd[$field]['values']['table'];
-      $id    = $this->fdd[$field]['values']['column'];
-      $desc  = $this->fdd[$field]['values']['description'];
-      if ($desc != '' && $id != '') {
-         $alias = 'PMEjoin'.$k;
-         if (!in_array($alias,$tbs)) {
-            $join .= " LEFT OUTER JOIN $db.$table AS $alias";
-            $join .= " ON $alias.$id = PMEtable0.$field";
-            $tbs[]=$alias;
-         }
-      }
   }

As always, the code suppressed was in fact moved in the previous function.

Note that all this was done on phpMyEdit-5.3; I'm in the process to upgrade to the CVS version to see if all works still as well.

If this works, and the developpers agree, I can send them the corresponding patch to the CVS top version.

 
bernie95     Joined: 03 Sep 2003   Posts: 10  
Post Posted: 2003-10-07 09:54
Back to top  Reply with quote     

Hello,

I've ported my multi-table join system to the CVS version and it works fine. Is there any interest in someone reviewing it and/or then committing it to the CVS version or do I have to maintain a separate branched version of phpMyEdit?

I had some requests for my updated phpMyEdit.class.php and it seems to be a recurring request in the forums so I think it could be interesting to integrate the feature in the main release trunk.

Bernard

 
etcetera     Joined: 08 Mar 2004   Posts: 4  
Post Posted: 2004-03-08 15:00
Back to top  Reply with quote     

Good day Bernard,

Being a newbie to PME, I am currently looking at the various features that I can use to make my "in the making" CMS more complete. I have looked at the changes that you have written and it seems very useful for multi-table linking. Could you tell me if your changes have been included in the CVS class file? Otherwise, would it be possible for you to send me your class file?

Great work!

Regards,

Dan (daniel.dansereau@logikinfo.com)

bernie95 wrote:
Hello,

I've ported my multi-table join system to the CVS version and it works fine. Is there any interest in someone reviewing it and/or then committing it to the CVS version or do I have to maintain a separate branched version of phpMyEdit?

I had some requests for my updated phpMyEdit.class.php and it seems to be a recurring request in the forums so I think it could be interesting to integrate the feature in the main release trunk.

Bernard


 
hbernard     Joined: 23 Mar 2003   Posts: 159   Location: FRANCE
Post Posted: 2004-03-09 17:47
Back to top  Reply with quote     

bernie95 wrote:
I've ported my multi-table join system to the CVS version and it works fine. Is there any interest in someone reviewing it and/or then committing it to the CVS version or do I have to maintain a separate branched version of phpMyEdit?


We all are interested to have a patch. About when/if it will be committed is another thing.

1. open a bug report, describing _what_ you want to achieve
2. upload patch, comment _how_ you solved this issue
3. IMHO, having to rewrite all mysql syntax with php arrays is a bad thing. Couldn't we just being able to display values from 'querys' in place of 'table' ?

However, nice patch !

--Hugues (hbernard)

_________________
hbernard - phpMyEdit updater.
 
pakman     Joined: 09 Jul 2004   Posts: 5  
Post Posted: 2004-07-14 16:38
Back to top  Reply with quote     

Really, we need that patch, the changes that i saw are from the 5.3 version and i have to do it with the 5.4 version (tabs). Maybe you can show me how to obtain it!

Thanks in advance!

 
cburns     Joined: 20 Sep 2004   Posts: 8  
Post Posted: 2004-09-21 21:52
Back to top  Reply with quote     

I am fairly confident that I have 'patched' 5.4 to include the code that Bernie95 wrote.

Here is a copy of the class file:

http://www.ccs.neu.edu/home/cburns/scripts/phpMyEdit.class.php

Suppressed code is commented out. I have added some lines and some extra functions as he suggested in his directions. There were also some bugs that I think I worked out.

Here is an example of how I used the code to obtain the display and add/edit functionality I was going for in this thread ( http://opensource.platon.sk/forum/projects/viewtopic.php?t=494 )

Code:

$opts['fdd']['section_id'] = array(
  'name'     => 'Course: Instructor',
  'required' => true,
  'sort'     => true,
  'values'    => array(
    'table'       => 'sections',
    'column'      => 'id',
    'description' => array(
      'columns'     => array('course', 'section_name', 'instructor'),
      'divs'        => array(': ', ': '),
      'values'      => array(
        'course'       => array (
          'table'           => 'courses',
          'column'          => 'id',
          'description'     => array('columns' => array('name'))),     
        'section_name' => array(),   
        'instructor'   => array (
          'table'           => 'instructors',
          'column'          => 'id',
          'description'     => array('columns' => array('instructor_name')))
))));


I hope this helps someone.

 
blossom     Joined: 06 Oct 2004   Posts: 1  
Post Posted: 2004-10-06 02:27
Back to top  Reply with quote     

cburns wrote:
I am fairly confident that I have 'patched' 5.4 to include the code that Bernie95 wrote.

...

I hope this helps someone.


Oh, yes. It helps. I now use it for almost all of the database interfaces on my site.

Thanks for your work.

I found a "division by zero" warning in a few lines of code, though. Here is what I did to get rid of them:

Code:
In function display_list_table_buttons():

- $current_page = intval($this->fm / $this->inc) + 1;
- $total_pages  = max(1, ceil($total_recs / abs($this->inc)));
+ $current_page = ($this->inc==0) or (intval($this->fm / $this->inc) + 1);
+ $total_pages  = ($this->inc==0) or (max(1, ceil($total_recs / abs($this->inc))));



Blossom Merz

 
cburns     Joined: 20 Sep 2004   Posts: 8  
Post Posted: 2004-10-06 13:38
Back to top  Reply with quote     

Glad to hear it's helped =)

 
pccolella     Joined: 28 Oct 2004   Posts: 1  
Post Posted: 2004-10-28 23:10
Back to top  Reply with quote     

I am very interested in getting a copy of this class, however this link does not work. Can anyone help?

Thank you.

cburns wrote:

Here is a copy of the class file:

http://www.ccs.neu.edu/home/cburns/scripts/phpMyEdit.class.php



 
cburns     Joined: 20 Sep 2004   Posts: 8  
Post Posted: 2004-10-29 13:30
Back to top  Reply with quote     

The file should be available now. I had moved stuff around in my web directory, forgetting that people would be accessing that script. Sorry for the inconvenience. Enjoy!

 
pilavdzic     Joined: 26 Oct 2004   Posts: 9  
Post Posted: 2004-10-29 20:36
Back to top  Reply with quote     

Hi. I think it is trying to run the .php file rather than letting my d/l it...

Do you have an FTP site? Or could you zip it or something. I can't get it.

Thanks,
Enis

 
cburns     Joined: 20 Sep 2004   Posts: 8  
Post Posted: 2004-11-01 14:22
Back to top  Reply with quote     

http://www.ccs.neu.edu/home/cburns/scripts/phpMyEdit.class.php.txt

that should work for you . sorry for the trouble!

 
jestapher     Joined: 09 Jun 2003   Posts: 5  
Post Posted: 2005-03-10 04:15
Back to top  Reply with quote     

I updated my original code for use with phpMyEdit 5.4. For my purposes, it is much simpler than bernie95's solution. The patch live's here:

http://inwa.net/~ben/phpmyedit.multijoin.patch

 
Post new topic   Reply to topic   Goto page 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