Answers

• rf1234 Posts: 2,809Questions: 85Answers: 406

  October 2023 edited October 2023 Answer ✓

  You could use a custom validator and check whether the value already exists for the respective subscriberID.

  Alternatively you could place a unique index on "Details" and "subscriberID" in your database. Whenever the insertion of a duplicate occurs an SQL error (1062 Duplicate entry) will be produced that you could catch at the front end and overwrite with something users understand.

  I use the second way frequently because it is less code to write:

  editor
      .on( 'postSubmit', function ( e, json, data, action ) {
          if (json.error) {
              if ( json.error.indexOf('1062 Duplicate entry') >= 0 ) {
                  json.error = "This Staff Role already exists, please try again."; 
              }
          }
      })
  

  In your PHP script you could do this alternatively (using Editor's db handler):

  Field::inst( 'Details' )
      ->validator( function ( $val, $data, $opts ) use ( $db ) {
          $result = $db->raw()
              ->bind( ':subscriberID',  $subscriberID )
              ->bind( ':Details',       $val )    
              ->exec( 'SELECT COUNT(*) AS valueCount 
                         FROM tblStaffRoles
                        WHERE subscriberID      = :subscriberID
                          AND TRIM(`Details`)   = :Details' );
          $row = $result->fetch(PDO::FETCH_ASSOC);
          if ( (bool)$row["valueCount"] ) {
              return "This Staff Role already exists, please try again.";
          }
          return true;
      } ),
  
• rf1234 Posts: 2,809Questions: 85Answers: 406

  October 2023 Answer ✓

  You might need to change the second line of code in your PHP like this to be able to use $subscriberID in your custom validator.

  ->validator( function ( $val, $data, $opts ) use ( $db, $subscriberID ) { 
  
• ShaneBrennan Posts: 49Questions: 18Answers: 1

  October 2023

  Thank you rf1234, I had to take the trim out of the exec string, but apart from that it worked first time.

  Editor::inst( $db, 'tblStaffRoles' )
  ->fields(
  Field::inst( 'Details' )
  ->validator( function ( $val, $data, $opts ) use ( $db, $subscriberID ) {
  $result = $db->raw()
  ->bind( ':subscriberID ', $subscriberID )
  ->bind( ':Details', trim($val) )
  ->exec( 'SELECT COUNT(*) AS valueCount
  FROM tblStaffRoles
  WHERE subscriberID = :subscriberID
  AND Details = :Details' );
  $row = $result->fetch(PDO::FETCH_ASSOC);
  if ( (bool)$row["valueCount"] ) {
  return "This Staff Role already exists, please try again.";
  }
  return true;
  } ),
  )
  ->where('HBIsubscriberID',$HBIsubscriberID)
  ->process( $_POST )
  ->json();
• rf1234 Posts: 2,809Questions: 85Answers: 406

  October 2023

  Sounds good! Great you got it working. Please use Markdown (see below) to make your code more legible. I mean next time.

  Those built-in validators etc. are quite nice but for more complex use cases you will need to roll your own.