******EDITED / REWRITTEN*****<br>
OK I have not received any responses, and cannot move on with this project without some help. <br><br>I AM SURE THIS WOULD NOT TAKE MORE THAN 2 MINUTES OF THOUGHT from someone who knows what they are doing, so in an attempt to get some help I have completely rewritten my post in the hope that it will be much clearer, and more likely to receive help.<br><br> I have searched extensively on the forums and CANNOT find this topic. The only other post that I think might help mentioned the ‘aliasparenttable’ method which I do not know how to implement.<br><br> I have two tables set out as follows:
Table1, name= ‘Master’<br>
Id<br>
first<br>
last<br>
group1<br>
group1status<br>
group2<br>
group2status<br><br>
Example of Master:
Table2, name= ‘Groups’<br>
Id<br>
group_names<br><br>
Example of Groups:
id
Group Name
1
Garden Tidy Client
2
Community Lunch Client
3
Gateway Trades Client
4
Luncheon Club Volunteer
In table ‘Master’, group1 and group2 columns should contain one of the names from the list in table:‘Groups’:column:’group_names’. In the editor form there should be one ‘select’ for group1 and another ‘select’ for group2, however both of these selects would obviously have an identical list drawn from ‘Groups’:column:’group_names’. THIS is where I run into a problem, as I have duplicate references and joins.<br>
Got this working fine without the group2 column in ‘master’, with the select nicely populating from the ‘groups’ table, and any edits updating, however with more than one group options in my ‘master’ table I have problems. I am hoping to eventually get this working with 5 group options in ‘master’ table.<br><br>
*************************PHP AS FOLLOWS:****************************<PRE>
$out = Editor::inst( $db, 'master' )
->fields(
Field::inst( 'master.title' ),
Field::inst( 'master.first' ),
Field::inst( 'master.last' ),
Field::inst( 'master.group1' ),
Field::inst( 'groups.group_names' ),
Field::inst( 'master.group1status' ),
Field::inst( 'master.group2' ),
Field::inst( 'groups.group_names' ),
Field::inst( 'master.group2status' )
)
->leftJoin( 'groups', 'groups.id', '=', 'master.group1' )
->leftJoin( 'groups', 'groups.id', '=', 'master.group2' )
->process($_POST)
->data();
// When there is no 'action' parameter we are getting data, and in this
// case we want to send extra data back to the client, with the options
// for the 'sites' and 'dept' select lists
if ( !isset($_POST['action']) ) {
// Get a list of sites for the select list
$out['groups'] = $db
->selectDistinct( 'groups', 'id as value, group_names as label' )
->fetchAll();
}
// Send it back to the client
echo json_encode( $out );
</PRE>
<br><br>
Thanks very much for your detailed question! I'm sorry I wasn't able to reply yesterday as I was traveling and didn't have web access.
The Editor leftJoin method is based upon the SQL LEFT JOIN and can be used in a very similar way, including the ability to alias a table name using the as keyword. For example, you might have:
(with apologies for the poor linguistics of using groups1 and groups2 - you might want to choose better names!).
There is an example on the Editor site of this technique. For some reason the PHP for the example isn't being shown - sorry about that. Another teething problem for the new site. The file contains:
$out = Editor::inst( $db, 'users' )
->field(
Field::inst( 'users.first_name' ),
Field::inst( 'users.last_name' ),
Field::inst( 'users.manager' ),
Field::inst( 'manager.first_name' ),
Field::inst( 'manager.last_name' )
)
->leftJoin( 'users as manager', 'users.manager', '=', 'manager.id' )
->process($_POST)
->data();
// When there is no 'action' parameter we are getting data, and in this
// case we want to send extra data back to the client, with the options
// for the 'department' select list and 'access' radio boxes
if ( !isset($_POST['action']) ) {
$userList = $db->select( 'users', 'id, first_name, last_name' );
$out['userList'] = array();
while ( $row = $userList->fetch() ) {
$out['userList'][] = array(
"value" => $row['id'],
"label" => $row['id'].' '.$row['first_name'].' '.$row['last_name']
);
}
}
// Send it back to the client
echo json_encode( $out );
It and the supporting JS / HTML files are included in the trial download if you want to take a look at them.
Great thanks Alan that worked brilliantly. It now displays the table and the editor form correctly.<br><br>
My only issue now is that I can't make any updates without an error being thrown.<br><br>
I'm pretty sure it is to do with either of the following bits of code not being quite right:<br>
if ( !isset($_POST['action']) ) {
// Get a list of sites for the `select` list
$out['groups'] = $db
->selectDistinct( 'groups', 'id as value, group_names as label' )
->fetchAll();
}
// Send it back to the client
echo json_encode( $out );
************************OR possibly this in my JS?:*************<br>
initComplete: function ( settings, json ) {
// Populate the site select list with the data available in the
// database on load
editor.field( 'master.group1' ).update( json.groups );
editor.field( 'master.group2' ).update( json.groups );
}
When you say you can't make any updates without an error, what is the error you are getting? Is it coming from the editor().field().update() function you have there? Is it a Javascript error, or PHP?
I've just gone into my google developer tools and looked at the XHR Response when I click update and it says the following:
<br />
<b>Fatal error</b>: Call to a member function val() on a non-object in <b>C:\xampp\htdocs\MyDocs\datatablesmod\extensions\Editor-1.3.0\php\Editor\Editor.php</b> on line <b>1014</b><br />
Could you show me the Javascript you are using to initialise Editor please? Also, if you have any differences from the PHP above, that would be useful. If you would prefer to e-mail the files to me, please feel free to do so.
Replies
COMPLETELY REWRITTEN IN AN ATTEMPT TO RECEIVE SOME HELP. SEE BELOW:
COMPLETELY REWRITTEN IN AN ATTEMPT TO RECEIVE SOME HELP. SEE BELOW:
Anyone?
******EDITED / REWRITTEN*****<br>
OK I have not received any responses, and cannot move on with this project without some help. <br><br>I AM SURE THIS WOULD NOT TAKE MORE THAN 2 MINUTES OF THOUGHT from someone who knows what they are doing, so in an attempt to get some help I have completely rewritten my post in the hope that it will be much clearer, and more likely to receive help.<br><br> I have searched extensively on the forums and CANNOT find this topic. The only other post that I think might help mentioned the ‘aliasparenttable’ method which I do not know how to implement.<br><br> I have two tables set out as follows:
Table1, name= ‘Master’<br>
Id<br>
first<br>
last<br>
group1<br>
group1status<br>
group2<br>
group2status<br><br>
Example of Master:
Table2, name= ‘Groups’<br>
Id<br>
group_names<br><br>
Example of Groups:
In table ‘Master’, group1 and group2 columns should contain one of the names from the list in table:‘Groups’:column:’group_names’. In the editor form there should be one ‘select’ for group1 and another ‘select’ for group2, however both of these selects would obviously have an identical list drawn from ‘Groups’:column:’group_names’. THIS is where I run into a problem, as I have duplicate references and joins.<br>
Got this working fine without the group2 column in ‘master’, with the select nicely populating from the ‘groups’ table, and any edits updating, however with more than one group options in my ‘master’ table I have problems. I am hoping to eventually get this working with 5 group options in ‘master’ table.<br><br>
*************************PHP AS FOLLOWS:****************************<PRE>
$out = Editor::inst( $db, 'master' )
->fields(
Field::inst( 'master.title' ),
Field::inst( 'master.first' ),
Field::inst( 'master.last' ),
Field::inst( 'master.group1' ),
Field::inst( 'groups.group_names' ),
Field::inst( 'master.group1status' ),
Field::inst( 'master.group2' ),
Field::inst( 'groups.group_names' ),
Field::inst( 'master.group2status' )
)
->leftJoin( 'groups', 'groups.id', '=', 'master.group1' )
->leftJoin( 'groups', 'groups.id', '=', 'master.group2' )
->process($_POST)
->data();
// When there is no 'action' parameter we are getting data, and in this
// case we want to send extra data back to the client, with the options
// for the 'sites' and 'dept' select lists
if ( !isset($_POST['action']) ) {
// Get a list of sites for the
selectlist$out['groups'] = $db
->selectDistinct( 'groups', 'id as value, group_names as label' )
->fetchAll();
}
// Send it back to the client
echo json_encode( $out );
</PRE>
<br><br>
**************************AND RELEVANT JS AS FOLLOWS:*********************
<br><br>
<PRE>
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
ajax: "../php/usergroupstable.php",
table: "#usergroupstable",
fields: [
{
label: "Title", name: "master.title", type: "select",
ipOpts: [
{label: "", value: ""},
{label: "Miss", value: "Miss"},
{label: "Mrs", value: "Mrs"},
{label: "Mr", value: "Mr"},
{label: "Ms", value: "Ms"},
{label: "Dr", value: "Dr"},
{label: "Other", value: "Other"}
]
},
{
label: "First Name", name: "master.first", type: "text"
},
{
label: "Last Name", name: "master.last", type: "text"
},
{
label: "Group 1", name: "master.group1", type: "select"
},
{
label: "Group 1 Status", name: "master.group1status", type: "select",
ipOpts: [
{label: "", value: ""},
{label: "Active", value: "Active"},
{label: "Temp", value: "Temp"},
{label: "Inactive", value: "Inactive"}
]
},
{
label: "Group 2", name: "master.group2", type: "select"
},
{
label: "Group 2 Status", name: "master.group2status", type: "select",
ipOpts: [
{label: "", value: ""},
{label: "Active", value: "Active"},
{label: "Temp", value: "Temp"},
{label: "Inactive", value: "Inactive"}
]
}
]
} );
$('#usergroupstable').dataTable( {
dom: "Tfrtip",
ajax: {
url: "../php/usergroupstable.php",
type: 'POST'
},
//Custom settings
sScrollY: 500,
sScrollX: "300px",
bScrollCollapse: true,
bJQueryUI: true,
bPaginate: false,
columns: [
{data: "master.title"},
{data: "master.first"},
{data: "master.last"},
{data: "groups.group_names"},
{data: "master.group1status"},
{data: "groups.group_names"},
{data: "master.group2status"}
],
tableTools: {
sRowSelect: "os",
aButtons: [
{ sExtends: "editor_edit", editor: editor }
]
},
initComplete: function ( settings, json ) {
// Populate the site select list with the data available in the
// database on load
editor.field( 'master.group1' ).update( json.groups );
editor.field( 'master.group2' ).update( json.groups );
}
</PRE>
Hi Ben,
Thanks very much for your detailed question! I'm sorry I wasn't able to reply yesterday as I was traveling and didn't have web access.
The Editor leftJoin method is based upon the SQL
LEFT JOINand can be used in a very similar way, including the ability to alias a table name using theaskeyword. For example, you might have:(with apologies for the poor linguistics of using
groups1andgroups2- you might want to choose better names!).There is an example on the Editor site of this technique. For some reason the PHP for the example isn't being shown - sorry about that. Another teething problem for the new site. The file contains:
It and the supporting JS / HTML files are included in the trial download if you want to take a look at them.
Regards,
Allan
Great thanks Alan that worked brilliantly. It now displays the table and the editor form correctly.<br><br>
My only issue now is that I can't make any updates without an error being thrown.<br><br>
I'm pretty sure it is to do with either of the following bits of code not being quite right:<br>
if ( !isset($_POST['action']) ) { // Get a list of sites for the `select` list $out['groups'] = $db ->selectDistinct( 'groups', 'id as value, group_names as label' ) ->fetchAll(); } // Send it back to the client echo json_encode( $out );************************OR possibly this in my JS?:*************<br>
initComplete: function ( settings, json ) { // Populate the site select list with the data available in the // database on load editor.field( 'master.group1' ).update( json.groups ); editor.field( 'master.group2' ).update( json.groups ); }When you say you can't make any updates without an error, what is the error you are getting? Is it coming from the
editor().field().update()function you have there? Is it a Javascript error, or PHP?Allan
in the editor modal form when I click update it just says 'An error has occurred - Please contact the system administrator' in the actual modal
I've just gone into my google developer tools and looked at the XHR Response when I click update and it says the following:
<br />
<b>Fatal error</b>: Call to a member function val() on a non-object in <b>C:\xampp\htdocs\MyDocs\datatablesmod\extensions\Editor-1.3.0\php\Editor\Editor.php</b> on line <b>1014</b><br />
and this was in the headers which looks OK to me
action:edit<br>
data[master][title]:Miss<br>
data[master][first]:Jessica<br>
data[master][last]:Johnston<br>
data[master][group1]:1<br>
data[master][group1status]:<br>
data[master][group2]:3<br>
data[master][group2status]:<br>
id:row_1066<br>
Any Ideas?
Could you show me the Javascript you are using to initialise Editor please? Also, if you have any differences from the PHP above, that would be useful. If you would prefer to e-mail the files to me, please feel free to do so.
Allan