DataTables Editor - extended form fields example

femolah

Hi Allan or anyone out there..
quite newby to datatables and editor, can I use extended form fields example with table from DOM?
when I tried it, it is appending the array indexes of the columns in the table to my form and my form textfields are not in array indexes of the indexes coming from the dom table, how can I do it insuch a way that the indexes coming from the dom table gets appended to the correct text field on the edit form.

Many thanks.

allan

Hi,

I'm not quite sure what you mean by extended form fields? Could you link me to that example and also to the page that you are currently working on so I can see what the error is please.

Allan

femolah

thanks Allan for the quick response. in your example, this one: http://editor.datatables.net/release/DataTables/extras/Editor/examples/formOnlyData.html

I'm sorry I'm working on localhost, can't send you a link. Cheers.

allan

Can you show me how you'd initialised Editor? You probably need to use the `name` option to get the data from the data source array if you haven't done that already - see: http://editor.datatables.net/release/DataTables/extras/Editor/examples/htmlTable.php

Allan

femolah

I've dont that.. I'm using the name option [0,1,2,3...] here is initialisation of editor

var editor; // use a global for the submit and return data rendering in the examples
//$.fn.dataTableExt.sErrMode = 'throw';
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "php/job.php",
//"display": "envelope",
"domTable": "#example",
"fields": [ {
"label": "Name:",
"name": 0
}, {
"label": "Quote NO:",
"name": 1
}, {
"label": "Price / Hr (£):",
"name": 2
}, {
"label": "Item Description 1:",
"name": 3,
"type": "textarea"
}, {
"label": "Item Description 2:",
"name": 4,
"type": "textarea"
}, {
"label": "Item Description 3:",
"name": 5,
"type": "textarea"
}, {
"label": "Item Description 4:",
"name": 6,
"type": "textarea"
}, {
"label": "Item Description 5:",
"name": 7,
"type": "textarea"
},{
"label": "Site:",
"name": 8
},{
"label": "Quantity 1:",
"name": 9
},{
"label": "Price 1 (£):",
"name": 10
},{
"label": "Quantity 2:",
"name": 11
},{
"label": "Price 2 (£):",
"name": 12
},{
"label": "Quantity 3:",
"name": 13
},{
"label": "Price 3 (£):",
"name": 14
} ,{
"label": "Quantity 4:",
"name": 15
} ,{
"label": "Price 4 (£):",
"name": 16
} ,{
"label": "Quantity 5:",
"name": 17
} ,{
"label": "Price 5 (£):",
"name": 18
} ,{
"label": "Project Manager:",
"name": 19
} ,{
"label": "Customer Contact (Name):",
"name": 20
} ,{
"label": "Date/time of Update:",
"name": 21,
"type": "date",
"dateFormat": $.datepicker.ISO_8601
//"dateFormat": 'D, d M Y'
} ,{
"label": "Employee Name Editing:",
"name": 22,
"type": "hidden"
}
]
} );

$('#example').dataTable( {
"sDom": '<"top"Tlirpf<"clear">>rt<"bottom"Tlirpf<"clear">>',
"sPaginationType": "full_numbers",
"aaSorting": [[ 1, "desc" ]],
"iDisplayLength": 20,
"oTableTools": {
"sRowSelect": "multi",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
}
} );
} );







Customer
Quote No
Title
Site
Tot. Qty
Tot. Price
Proj Manag
Last Update
Update By



<?php do { ?>

<?php echo $row_quotations_sql['custName']?>
<?php echo $row_quotations_sql['quNo']?>
<?php echo $row_quotations_sql['quItemdescrip1']?>
<?php echo $row_quotations_sql['custSite']?>
<?php echo sprintf('%03d', $row_quotations_sql['totalQty']);?>
£<?php echo number_format(round($row_quotations_sql['quOrderValue'], 2),2);?>
<?php echo $row_quotations_sql['projectManager']?>
<?php echo $row_quotations_sql['jobDATE']?>
<?php echo $row_quotations_sql['employeeName']?>

<?php } while ($row_quotations_sql = mysql_fetch_assoc($quotations_sql)); ?>



Customer
Quote No
Title
Site
Tot. Qty
Tot. Price
Proj Manag
Last Update
Update By



copy of php/job.php:
<?php

/*
* Example PHP implementation used for the index.html example
*/

// DataTables PHP library
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'job' )
->fields(
Field::inst( 'custName', 0)->validator( 'Validate::required' ),
Field::inst( 'quNo', 1)->validator( 'Validate::required' ),
Field::inst( 'quPriceHr', 2 ),
Field::inst( 'quItemdescrip1', 3)->validator( 'Validate::required' ),
Field::inst( 'quItemdescrip2', 4 ),
Field::inst( 'quItemdescrip3', 5),
Field::inst( 'quItemdescrip4', 6 ),
Field::inst( 'quItemdescrip5', 7),
Field::inst( 'custSite', 8),
Field::inst( 'quQty1', 9)->validator( 'Validate::required' ),
Field::inst( 'quPrice1', 10)->validator( 'Validate::required' ),
Field::inst( 'quQty2', 11),
Field::inst( 'quPrice2', 12 ),
Field::inst( 'quQty3', 13),
Field::inst( 'quPrice3', 14 ),
Field::inst( 'quQty4', 15 ),
Field::inst( 'quPrice4', 16 ),
Field::inst( 'quQty5', 17 ),
Field::inst( 'quPrice5', 18 ),
Field::inst( 'projectManager', 19 ),
Field::inst( 'custCont', 20),
Field::inst( 'jobDate', 21)
->validator( 'Validate::dateFormat_required', array(
"format" => Format::DATE_ISO_8601,
"message" => "Please enter a date in the format yyyy-mm-dd"
) )
->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
// ->validator( 'Validate::dateFormat', 'D, d M y' )
// ->getFormatter( 'Format::date_sql_to_format', 'D, d M y' )
// ->setFormatter( 'Format::date_format_to_sql', 'D, d M y' ),
Field::inst( 'employeeName', 22 )
)
->process( $_POST )
->json();
thanks.

