One table - One Column - One Primary Key

BROB201

Hi,

I am using the Editor (1.2.3) with the PHP library for users to modify a table (admin_user) which has only one column (username) and that column is the primary key.

On update, I get a "Undefined offset: 0 in ...datatable/php/lib/Editor/Editor.php on line 544". Which is fair, it's a primary key, you wouldn't want to be able to do that usually... Also I want to here :D And the field actually get modified in the DB but the user sees the "an error has occured ..." message.

On delete, everything goes fine.

On create, I get a "offset: 0 in ...datatable/php/lib/Editor/Editor.php on line 507" (The row gets added in the DB and I get the "an error has occured ..." as well here). And it's my understanding that this should not happen and I have the feeling I am doing something wrong: I just can't figure out what, so here is my code!!


The JS and HTML:

[code]
var editor;

$(document).ready(function() {
editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "assets/datatable/php/ajax_adminusers.php",
"domTable": "#example",
"fields": [ {
"label": "User Name (NT ID):",
"name": "username"
}
]
} );

// New record
$('#btn_editor_create').click(function (e) {
e.preventDefault();
editor.create(
'Create new record',
{ "label": "Add", "fn": function () { editor.submit() } }
);
} );

// Edit record
$('#example').on('click', 'a.editor_edit', function (e) {
e.preventDefault();
editor.edit(
$(this).parents('tr')[0],
'Edit record',
{ "label": "Update", "fn": function () { editor.submit() } }
);
} );

// Delete a record (asking a user for confirmation)
$('#example').on('click', 'a.editor_remove', function (e) {
e.preventDefault();
editor.message( "Are you sure you want to remove this row?" );
editor.remove( $(this).parents('tr')[0], 'Delete row', {
"label": "Confirm",
"fn": function () { this.submit(); }
});
});

$('#example').dataTable( {
"sDom": "lfrtip",
"iDisplayLength": 50,
"sAjaxSource": "assets/datatable/php/ajax_adminusers.php",
"aoColumns": [
{ "mData": "username" },
{
"mData": null,
"sClass": "center",
"sDefaultContent": 'Edit / Delete'
}
]
});
} );




Create New Record







Username
Admin




[/code]

And the PHP:


[code]

include( "lib/DataTables.php" );

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

Editor::inst( $db, 'admin_user', 'username' )
->fields(
Field::inst( 'username' )->validator( 'Validate::required' )
)
->process( $_POST )
->json();

[/code]

Any help/pointer/suggestion would be appreciated!

Thanks

allan

Tricky... The problem is that Editor doesn't expect the primary key to change on edit. If it does, then the 'get' that it performs will fail since the primary key it is doing its `where` condition on is no longer there - which is exactly what you are seeing.

The updates I've made to Editor 1.3 will "mask" this error - in that if Editor can't get a row, rather than throwing an error, it will remove it form the table. But I rather suspect that isn't what you want in this case.

I think the Editor code should probably cope with this...

In Editor.php at the bottom of the `_update()` method you will find:

[code]
$this->_out['id'] = $this->_idPrefix . $id;

// Get the data for the row so we can feed it back to the client and redraw
// the whole row with the full data set from the server.
$row = $this->_get( $id );
[/code]

Replace it with:

[code]
// Was the primary key altered as part of the edit?
$getId = isset( $this->formData['data'][ $this->_pkey ] ) ?
$this->formData['data'][ $this->_pkey ] :
$id;

$this->_out['id'] = $this->_idPrefix . $getId;

// Get the data for the row so we can feed it back to the client and redraw
// the whole row with the full data set from the server.
$row = $this->_get( $getId );
[/code]

If you could try that and let me know how you get on that would be great!

Allan

BROB201

Would I need to update editor from my 1.2.3 to 1.3 first or can I do this right away?

BROB201

I made the change in 1.2.3 but the method is called _insert and it errors out the same way.

BROB201

I updated to 1.2.4, made the change (method is still called _insert), same results.

allan

No need for 1.3 for the change above.

The method is `private function _update( $id )` - it is just before the insert function (insert for a new record, and update for an edit). You mentioned that it was on update you are having an issue.

Allan

BROB201

Thanks Alan,

I actually have a problem for both an insert (new record) and update (edit).

I made the change in _update and it works great. Anything I can do in insert for the error message to go away?

allan

The error would only occur on insert if Editor isn't able to read the data back that it has just written. That sounds a little bit odd and I can't think (off the top of my head) what would cause that, other than a where condition that isn't being fulfilled. I think I would need to be able to see your full code to understand what is going.

Allan

BROB201

I sent you a link, please let me know.

allan

Thanks for the link. It looks like this line `$id = $res->insertId();` is failing in the PHP. Which would suggest that the database isn't returning the value needed. Can you show me both your SQL schema for the table and PHP script at ajax_adminusers.php please?

Allan

BROB201

SQL schema:

[code]
CREATE TABLE `admin_user` (
`username` varchar(45) NOT NULL,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$
[/code]

And ajax_adminusers.php:

[code]
include( "lib/DataTables.php" );
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

Editor::inst( $db, 'admin_user', 'username' )
->fields(
Field::inst( 'username' )->validator( 'Validate::required' )
)
->process( $_POST )
->json();
[/code]

allan

Interesting - I would have through that would work. I'll need to spend some time on it and experiment a bit if I can tomorrow, although things a bit busy at the moment, so can't say for sure when.

Allan

allan

Sorry, haven't had a chance to get to this today. Will do so tomorrow.

Allan

BROB201

Understood, thank you.

allan

So with a bit of messing around it appears that the issue is with PHP's lastInsertId function ( http://www.php.net/manual/en/pdo.lastinsertid.php ).

Based on this thread: http://stackoverflow.com/questions/11156735/last-insert-id-is-not-returning , which says:

> lastInsertId() only returns IDs automatically generated in an AUTO_INCREMENT column.

So - the fix is to use the id that you submit.

In the `_insert()` function (Editor.php again) you will find:

> $id = $res->insertId();

Immediately after that, add:

[code]
if ( isset( $this->_formData[ $this->_pkey ] ) ) {
$id = $this->_formData[ $this->_pkey ];
}
[/code]

And that should do it.

I've just tried it here and it seems to work well for create, edit and delete.

These changes will be in 1.3 :-)

Allan

BROB201

It works perfectly, thank you so much Allan!