Editor with WHERE-clause

hasenumberone

I have got a table where multiple customers have got records. They should just edit their own records of course. Normally i would make it with an where-clause.

How can i make it with the datatables-editor?

(Yes, i have searched for this before :-) )

allan

If you are using the Editor PHP classes, you can use the `where` method: http://editor.datatables.net/docs/current/php/class-DataTables.Editor.html#_where

If you aren't using the Editor PHP classes, then you'd need to add a WHERE condition to however you are getting the data for the table.

Allan

hasenumberone

Thx. I'll try it.

christopherlawson

I am using editor and have tried the where method, but it doesn't seem to be restricting my results in the datatable. I have tried different fields and still no result. Any suggestion where I might be going wrong?

christopherlawson

I put the where method before the process method and it worked fine.

allan

@christopherlawson - That's it exactly. The other methods are basically 'setup', getting everything ready for the `process` method. A bit like an SQL commit :-).

Regards,
Allan

jwise

I've been attempting to get this working, but I'm not quite clear on what the syntax is when creating a new editor instance. I've got two fields, ListType and ItemID, both of which are primary keys. ItemID is specified in my editor instance as the PK, since it only accepts one PK, but there multiple records in which the ListType can have the same ItemID value - e.g.:

ListType = Phone, ItemID = Home
ListType = Email, ItemID = Home
ListType = Address, ItemID = Home

This causes Editor to attempt to throw an integrity constraint violation on submit. How do I specify the where clause in the editor instance to only submit the update where the ListType matches that of the currently-selected record?

Thanks!
-John

allan

Hi John,

I'm not sure I 100% understand I'm afraid. How does this relate to using a `where` clause in the PHP Editor libraries?

What does your code currently look like?

Thanks,
Allan

jwise

Allan,

Thanks for the quick response. Here's the code I'm using for the Editor instance I'm getting the integrity constraint errors on:

[code]
editorCodeList = new $.fn.dataTable.Editor( {
"ajaxUrl": "table.CodeList.php",
"domTable": "#CodeList",
"pkey": "ItemID",
"fields": [
{
"label": "List Type:",
"name": "ListType"
},
{
"label": "Code:",
"name": "ItemID"
},
{
"label": "Abbreviation:",
"name": "ShortName",
"type": "text"
},
{
"label": "Long Name:",
"name": "LongName",
"type": "text"
},
{
"label": "Description:",
"name": "CodeDesc",
"type": "text"
},
{
"label": "Active:",
"name": "Active",
"type": "checkbox",
"default": "Y",
"ipOpts": [
{
"label": "",
"value": "Y"
}
],
"separator": ","
}
],
});
[/code]

As a specific example, I need to be able to specify that updates should only occur on the specific record, where ListType = "Email" & ItemID = "Home", rather than all of the records containing an ItemID of "Home", of which there are several.

allan

Thanks to the explanation. This isn't actually possible with the Editor PHP libraries, as they assume that the database schema will reflect the data, rather than using an ad-hoc key / value method for storing data. I did look into adding this ability a while back, but it is non-trivial to implement in the libraries as they are currently constructed. It is something that will be looked at again in future, but at the moment you might need to craft a little bit of PHP that provides this ability, interfacing with the Editor client/server protocol: http://editor.datatables.net/server/

Allan

dts1