allan

Let's take the `Site` column as an example:

[code]
"label": "Site:",
"name": 8
[/code]

But in the HTML table it is column 4 (i.e. array index 3). So there is a discrepancy between the two, which if I understand correctly, is what is causing the problem for you.

So the key thing here is to realise that DataTables, when using a DOM source for the table, reads the rows into arrays, with the array index for each column defined by the column position. Therefore, you need to have the two 'line up'.

I would say that using a DOM source for your Editor like with, doesn't provide enough information to Editor. For example if you try to edit an existing row, the information that is not in the table would need to be obtained through another method (Ajax?). Perhaps it would be simplest to have all 22 columns in the HTML table and use the bVisible option to hide the ones you don't want to be visible.

Allan

femolah

right.. I get it. Many thanks Allan. it's easier for me to use dom source because I'm comfortable with writing the sql to provide the dom table and how I want the table data based on where clauses and all that.
as I am a newby with editor and datatables, it's going to take me a while to learn to write sqls through the editor so I could easily use the sAjaxsource. Please could you provide me useful links to examples of where clauses, !=, sum and all that.. with the editor. Thanks.

allan

Not quite sure what you mean by "where clauses, !=, sum and all that.. with the editor"? Do you mean what data is shown in the table? In which case, that's how you populate the table, and nothing to do with Editor (which just uses the data in the table). So if you are creating a DOM table from an SQL statement, you'd add your conditions to whatever your SQL statement is.

If you are using the Editor PHP libraries, then there is the `where` method: http://editor.datatables.net/docs/current/php/class-DataTables.Editor.html#_where

Allan

femolah

that's exactly what I meant Allan, it will take me time to learn how to work with the editor to write complex sqls.

I'm in a dilema at the moment, I'm using the dom source table, I've been able to fetch all the indexes into the edit modal div also using bVisible like you suggested.

now it is saying; An error has occurred - Please contact the system administrator every time I click update, new and when I use the delete button, it deletes but when I refresh the page, the row I just deleted comes back. Please any ideas. here is my editor initialisaiton:

var editor; // use a global for the submit and return data rendering in the examples
//$.fn.dataTableExt.sErrMode = 'throw';
$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "php/job.php",
//"display": "envelope",
"domTable": "#example",
"fields": [ {
"label": "Name:",
"name": 0
}, {
"label": "Quote NO:",
"name": 1
}, {
"label": "Price / Hr (£):",
"name": 2
}
]
} );
$('#example').dataTable( {
"sDom": '<"top"Tlirpf<"clear">>rt<"bottom"Tlirpf<"clear">>',
"sPaginationType": "full_numbers",
"aaSorting": [[ 1, "desc" ]],
"iDisplayLength": 20,
"oTableTools": {
"sRowSelect": "multi",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
}
} );
} );



table:



Customer
Quote No
Price/Hr



<?php do { ?>

<?php echo $row_quotations_sql['custName']?>
<?php echo $row_quotations_sql['quNo']?>
<?php echo $row_quotations_sql['quPriceHr']?>

<?php } while ($row_quotations_sql = mysql_fetch_assoc($quotations_sql)); ?>



Customer
Quote No
Price/Hr




php/job.php:
<?php

/*
* Example PHP implementation used for the index.html example
*/

// DataTables PHP library
include( "lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'job')
->fields(
Field::inst( 'custName', 0)->validator( 'Validate::required' ),
Field::inst( 'quNo', 1)->validator( 'Validate::required' ),
Field::inst( 'quPriceHr', 2 )

)
->process( $_POST )
->json();
thanks.

femolah

the insert works. but the delete and update are not working as I described. thanks Allan or anyone out there.

femolah

I think I know what is happening, the editor is fetching the indexes alright, but maybe it is not knowing the id of that row data from the dom source for it to delete or to update. somebody help.. thanks.

allan

> An error has occurred - Please contact the system administrator every time I click update, new and when I use the delete button

That means there is a JSON error - what does the return from the server look like? Odds are there is an error message in there saying what the problem is.

Allan

allan

If you can link us to a test page, that will help use to resolve your issue a lot quicker.

Allan

femolah

thanks Allan, there is no error in what is returned from the server. it displays the records of data ok.

need to ask Allan, how do I create a test page as I am on localhost.

Many thanks.

allan

If you are getting the "An error has occurred - Please contact the system administrator" message - then there is an error being returned by the server. 99% of this time this is invalid JSON - so we need to see what is being returned to be able to figure out what is going on.

> how do I create a test page as I am on localhost.

You could put the code into http://live.datatables.net . It won't work of course, but I would at least be able to see the code. However, that won't help with the JSON error - I'd need access to a page that shows the error to be able to help in that regard. If you run the DataTables debugger on your page as soon as you get the "An error has occurred" message, that might give me a clue.

Allan