Can I generate selector in edit form from ajax

tatevikz

Hi.
I have the following task to solve.
In my edit form I need to have a selector, which needs to be generated from the following DB query

SELECT products_id,color_code,`products_price` FROM `products` GROUP BY (color_code)
Is there a feature to generate the selector from ajax or maybe some other method?

Thanks in advance

colin

Hi @tatevikz ,

Is this using Editor's edit form? Or your own?

Cheers,

Colin

tatevikz

Hi @colin .
Sorry forgot to mention. Using Datatable Editor's Edit form:

    editor = new $.fn.dataTable.Editor( {
        ajax: "ajax/get_table_values.php",
        table: "#myTable",
        fields: [   
            {
                label: "Product ID",
                name: "products.products_id",
                type: "hidden"
            }, {
                label: "Categories Name:",
                name: "categories_description.categories_name"
            }, {
                label: "Categories Heading Title:",
                name: "categories_description.categories_heading_title"
            }, {
                label: "Categories Description:",
                name: "categories_description.categories_description"
            }, {
                label: "Concert Venue:",
                name: "categories_description.concert_venue",
                type:  "select",
                options: [
                    { label: "Monday", value: "Monday" },
                    { label: "Tuesday", value: "Tuesday" },
                    { label: "Wednesday", value: "Wednesday" },
                    { label: "Thursday", value: "Thursday" },
                    { label: "Friday",  value: "Friday" },
                    { label: "Saturday",  value: "Saturday" },
                    { label: "Sunday",  value: "Sunday" }
                ]               
            }, {
                label: "Date ID:",
                name: "categories.date_id",
                type: "datetime",
                def:  function () { return new Date(); },
                format: 'YYYY-MM-DD h:mm',
                opts: {
                    minutesIncrement: 5
                }
            }, {
                label: "Products Price:",
                name: "products.products_price"
            }, {
                label: "Color Code:",
                name: "products.color_code"
            }, {
                label: "Categories Status:",
                name: "categories.categories_status",
                type: "radio",
                options:    [
                    { label: "Uncheck", value: 0 }, //1
                    { label: "Check", value: 1 }, //1
                ]
            }
        ]
    } );

I want the selector on products.color_code to be selected as distinct from DB products table.

Thanks

colin

Hi @tatevikz ,

You could use dependent() to do that, on that page there's a couple of examples where Ajax is being called to get values for the field. You won't be able to issue SQL on the client, so you'll need the server-side script to run that SQL statement.

Hope that helps,

Cheers,

Colin

tatevikz

Hi @colin.draffin@gov.ab.ca
Thanks for your answer. That help a lot.
Now can you help me with the following implementation of the dependent() concept:

    editor.dependent('products.color_code', function(val,data,callback){
        $.ajax ( {
            url         : 'ajax/update_color_code.php',
            data        : {
                        "cat_id"        : editor.field('categories_description.categories_id').val()
                        },
            type        : 'post',
            dataType    : 'json',
            success     : function ( json ) {
            }
        });
    }) ;

the outpus json is

"options: ": [["85.0000", "blue"], ["125.0000", "orange"], ["1111.0000", "red"], ["75.0000", "yellow"]]

Now how can I insert this json to the field:

{
            label: "Color Code:",
            name: "products.color_code",
            type:  "select"
        },

tatevikz

Hi @colin
Thanks for your help.

Could you please review the above question as well as I'm stuck in this step

Thanks

allan

In the success callback:

return json.options.map( (item) => {
  return { label: item[1], value: item[0] };
} );

i.e. convert the array of arrays into something that Editor can understand (an array of objects with label/value properties).

Allan

tatevikz

