Check for duplicates

nskwortsow

Hi Allan,
In your API, how would I check for the existence of a duplicate value (e.g. each patient should have a unique email address), and report an error back to editor?
Thanks,
Nathan

SergeHipontoise

Interested in that too

allan

What you would do is have a `validator` method ( http://editor.datatables.net/docs/current/php/class-DataTables.Editor.Field.html#_validator ) which itself describes a function which will make the database lookup for the submitted value and then report the item as valid or not. Simply doing a `select` with a suitable `where` and checking the number of rows returned would do.

Allan

djtyroon1

Hello Allan,

Interested in that too, can you give to us a basic example ?

Best and thanks in advance.

$editor = Editor::inst( $db, 'tablename', 'id' )
->fields(
Field::inst( 'idtodepuplicate' )->validator( function($val, $data, $opts) {

//how to query and do anything ?
return true;
} )
)
);

allan

Hi djtyroon1,

You'd use the `select` method for the DB instance: http://editor.datatables.net/docs/current/php/class-DataTables.Database.html#_select . Then based on the return from that, you'd make a decision on if it is valid or not - the `result` instance from the `select` has a `count` method which would be perfect to check if any rows were found or not: http://editor.datatables.net/docs/current/php/class-DataTables.Database.Result.html#_count

Regards,
Allan

djtyroon1

Thank you for your quick response.

I am currently on this page but I am unable to use it without examples.

I looked for examples such as:
http://datatables.net/forums/discussion/13149/filtering-query-where/p1#
and
http://www.datatables.net/forums/discussion/5788/server-side-processing-with-ms-sql-database/p1#
[code]
$editor = Editor::inst( $db, 'tablename', 'id' )
->fields(
Field::inst( 'idtodepuplicate' )->validator( function($val, $data, $opts) {


$count = $db
->query( 'select', 'tablename' )
->get( 'id' )
->where( 'idtodepuplicate',$val,'=' )
->exec()
->count();
}
if($count !== 0){
return "Duplicate";
}
return true;
} )
)
);

//right ?
[/code]

But I can not apply. I do not understand the usage syntax.

Best,

allan

Try:

[code]
$rows = $db
->select( 'tablename', 'id', array(
'id' => $val
) )
->count();
[/code]

That will give you the number of rows in the `$rows` variable.

I see that your trial has actually expired now. How have you found it? Are there any other questions you have about Editor?

Regards,
Allan

djtyroon1

Thanks Allan,

I currently access here : http://editor.datatables.net/docs/current/php/class-DataTables.Database.Query.html

I wanted to take a license, but the use of the Editor lack of examples. Unlike DataTables, Editor tool is not explicit enough.

Thanks for this example i will test it, and thanks for all your answers in many posts, (help me a lot).

allan

> I wanted to take a license, but the use of the Editor lack of examples

Perhaps you could explain what examples you think are missing from the list here: http://editor.datatables.net/examples/ and I'll take a look at adding them.

Regards,
Allan

djtyroon1

I just try your example :

[code]
Editor::inst( $db, 'xm_programmes', 'ID' )
->fields(
Field::inst( 'P' )->validator( function($val, $data, $db) {
if ( empty($val) ) {
return "This field is required.";
}
$checkduplicate = $db
->select( 'xm_programmes', 'ID', array(
'P' => $_POST['data']['P']
) )
->count();
if($checkduplicate !== 0){
return "Duplicate !";
}elseif(!is_numeric($val)){
return "This field must be numeric only.";
}
return true;
} ),
[/code]

I got Fatal error: Call to undefined method DataTables\Editor\Field::select()

What's wrong ?

Thanks

allan

`$db` isn't passed into the validator function. Its an options array as you had in your previous post (where you had `$opts` ). I don't see anything in the documentation to suggest it is the $db parameter - but perhaps there is something misleading that I didn't see?

You need the `$db` variable, so just add `global $db;` in the closure.

Allan

djtyroon1

Hello Allan,

Thanks for your help, it work now.

Maybe this code will help someone, how to check duplicate on the browser field :
[code]
<?php

/*
* Example PHP implementation used for the index.html example
* DataTables Editor basic 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, 'browsers' )
->fields(
Field::inst( 'engine' )->validator( 'Validate::required' ),
Field::inst( 'browser' )->validator( function($val, $data, $opts) {
global $db;
if ( empty($val) ) {
return "This field is required.";
}else{
$checkdup = $db
->select( 'browsers', 'id', array(
'browser' => mysql_real_escape_string($val)
) )
->count();
if($checkdup !== 0){
return "Duplicate browse.";
}
}
return true;
} ),
Field::inst( 'platform' ),
Field::inst( 'version' ),
Field::inst( 'grade' )->validator( 'Validate::required' )
)
->process( $_POST )
->json();
[/code]
Best

vinod_ccv

Hi Allan,
This code helps in solving duplication issue while posting new row (create) But while editing this code blocks any changes saying it is duplication. Kindly advice to get ride of counting same raw which is being modified by current post.
Thanking you.

allan

Do a check on the `action` parameter that is submitted to ensure that it is `create` .

Allan

vinod_ccv

Hi Allan,
But how to avoid duplication while editing/modifying data
I not possible, how to hide a particular field only while editing so that it can't be changed.
Thank u

vinod_ccv

[code]
$checkdup1 = $db
->select( 'browsers', 'id', array(
'browser' => $val
) )
->count();
if($checkdup1 =>2){
return "Duplicate browse.";}
if($checkdup1 ==1){
///here checking for duplication is caused by the same row which is being edited//
$checkdup2 = $db
->select( 'browsers', 'id', array(
'browser' => $val,'id'=>$id
) )
->count();
if($checkdup2 !== 1){
return "Duplicate browse.";
}
[/code]

This gives me Undefined variable id, Please help with this

allan

> But how to avoid duplication while editing/modifying data

As I said, check the submitted action before doing the duplicate check.

[code]
if ( $_POST['action'] === 'create' ) {
// Do duplicate check
}
[/code]

> This gives me Undefined variable id, Please help with this

What is $id meant to be? The ID sent from the client? Then you want to use `$_POST['id']` no?

Allan

vinod_ccv

[quote]allan said: What is $id meant to be[/quote]
Here in checkup1 if it is found one more duplication I have to make sure that it is not the same row I am editing with.
$id here is the id of the row being edited. I have the field name id in the data base, and want to make sure that it is not the one i counted as duplicate . I am getting Undefined index: id while using $_POST['id']

allan

Editor submit's the variable `id` in the POST (see the documentation here: http://editor.datatables.net/server/ ), so if that script is what Editor is calling there should be an ID parameter being sent. Please link me to a test case if this is not happening.

Allan

vinod_ccv

Missed to mention one thing that I am using REST interfaces method. In Firebug I could see that under 'Put' parameter 'id' exists. But when trying to collect this value $_POST['id'] i m getting error "Undefined index". Even $_POST['action'] not working in this instance while using it inside CRUD action scripts.

vinod_ccv

Solved the issue . The reason was in REST interface for editing PUT is used. Hence it was $args['action'] is 'create' while editing and $_POST['action'] is 'create' while adding new.
[code]
global $args;
if($args==""){$postcheck=$_POST['action'];}else{$postcheck=$args['action']; $postid=trim($args['id'],"row_");}

if($postcheck=='create' ){..............................}
if($postcheck=='edit' ){..............................}

[/code]
may be useful for some one.