Check for duplicates

Check for duplicates

nskwortsownskwortsow Posts: 120Questions: 0Answers: 0
edited November 2012 in Editor
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

Replies

  • SergeHipontoiseSergeHipontoise Posts: 5Questions: 0Answers: 0
    Interested in that too
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • djtyroon1djtyroon1 Posts: 5Questions: 0Answers: 0
    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;
    } )
    )
    );
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    edited January 2013
    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
  • djtyroon1djtyroon1 Posts: 5Questions: 0Answers: 0
    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,
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • djtyroon1djtyroon1 Posts: 5Questions: 0Answers: 0
    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).
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    > 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
  • djtyroon1djtyroon1 Posts: 5Questions: 0Answers: 0
    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
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    `$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
  • djtyroon1djtyroon1 Posts: 5Questions: 0Answers: 0
    edited January 2013
    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_ccvvinod_ccv Posts: 75Questions: 0Answers: 0
    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.
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Do a check on the `action` parameter that is submitted to ensure that it is `create` .

    Allan
  • vinod_ccvvinod_ccv Posts: 75Questions: 0Answers: 0
    edited February 2013
    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_ccvvinod_ccv Posts: 75Questions: 0Answers: 0
    edited February 2013
    [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
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    > 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_ccvvinod_ccv Posts: 75Questions: 0Answers: 0
    edited February 2013
    [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']
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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_ccvvinod_ccv Posts: 75Questions: 0Answers: 0
    edited February 2013
    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_ccvvinod_ccv Posts: 75Questions: 0Answers: 0
    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.
This discussion has been closed.