Cascading Lists in Editor

cpshart

Hi

I am trying to create a subcategory dropdown list based upon the selection of a category from the previous dropdown list, but I have been struggling for ages to get it working, but I suspect I am close.

I select dm_cash_accounts_transactions.category_id
and want to filter the dropdown
dm_cash_accounts_transactions.subcategory_id
for the above category

Do I need to define the options in the format shown below currently commented out

           }, {
                label: "category:",
                name: "dm_cash_accounts_transactions.category_id",
                type: "select",
**//                options: json.dm_cash_accounts_transactions.category_id,
**                placeholder: "Select category"
           }, {
                label: "subcategory:",
                name: "dm_cash_accounts_transactions.subcategory_id",
                type: "select",
**//                options: json.dm_cash_accounts_transactions.subcategory_id,
**                placeholder: "Select subcategory"
           }, {
                label: "account:",
                name: "dm_cash_accounts_transactions.cash_accounts_id",
                type: "select",
                placeholder: "Select account"
            }
        ]
    } );    
            
    editor.dependent( 'dm_cash_accounts_transactions.category_id', function ( val, data, callback ) {
    $.ajax( {
        url: '../../Editor-PHP-1.8.1/controllers/ajax_cash_accounts_transactions.php',
        data: { "category_id": val },
        dataType: 'json',
**        success     : function ( json ) {
           console.log( 1, JSON.stringify( json ) );
                        callback( json );
                      }    
**  });
    })
 

if I enable commented out lines I get the following json error

**(index):752 Uncaught ReferenceError: json is not defined
**

            { data: "dm_api_category.name",
**/*                render: function ( d ) {
                    return json.dm_cash_accounts_transactions.category_id[ d ];
                }*/
            },
            { data: "dm_api_subcategory.name", 
/*              render: function ( d ) {
                    return json.dm_cash_accounts_transactions.subcategory_id[ d ];
                }*/
**          }
        ],

Extract of PHP file on server, ajax_cash_accounts_transactions.php

$category_id = $_GET['category_id']; // manually setting variable for testing
$subcategory_array = array();

echo "the value of category_id is :" . $category_id . ":" . "\n";

try {
    $pdo = new PDO(strtolower($sql_details['type']) . ":host=" . $sql_details['host'] . ";dbname=" . $sql_details['db'], $sql_details['user'], $sql_details['pass']);
    // set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully" . "\n\n"; 
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }


$result = $pdo->query("SELECT id, name FROM dm_api_subcategory WHERE category_id = $category_id");
        foreach ($result as $row) {
                echo "" . $row['id'] . "";
                echo " : " . $row['name'] . "\n";

            array_push( $subcategory_array, array('value'=>$row['id'], 'label'=>$row['name']) );

        }

echo json_encode( [
        'options' => [
            'dm_cash_accounts_transactions.subcategory_id' => $subcategory_array
    ]
] );


<?php
>
```
?>


PHP file on server, added options

    //options needed for cascading dropdown list    
    Field::inst( 'dm_api_subcategory.category_id' ) 
        ->options( 'dm_api_subcategory', 'id', 'dm_api_subcategory.id' ),
    Field::inst( 'dm_api_subcategory.name' )
        ->options( 'dm_api_subcategory', 'name', 'dm_api_subcategory.name' ),

/* cash account */
Field::inst( 'dm_cash_accounts_transactions.cash_accounts_id' )
->options( Options::inst()
->table( 'dm_cash_accounts' )
->value( 'id' )
->label( array('code', 'name') )
->where( function ( $q ) use ( $userid) {
$q->where( 'dm_cash_accounts.user_id', $userid);
} )

        )
        ->validator( Validate::notEmpty( ValidateOptions::inst()
            ->message( 'Please select cash account ..' )
        ) ),    
    Field::inst( 'dm_cash_accounts.code' ),
    Field::inst( 'dm_cash_accounts.name' )

)

->leftJoin( 'dm_cash_accounts', 'dm_cash_accounts.id', '=', 'dm_cash_accounts_transactions.cash_accounts_id' )
->leftJoin( 'dm_api_category', 'dm_api_category.id', '=', 'dm_cash_accounts_transactions.category_id' )
->leftJoin( 'dm_api_subcategory', 'dm_api_subcategory.id', '=', 'dm_cash_accounts_transactions.subcategory_id' )
->where( function ( $q ) use ( $userid) {
$q->where( 'dm_cash_accounts_transactions.user_id', $userid);
} )

->process($_POST)
->json();
```

Data coming back from server for a selection of category_id = 10 label, Savings

{"options":{"dm_cash_accounts_transactions.subcategory_id":[{"value":"52","label":"Car fund"},{"value":"53","label":"Electrical fund"},{"value":"54","label":"General savings"},{"value":"55","label":"Holiday fund"},{"value":"56","label":"Other fund"},{"value":"57","label":"Property fund"},{"value":"58","label":"Rainy day fund"},{"value":"59","label":"Wedding fund"}]}}

I am hoping I have made a simple error somewhere, help on this would be much appreciated.

Many Thanks

Colin

allan

The only thing that immediately stands out is the echo "" . $row['id'] . ""; and the following line. That's going to make the result invalid JSON.

Beyond that, can you give me a link to the page so I can debug it live? There is a fair amount of code for me to wrap my head around there.

Allan

cpshart

Hi allan

Thanks for your response, I tried removing the echo comments but to no avail, I believe as they do not form part of the JSON.

Can I send you a private message of login details to my site to look at the problem, with thanks, if so how do I send you a pm ?

Thanks

Colin

cpshart

Hi Allan

I have copied the JSON above into https://jsonlint.com/ and it shows it to be valid JSON data.

Thanks

Colin

colin

Hi @cpshart ,

If click on the users name here, then you'll see the message option at the top of the page.

Cheers,

Colin

cpshart

Many Thanks Colin, I will send the message now, I have spent some time reviewing your example

https://datatables.net/blog/2017-09-01

I can see why my example is not working due to the matching qualifiers in your JSON data

https://datatables.net/media/blog/2017-09-01/data

with the client file. It helps having your example.

Best Regards

Colin