@allan thank you very much for your response.
Sorry but now getting the js error on json.options.map( (item) => { line:
Uncaught TypeError: Cannot read property 'map' of undefined.
Tried different ways but no result.
I'd very appreciate your help again.

Thanks

colin

In your success callback, json is the argument passed in - with options being the array in that structure. If you print both out and debug it, it should get you going.

tatevikz

Hi @colin ,

Thanks for your answer. found the error source.
Now seems I have everything but still this column is showing processing and selector is not being filled with options.
Here is the filed. I suppose I don't need 'options' to be added here.

, {
                label: "Color Code:",
                name: "products.color_code",
                type:  "select"
            },

And here is dependent function:

    editor.dependent('products.color_code', function(val,data,callback){
        $.ajax ( {
            url         : 'ajax/update_color_code.php',
            data        : {
                        "cat_id" : editor.field('categories_description.categories_id').val()
                        },
            dataType    : 'json',           
            type        : 'post',
            success     : function ( jsonData ) {
                return jsonData.options.map( (item) => {
                    return { label: item[0], value: item[1] };
                } );            
            }
        });
    }) ;    

Please help to understand what's wrong.

Thanks

tatevikz

Here is the result.

allan

Can you give us a link to the page so we can take a look please?

Allan

tatevikz

Hi @allan
Unfortunately it's on my localhost.

tatevikz

Hi. Maybe if I show all the codes it'll be more clear. I know that it's something small and simple which i cannot find.
So this is the whole JS

var editor; // use a global for the submit and return data rendering in the examples
 
$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax    : "ajax/get_table_values.php",
        table: "#myTable",
        fields: [   
            {
                label: "Categories ID",
                name: "categories_description.categories_id",
                type: "hidden"
            }, {
                label: "Product ID",
                name: "products.products_id",
                type: "hidden"
            }, {
                label: "Categories Name:",
                name: "categories_description.categories_name"
            }, {
                label: "Categories Heading Title:",
                name: "categories_description.categories_heading_title"
            }, {
                label: "Categories Description:",
                name: "categories_description.categories_description"
            }, {
                label: "Concert Venue:",
                name: "categories_description.concert_venue",
                type:  "select",
                options: [
                    { label: "Monday", value: "Monday" },
                    { label: "Tuesday", value: "Tuesday" },
                    { label: "Wednesday", value: "Wednesday" },
                    { label: "Thursday", value: "Thursday" },
                    { label: "Friday",  value: "Friday" },
                    { label: "Saturday",  value: "Saturday" },
                    { label: "Sunday",  value: "Sunday" }
                ]               
            }, {
                label: "Date ID:",
                name: "categories.date_id",
                type: "datetime",
                def:  function () { return new Date(); },
                format: 'YYYY-MM-DD h:mm',
                opts: {
                    minutesIncrement: 5
                }
            }, {
                label: "Products Price:",
                name: "products.products_price"
            }, {
                label: "Color Code:",
                name: "products.color_code",
                type:  "select"
            }, {
                label: "Categories Status:",
                name: "categories.categories_status",
                type: "radio",
                options:    [
                    { label: "Uncheck", value: 0 }, //1
                    { label: "Check", value: 1 }, //1
                ]
            }
        ]
    } );
    
    editor.dependent('products.color_code', function(val,data,callback){
        $.ajax ( {
            url         : 'ajax/update_color_code.php',
            data        : {
                        "cat_id" : editor.field('categories_description.categories_id').val()
                        },
            dataType    : 'json',
            type        : 'post',
            success     : function ( jsonData ) {
                var colors = "options: [";
                jsonData.options.map( (item) => {
                    colors += '{ label: "'+item[1]+'", value: "'+item[0]+'" },';
                } );
                colors = colors.substr(0, colors.length-1);
                colors += "]";
                callback (colors);
            }
        });
    }) ;    
    $('#myTable').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: 'ajax/get_table_values.php',
            type: 'POST'
        },      
        order: [[ 1, 'asc' ]],
        columns: [
            { data: "categories_description.categories_name" },
            { data: "categories_description.categories_heading_title" },
            { data: "categories_description.categories_description" },
            { data: "categories_description.concert_venue" },
            { data: "categories.date_id" },
            { data: "products.products_price" },
            { data:   "categories.categories_status",
                orderable: false,
                render: function ( data, type, row ) {
                    if ( type === 'display' ) {
                        return '<input type="checkbox" class="editor-categories_status">';
                    }
                    return data;
                },
                render: function (val, type, row) {
                    return val == 0 ? 0 : 1;
                }               
            }
        ],
        select: {
            style:    'os',
            selector: 'td:not(:last-child)'
        },
        buttons: [
            { extend: "edit",   editor: editor },
        ],
    } );
} );

This is the update_color_code.php which is ajax call from dependent() function:
```
<?php
require_once('../connect_to_db.php');
$cat_id = $_POST['cat_id'];
$selectSQL = "SELECT products_price,color_code FROM products
LEFT JOIN products_to_categories ON products.products_id=products_to_categories.products_id
WHERE products_to_categories.categories_id = $cat_id GROUP BY color_code";
$result = mysqli_query($connect,$selectSQL) or die(mysqli_error());
$rows_arr = [];
while ($row = mysqli_fetch_row($result)){
array_push($rows_arr, $row);
}
echo json_encode( array('options' => $rows_arr) );

<?php > ``` This is the JSON value from ajax call: ``` options: [["85.0000", "blue"], ["125.0000", "orange"], ["1111.0000", "red"], ["75.0000", "yellow"]] ``` There is no JS or any other errors on page. console.log(colors) = options: [{ label: "blue", value: "85.0000" },{ label: "orange", value: "125.0000" },{ label: "red", value: "1111.0000" },{ label: "yellow", value: "75.0000" }] ?>

But here is what I see:

Please help me to understand. I was trying to find more examples on dependent(), but not so many.

allan

I think I've just realised what the issue is. Rather than returning something from the success function you need to call the callback() function with the data you want to "return". E.g.

var options = jsonData.options.map( (item) => {
    return { label: item[0], value: item[1] };
} );   

callback( options );

That should sort out both the processing indicator and the list of options.

Allan