WHERE clause causing error on update using Editor

Skycpt25

Hi, firstly thanks for all your hard work on DataTables its superb.

I'm new to Json but I think I understand the error I'm just not sure how to get round it.

I have a WHERE that I'm using to select and show data from MySQL, which works fine, however I need to edit the value that the WHERE is using to display the data, when I do this it causes an error, presumably because it can no longer find the data. A page refresh obviously solves the error. But there must be a way that I can update without the error occurring.

My example would be: WHERE the status is A....error occurs when updating A to B using editor/edit.

allan

Hi,

Do you actually want to be setting the data in the joined table, or just on the host? If you don't want to set the data on the joined table use `->set( false )` on the join class: https://editor.datatables.net/docs/current/php/class-DataTables.Editor.Join.html#_set .

I wish I'd made that the default action to be honest as it turns out that is the most common requirement for the join!!

Allan

Skycpt25

Hi Allan

It's not on a join, it's quite simple actually, here's the code

Editor::inst( $db, 'sales__branch_stock_analysis', 'branch_stock_ID' )

->where( 'engineer_code', $_SESSION['MM_Userview'] )
->where( 'status', 'Target' )



->fields(
Field::inst( 'branch_stock_ID' ),
Field::inst( 'cef_group' ),
Field::inst( 'branch_name' ),
Field::inst( 'factory' ),
Field::inst( 'product' ),
Field::inst( 'supplier_1' ),
Field::inst( 'supplier_2' ),
Field::inst( 'supplier_3' ),
Field::inst( 'supplier_other' ),
Field::inst( 'engineer_name' ),
Field::inst( 'engineer_code' ),
Field::inst( 'rsm' ),
Field::inst( 'status_notes' ),
Field::inst( 'status' )
)

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

You can see I'm just running a second WHERE to further select the data. I'm starting to think this needs to be a little more complicated to allow it to work.

Skycpt25

I should point out I'm using a select query in the editor to give me the status select options.

tangerine

I think you need
->where(...)->and_where(...)
for more than one "where".

Skycpt25

Hi tangerine

I thought that as well but using that gives me an invalid Json repsonse, the way I have it works. The problem is when the update is used it's causing an error when it rebuilds the table because the row of data in the table has changed it's status from Target to something else. I'm just wondering if theres a way around it so it doesnt give me an error.

allan

> ->where(...)->and_where(...)

`and_where` isn't a method of the Editor class, it is of the Db class, but not the Editor, which is why this will give you a runtime error.

@Skycpt25

Apologies, you did make the problem clear originally and I didn't quite clock it:

> presumably because it can no longer find the data

Absolutely correct. With the `where` condition set, you will likely be getting an error about the `$row['aaData'][0]` parameter, or not existing I think. This is caused by the `_update` function in the Editor class:

[code]
$row = $this->_get( $id );
return $row['aaData'][0];
[/code]

You could change it to be:

[code]
$row = $this->_get( $id );
return count($row) > 0 ? $row['aaData'][0] : null;
[/code]

which will stop the PHP error. But on the client-side you might need to use onEditComplete or similar to actually remove the row front he table, since Editor doesn't know to remove it (perhaps something I need to add to the protocol).

Regards,
Allan

tangerine

[quote]and_where isn't a method of the Editor class, it is of the Db class[/quote]
Sorry, guys. I should learn to read.