When using EDITOR with a "where" clause it is also applied at EDIT time

johnhpe

The documentation states that the WHERE clause is only read at fetch....

It is important to note that the conditions applied by Editor->where() are used only on data fetch.

This works fine for example SELECT ........ WHERE id=1 but when I edit a row it updates all rows with an id of 1. I have verified this by debugging the MySQL command i.e. UPDATE ..... WHERE id=1.

I only want the highlighted rows to be updated. Is this normal behaviour or have I read this totally wrong... here is my server side code....
```
<?php
session_start();

include( "../JS/Editor-1.6.5/php/DataTables.php" );

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

$editor = Editor::inst( $db, 'admins' )
->field(
Field::inst( 'admins.key' ),
Field::inst( 'admins.user' ),
Field::inst( 'admins.priv_level' ),
Field::inst( 'admins.password' )
->getFormatter( function ( $val, $data, $opts ) { return '';}),
Field::inst( 'admins.email' ),
Field::inst( 'admins.copy_email' )
)
->where(function ($q) use($_SESSION) {
$q->where('admins.id',$_SESSION['id']);
})
->on('preEdit', function ($e, $id, $values) {
if ( $values['admins']['password'] === '' ) {
$e->field( 'admins.password' )->set( false );
}
else { $e->field( 'admins.password' )->setValue(sha1($values['admins']['password']));}
})
->process($_POST)
->json();

<?php > ``` ?>

allan

The where condition absolutely should not be applied when writing to the table. It will be applied when the written data is then read back, but not during the INSERT or UPDATE.

Can you add ->debug( true ) immediately before the ->process($_POST) line and then show me the JSON that the server returns after an edit please?

Thanks,
Allan

allan

Oh - one other thing - what is the primary key on your table - is it key or id?

Allan

johnhpe

Hi Allan,

Firstly I have to congratulate you on some absolutely brilliant code and the documentation is equally as good.. awesome....

I have been hunting around for an hour again and tried various options only to come to the same conclusion as you ... the PRIMARY KEY.... if only I had seen your reply first.

This was indeed the issue but probably compounded by the DB Table structure. In it the primary key is called "key" but there is another indexed column named "id" and it is the "id" column that was included in the WHERE clause. Hence when I do an EDIT the MySQL was updating based on a where clause of the column "id",. perhaps thinking it is the primary key, which was updating all my records matching "id".

Simple fix was to add the pkey() option to the constructor..... now works a treat.

Just in case anyone see this here is the finished working article, also with a set for the id field on create based on user session variable......

Thanks once again....

$editor = Editor::inst( $db, 'admins1' )
    ->field(
        Field::inst( 'admins1.key' ),
        Field::inst( 'admins1.user' ),
        Field::inst( 'admins1.priv_level' ),
        Field::inst( 'admins1.password' )
            ->getFormatter( function ( $val, $data, $opts ) { return '';}),
        Field::inst( 'admins1.email' )->validator('Validate::email'),
        Field::inst( 'admins1.copy_email' ),
        Field::inst( 'admins1.id' )->set( Field::SET_CREATE )->setValue($_SESSION['id'])
    )
    ->pkey('admins1.key')
    ->where(function ($q) use($_SESSION) {
        $q->where('admins1.id',$_SESSION['id']);
    })
    ->on('preEdit', function ($e, $id, $values) {
        if ( $values['admins1']['password'] === '' ) {
            $e->field( 'admins1.password' )->set( false );
        }
        else { $e->field( 'admins1.password' )->setValue(sha1($values['admins1']['password']));}
    })
    ->process($_POST)
    ->json();

allan

Nice one - great to hear you have it fixed. You can also optionally give the primary key column name as a third parameter to the Editor factory constructor (it just calls ->pkey() internally).

And thank you for your kind words!

Regards,
Allan