Hi Allan,
I try to pass a dynamic value from the render page (ex. index.html) to the php page.
From my table.intra_produit_tarif.js :
$(document).ready(function() {

var editor = new $.fn.dataTable.Editor( {
"ajaxUrl": "php/table.intra_produit_tarif.php?idprod=<?php echo '19'; ?>",
"domTable": "#intra_produit_tarif",
"fields": [
{
"label": "N",
"name": "nouveaute",
"default": "0",
"type": "checkbox",
"ipOpts": [
{
"label": "Oui",
"value": "1"
}
],
"separator": "|"
}
]

How to pass the value from .js to in table.intra_produit_tarif.php (i need this for the where method) ?
Thanks.

Francois

dts1

Arf... i need to sleep more ! So simple with fnServerParams....
problem solved !

Francois

jwise

Allan,

That's disappointing to hear. This is a fairly common scenario, so I'm surprised that it's not supported yet. There really needs to be an easier way to specify either multiple PKs, or some sort of conditional on updates.

I've looked through the documentation you referred to, but it's a bit sparse and not very clear as to how one would accomplish this through the onPreSubmit or callback methods. Some examples would be extremely useful for those of us not accustomed to writing JS on a daily basis.

-John

dts1

Allan,
I still have a problem. I Can't create or update my database with the where method because the variable is lost ! It's OK when I load data.
Here is my js file :


/*
* Editor client script for DB table intra_produit_tarif
* Automatically generated by http://editor.datatables.net/generator
*/

(function($){

$('#intra_produit_tarif').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "php/table.intra_produit_tarif.php",
"fnServerParams": function ( aoData ) {
aoData.push( { "name": "idprod", "value": "19" } );
},
"aoColumns": [
...
],
"oTableTools": {
"sRowSelect": "multi",
"aButtons": [
{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
}
} );
} );

}(jQuery));

Here is my php file :

<?php
$idprod = "";
if (isset($_GET['idprod'])) $idprod = $_GET['idprod'];

/*
* Editor server script for DB table intra_produit_tarif
* Automatically generated by http://editor.datatables.net/generator
*/

// 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, 'intra_produit_tarif' )
->fields(
Field::inst( 'idprod' )
->set( false ),
Field::inst( 'nouveaute' ),
Field::inst( 'ref' )
->validator( 'Validate::required' ),
Field::inst( 'codebarre2013' ),
Field::inst( 'contenance' ),
Field::inst( 'idtype' ),
Field::inst( 'prix_ht' ),
Field::inst( 'prix_ttc' ),
Field::inst( 'poids_net' ),
Field::inst( 'idpays' ),
Field::inst( 'idpilulier' ),
Field::inst( 'idetiquette' ),
Field::inst( 'actif' )
)
->where( $key = "idprod", $value = $idprod, $op = '=' )
->process( $_POST )
->json();

Note that in fnServerParams value will be a dynamic value when the problem will be solved.
Thanks !
Dts1

allan

There are two conversations going on here - so this might get a bit confusing.

@jwise - Agreed - this is something that Editor's PHP libraries should be able to do in future. Personally I don't use a key / value tables much in SQL, since using database fields is more performant, if you know what the fields are going to be. That isn't always the case, and this should be added in future.

@dts1 - I don't understand what you mean by the variable being lost. At what point is it lost. It would be very useful if you could link me to a test case, rather than large amounts of unformatted code to parse through.

Allan

dts1

Allan,
ok I will try to explain.
I want to show only specifics data from my database. I have a database with price list of product.
Example :
Ref1 in 30 tablets, Ref1 in 60 tablets, Ref1 in 120 tablets, Ref2 in 30 tablets etc...
I only want that user can edit Ref1 in any quantities (30, 60 and 120 tablets) so I want to use the where method.
In my js file, I write this :
"fnServerParams": function ( aoData ) {
aoData.push( { "name": "idprod", "value": "19" } ); // 19 will be a dynamic value
},
In the php file, I write this :
->where( $key = 'idprod', $value = $idprod, $op = '=' )
->process( $_POST )
->json();

When I load the page, it's ok, I have only products with idprod 19. But when I want to edit or Add, I have an error :
Notice: Undefined offset: 0 in /home/.sites/28/site1/web/intra/datatable/php/lib/Editor/Editor.php on line 544
{"id":"row_23","error":"","fieldErrors":[],"data":[],"row":null}

But the database is well updated ! So why ? If I replace in the php file $idprod with a number, it function !.
I hope that i am clear, sorry for this bad english...

Dts1

allan

I see - its because you aren't sending `idprod ` with the Editor Ajax request - only the DataTables get request. If you want to send it with the Editor requests as well, you can do so with the onPreSubmit event: http://editor.datatables.net/options/#onPreSubmit .

Allan

dts1

I don't understand how to use onPreSubmit, sorry... any idea ?

allan

[code]
editor.on( 'onPreSubmit', function ( e, data ) {
data.idprod = {something};
} );
[/code]

Allan

dts1

thanks Allan !

dts1

Hi Allan,
always have an error with the where method.
When I want to edit or add new record :
Notice: Undefined offset: 0 in datatable/php/lib/Editor/Editor.php on line 544
{"id":"row_776","error":"","fieldErrors":[],"data":[],"row":null}

In my JS I have :
editor.add( [
{
"label": "idprod",
"name": "idprod",
"type": "hidden",
"default" : "<?php echo $idprod; ?>"
}
]
);

editor.on( 'onPreSubmit', function ( e, data ) {
data.idprod = {"value": "<?php echo $idprod; ?>"};
} );

$('#intra_produit_tarif').dataTable( {
"sDom": "Trt",
"sAjaxSource": "php/table.intra_produit_tarif.php",
"fnServerParams": function ( aoData ) {
aoData.push( { "name": "idprod", "value": "<?php echo $idprod; ?>" } );
},
...

In my PHP I have :
$idprod="";
if ( isset($_GET['idprod']) ) $idprod=$_GET['idprod'];

// 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 = Editor::inst( $db, 'intra_produit_tarif' )
->fields(
Field::inst( 'idprod' )
->get( false ),
Field::inst( 'nouveaute' ),
Field::inst( 'ref' )->validator( 'Validate::required' ),
Field::inst( 'codebarre2013' ),
Field::inst( 'contenance' ),
Field::inst( 'idtype' ),
Field::inst( 'prix_ht' ),
Field::inst( 'prix_ttc' ),
Field::inst( 'poids_net' ),
Field::inst( 'idpays' ),
Field::inst( 'idpilulier' ),
Field::inst( 'idetiquette' ),
Field::inst( 'actif' )
)
->join(
Join::inst( 'intra_type', 'object' )
->join( 'idtype', 'id' )
->set( false )
->field(
Field::inst( 'id' ),
Field::inst( 'type_fr' )
)

);

$out = $editor
->where( $key = "idprod", $value = $idprod, $op = "=" )
->process( $_POST )
->data();

if ( !isset($_POST['action']) ) {
// Get typeList
$out['typeList'] = $db
->select( 'intra_type', 'id as value, type_fr as label' )
->fetchAll();
}

echo json_encode( $out );

If I change in the php file :
->where( $key = "idprod", $value = $idprod, $op = "=" )
with
->where( $key = "idprod", $value = "19", $op = "=" )
it function !

Note that I use also the join function.

dts1

allan

> data.idprod = {"value": "<?php echo $idprod; ?>"};

I think you might just want:

[code]
data.idprod = "<?php echo $idprod; ?>";
[/code]

What is the value of `$idprod` in the submitted data - you can check using Firebug or Inspector.

Allan

dts1

Hi Allan,
$idprod as the good value (ex. 19), but i still have the error.
When I add a new record, no error message but idprod in my datatabase is 0
Firebug Post :
action create
data[actif]
data[codebarre2013]
data[contenance]
data[idetiquette] 1
data[idpays] 1
data[idpilulier] 1
data[idtype] 2
data[nouveaute] 1
data[poids_net]
data[prix_ht]
data[prix_ttc]
data[ref] ACI60
id
idprod 19
table

Réponse :
{"id":"row_801","error":"","fieldErrors":[],"data":[],"row":{"DT_RowId":"row_801","idprod":"0","nouveaute":"1","ref":"ACI52","codebarre2013":"0","contenance":"0","idtype":"2","prix_ht":"0.00","prix_ttc":"0.00","poids_net":"0","idpays":"1","idpilulier":"1","idetiquette":"1","actif":"0","intra_type":{"id":"2","type_fr":"Vcaps"}}}

As you can see, idprod is in Réponse at 0.
When I want to modify, still have the same error :
Notice: Undefined offset: 0 in /home/.sites/28/site1/web/intra/datatable/php/lib/Editor/Editor.php on line 544
{"id":"row_25","error":"","fieldErrors":[],"data":[],"row":null}

Firebug Post :
action edit
data[actif] 1
data[codebarre2013] 3700195660740
data[contenance] 60
data[idetiquette] 1
data[idpays] 1
data[idpilulier] 1
data[idtype] 1
data[nouveaute] 1
data[poids_net] 15
data[prix_ht] 1.00
data[prix_ttc] 1.00
data[ref] ACI60
id row_25
idprod 19
table

Thanks.
Dts1

allan

> When I add a new record, no error message but idprod in my datatabase is 0

You are setting the `where` condition - but not the field. So the died value isn't being entered in tot he database because it hasn't been instructed to do so. You need to add an extra `Field` instance with the value set as required in the data submitted.

Allan

dts1

Victory !!

thank you so much Allan.

AKM3

Allan,
I've a question regarding the where method.

When it's implemented, on POST action create fires an error:
Notice: Undefined offset: 0 in /datatables/lib/Editor/Editor.php on line 507
Call Stack
# Time Memory Function Location
1 0.0003 250640 {main}( ) ../admin-dashboard.php:0
2 0.0161 2384144 DataTables\Editor->process( ) ../admin-dashboard.php:36
3 0.0165 2438104 DataTables\Editor->_insert( ) ../Editor.php:333


So to avoid this error I just add the methods to the editor object in parts and add the where part only when there is no action:
if (!isset( $_POST['action'] )) $editor->where('toid',$_SESSION['UID'] );

I didn't went through all your class, but it seems for some reason the where is creating an error for me when fired on POST mode.
My server side part is:
[code]
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= Editor::inst( $db, 'my_dashboard' )
->pkey('did')
->fields(
Field::inst( 'did' ),
Field::inst( 'fromid' ),
Field::inst( 'toid' ) ->validator('Validate::required'),
Field::inst( 'day' ) ->validator('Validate::dateFormat'),
Field::inst( 'hour' ) ->validator('Validate::required'),
Field::inst( 'message' )->validator('Validate::required')
->setFormatter( function($_, $opts=null ) {
return strtolower( $_ );
}),
Field::inst( 'link' ) ,
Field::inst( 'status' ) ->validator('Validate::required')
);

if (!isset( $_POST['action'] )) $editor->where('toid',$_SESSION['UID'] );

$editor->process( $_REQUEST )
->json();
[/code]

Anyways with this little workaround is fine, just posting it in case someone finds it useful!

allan

That's interesting. Thanks for posting this. I will take a look and see what is going wrong.

Allan