jestapher
Joined: 09 Jun 2003
Posts: 5
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
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
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
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
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
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
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
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
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
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
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
Joined: 20 Sep 2004
Posts: 8
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
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
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
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
|
|