Validation of dependent fields

d-pod

Hello,

I am trying to do the following but have not found a solution yet.

I have 7 fields in my table. For example, if field 1 is filled, field 2 must not be filled. In the frontend, field 2 is hidden when field 1 is set. However, field 2 can be filled before field 1 and then it will be saved anyway. I think I have to validate with PHP. Is there a way to validate the fields depending on each other?

allan

Hi,

Yes indeed, a global validator is probably the best option.

With a custom field validator you do actually have the ability to access the data from the rest of the row (second parameter passed in $data), so you could do it there - it depends a little bit on if you want the error to appear next to a specific field or in the global error area.

Allan

d-pod

Hi Allan,

thank you very much for the quick reply. It worked!

Now I have another question. I have to make sure that a combination of 2 fields can occur only once in the database. Is there a solution for this as well? I know that for single fields it can be solved with ::unique. Is there also something for combinations?

allan

There isn't a built in option for that I'm afraid, it just works with a single column.

You'd need to use a custom validator for this as well, which you could possibly based on our unique validator for a single column.

The other option, if you are okay with an SQL being shown in the error box (depends what your application is being used for) is to just use an SQL constraint and make the two columns unique together.

Allan

d-pod

Hello Allan,

thank you very much for your help. Unfortunately, the problem cannot be solved with SQL, because the combination should only be unique if Field1 has the value 1.

Example: Field1 = 1; Field2 = 2; Field3 = 3;

If there is this case: "Field1 = 2; Field2 = 2; Field3 = 3;" this record may occur more than once.

I have to solve the problem in my PHP file where the validation is located with the following code:

Solution 1:

<?php

 // DataTables PHP library
include( "../../../assets/plugins/custom/editor/lib/DataTables.php" );

 // Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

    $result = $db->sql( "SELECT CASE WHEN count(distinct [Field1])= count([Field1]) AND count(distinct [Field2])= count([Field1]) AND [Field3] = 1
    THEN 0 ELSE 1 END FROM Table1" );
    $response = $result->fetchAll();

     if($response === 0) {
        $editor = Editor::inst( $db, 'Table1', 'ID' )
            ->fields(
                Field::inst( 'Field1' )
                    ->validator(Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'Field1 is required!' ))),
                Field::inst( 'Field2' ),
                Field::inst( 'Field3' )
                    ->validator( Validate::numeric( '.', ValidateOptions::inst()
                        ->message( 'Decimal expected' )))
            )
            ->process( $_POST )
            ->json();
    }

Solution 2:

<?php

 // DataTables PHP library
include( "../../../assets/plugins/custom/editor/lib/DataTables.php" );

 // Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

    $where = "Field1 = "."'".$_POST['data']['Field1']."'";
    $where .= " AND Field2 = "."'".$_POST['data']['Field2']."'";
    $where .= " AND Field3 = 1";
    $result = $db->sql( "SELECT * FROM Table1 WHERE " . $where);
    $response = $result->fetchAll();

    if(count($response) === 0) {
        $editor = Editor::inst( $db, 'Table1', 'ID' )
            ->fields(
                Field::inst( 'Field1' )
                    ->validator(Validate::notEmpty( ValidateOptions::inst()
                        ->message( 'Field1 is required!' ))),
                Field::inst( 'Field2' ),
                Field::inst( 'Field3' )
                    ->validator( Validate::numeric( '.', ValidateOptions::inst()
                        ->message( 'Decimal expected' )))
            )
            ->process( $_POST )
            ->json();
    }

Unfortunately, I noticed that an error is thrown if any code is added outside of the $editor variable. Is there any way around this?

Do you have any other suggestion for this case?

Translated with www.DeepL.com/Translator (free version)

allan

I'm not sure I understand what you mean I'm afraid. What is the error message you are being given?

For validation of a single field, use a custom field validator. I'm not sure why you are doing a conditional query before the Editor instance?

Allan

d-pod

When I add my code, i get " Invalid JSON response" out in the browser.

Validating individual fields is no problem. It's about validating a combination of multiple fields. If the combination of field1, field2 and field3 is already in the database, it must not occur again. So I wanted to check for that before doing the rest of the validation.

The peculiarity is that a combination of field1 and field2 may occur more than once. I hope now you can understand it better.

allan

When I add my code, i get " Invalid JSON response" out in the browser.

What is the response from the server? Most likely it will contain an error message which will let us resolve the problem. It might be a syntax error for example.

Allan

d-pod

Hello Allan,

sorry for the late reply. The Apache error log contains the following message: "client denied by server configuration: /my-html-file.html". I googled for the error and checked the Apache configuration. However, everything is correct. I can't think of any other way to proceed here. Do you have another suggestion?

colin

I'm guessing you've seen this SO thread, there are a few good suggestion there. Have you checked the file permission of the HTML referenced in the error?

Colin