How do i edit joined tables in my table?
How do i edit joined tables in my table?
FroopleDesign
Posts: 21Questions: 3Answers: 0
First, My MYSQL: database table is as follows: **
**Events:
eventId , eventName, eventDescription , eventStartTime, eventEndTime, eventCategory,
eventmage, eventStatus,eventToken.
EventLocations:
locationId, eventId, locationName, locationMapUrl
EventResponses:
responseId, username, eventToken, eventId, selectedEvent, userPhone, responseCreatedOn
I created a joint table and im able to edit all fields, but i'd like to be able to edit my locations from the event table. Currently** i can edit all fields directly associated with Events, but not the corresponding locations.** My JSON is outputting the correct data and i am able to see the location data in my editor, i simply cannot update them nor create new locations with an event.
I used the editor datatables generated to start.
javascript
"
`(function($){
$(document).ready(function() {
var editor = new DataTable.Editor({
ajax: 'php/table.Events.php',
table: '#Events',
fields: [
{
"label": "Name:",
"name": "eventName"
},
{
"label": "Description:",
"name": "eventDescription"
},
{
"label": "Start Time:",
"name": "eventStartTime",
"type": "datetime",
"format": "YYYY-MM-DD HH:mm:ss"
},
{
"label": "End Time:",
"name": "eventEndTime",
"type": "datetime",
"format": "YYYY-MM-DD HH:mm:ss"
},
{
"label": "Category:",
"name": "eventCategory",
"type": "select",
"options": [
"Food",
"Art",
"Culture"
]
},
{
"label": "Image:",
"name": "eventImage",
"type": "select",
"options": [""]
},
{
"label": "Status:",
"name": "eventStatus"
},
{
"label": "Location 1 Name:",
"name": "EventLocations.0.locationName"
},
{
"label": "Location 1 Map URL:",
"name": "EventLocations.0.locationMapUrl"
},
{
"label": "Location 2 Name:",
"name": "EventLocations.1.locationName"
},
{
"label": "Location 2 Map URL:",
"name": "EventLocations.1.locationMapUrl"
}
]
});
var table = new DataTable('#Events', {
dom: 'Bfrtip',
ajax: 'php/table.Events.php',
columns: [
{
"className": 'details-control',
"orderable": false,
"data": null,
"defaultContent": '<i class="fas fa-plus"></i>'
},
{
"data": "eventName"
},
{
"data": "eventStartTime"
},
{
"data": "eventEndTime"
},
{
"data": "eventCategory"
},
{
"data": "eventStatus"
},
{
"data": "eventToken",
"render": function (data, type, row) {
return '<a href="https://survey.website.com/event/eventPage.php?token=' + data + '"><i class="fas fa-link"></i></a>';
}
}
],
select: true,
lengthChange: false,
buttons: [
{ extend: 'create', editor: editor },
{ extend: 'edit', editor: editor },
{ extend: 'remove', editor: editor }
],
initComplete: function () {
// Initialize child rows (responses data) when the DataTable is ready
var api = this.api();
api.on('click', '.details-control', function () {
var tr = $(this).closest('tr');
var row = api.row(tr);
if (row.child.isShown()) {
// This row is already open - close it
row.child.hide();
tr.removeClass('shown');
$(this).html('<i class="fas fa-plus"></i>');
} else {
// Open this row with response data
var rowData = row.data();
var responses = rowData.EventResponses;
if (responses.length === 0) {
// No responses, display the message
row.child('<p>There are no responses to this event.</p>').show();
} else {
// Generate the table for responses
var tableHtml = '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">';
tableHtml += '<tr>';
tableHtml += '<td><strong>Username</strong></td>';
tableHtml += '<td><strong>Phone</strong></td>';
tableHtml += '<td><strong>Selected Event</strong></td>';
tableHtml += '</tr>';
for (var i = 0; i < responses.length; i++) {
tableHtml += '<tr>';
tableHtml += '<td>' + responses[i].username + '</td>';
tableHtml += '<td>' + responses[i].userPhone + '</td>';
tableHtml += '<td>' + responses[i].selectedEvent + '</td>';
tableHtml += '</tr>';
}
tableHtml += '</table>';
row.child(tableHtml).show();
}
tr.addClass('shown');
$(this).html('<i class="fas fa-minus"></i>');
}
});
}
});
});
})(jQuery);
`
** php**
`<?php
/*
* Editor server script for DB table Events
* Created by http://editor.datatables.net/generator
*/
// DataTables PHP library and database connection
include( "lib/DataTables.php" );
// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
/*
$db->sql( "CREATE TABLE IF NOT EXISTS `Events` (
`eventId` int(10) NOT NULL auto_increment,
`eventName` varchar(255),
`eventDescription` varchar(255),
`eventStartTime` datetime,
`eventEndTime` datetime,
`eventCategory` varchar(255),
`eventImage` varchar(255),
`eventStatus` varchar(255),
`eventToken` varchar(255),
PRIMARY KEY( `eventId` )
);" );
*/
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'Events', 'eventId' )
->fields(
Field::inst( 'eventName' )
->validator( Validate::notEmpty() )
->validator( Validate::minMaxLen( 3, 80 ) ),
Field::inst( 'eventDescription' ),
Field::inst( 'eventStartTime' )
->validator( Validate::dateFormat( 'Y-m-d H:i:s' ) )
->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),
Field::inst( 'eventEndTime' )
->validator( Validate::dateFormat( 'Y-m-d H:i:s' ) )
->getFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) )
->setFormatter( Format::datetime( 'Y-m-d H:i:s', 'Y-m-d H:i:s' ) ),
Field::inst( 'eventCategory' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 49 ) ),
Field::inst( 'eventImage' ),
Field::inst( 'eventStatus' ),
Field::inst( 'eventToken' )
->set( false )
->validator( Validate::url() )
)
->join(
Mjoin::inst( 'EventLocations' )
->link( 'Events.eventId', 'EventLocations.eventId' )
->fields(
Field::inst( 'locationName' )
->validator( Validate::notEmpty() )
->validator( Validate::minMaxLen( 3, 80 ) ),
Field::inst( 'locationMapUrl' )
->set( false )
->validator( Validate::url() )
)
)
->join(
Mjoin::inst( 'EventResponses' )
->link( 'Events.eventId', 'EventResponses.eventId' )
->fields(
Field::inst( 'username' ),
Field::inst( 'userPhone' ),
Field::inst( 'selectedEvent' )
->validator( Validate::notEmpty() )
->validator( Validate::minMaxLen( 3, 80 ) )
)
)
->process($_POST)
->json();
`
"
**:
This question has an accepted answers - jump to answer
Answers
Is nested editing an option for you? Have a Location 1 and Location 2 field which are DataTables that let you edit that information.
Or you could simply have a Locations field which lets the user select multiple locations.
Allan
Thank you @allan
I will give this a try ,
I'd have to attach some filter to ensure that the user can only create or edit EventLocations with an eventId matching the Events but it seems possible
I managed to make nested editing works. My issue right now is that i can't seem to make the nested eventLocation editor only show data related to the main editor ( filter by eventId). I also need it so when i create a new location in the event Editor, it created it with the matching eventId.
I've also attached the JSON data.
Heres my nested code:
Ah - do you mean the options to show in the
datatablewill change depending on which row is selected in the host table? If so, have a look at this example which handles exactly that.Allan
I’ve attempted to recreate something similar to the example given but it seems I'm missing something. It is not working like the example. All locations are visible, and it created locations unrelated to the event itself. I already have a working datatable written in CORE php, but i wish to use editor.datatables as I bought the license.
Events JSON
EventLocations JSON
Looks like the missing piece might be the
wherecondition in thetable.EventLocations.phpfile. You sendeventIdthrough the use ofajax.datafor thedatatablefield type. But there is nothing in that file that uses it.before the
->process(...)call should help.Allan
Thank you, i'll take a look. I also read "https://datatables.net/blog/2016-03-25".
Sincerely appreciate the help.
Edit: Adding " // Check if the "eventId" key exists in the $_POST array before using it
->where(function ($q) {
if (isset($_POST['eventId'])) {
$q->where('eventId', $_POST['eventId']);
}
})" seems to have successfully filtered the data and is allowing me to edit. Now i just have to sort the creation function. Thank you very much,I can't believe i missed this.
Awesome. Glad to hear that helped. There are a fair number of parts that need to come together for it to work successfully!
Allan