Multiple row insert based on multiple select inputs

eanderson

I have an interesting scenario where I would like to perform one or more inserts simultaneously, depending on the number of options selected within two multiple select inputs (we'll call them Input A and Input B ). I know the multiple option is available for the select field type, so I'm OK with setting up the Editor form. However, I do not want to insert an array of data (i.e., all of the selected options) into a single row. Rather, I want to create a new row for each selected option in Input A. I want Input B to have the same functionality. The result should be an insert of X rows , where X is the product of the number of options selected in Input A and Input B.

Example:

Input A selected options: 1, 2, 3
Input B selected options: a, b, c

3 selected options in Input A * 3 selected options in Input B should result in 9 newly inserted rows:

Row | Input A Destination Column | Input B Destination Column
-------------------------------------------------------------
 1  |  1                         |  a
 2  |  1                         |  b
 3  |  1                         |  c
 4  |  2                         |  a
 5  |  2                         |  b
 6  |  2                         |  c
 7  |  3                         |  a
 8  |  3                         |  b
 9  |  3                         |  c

Maybe there is a way to do this via rows.add(). Would this involve modifying the server side PHP script (I am using server-side processing)? I understand this functionality may not be part of the core DataTables code, but I was primarily looking for some direction on how to achieve something like this.

Edit: I found this page, which states:

Create new rows
Multi-row actions are limited to only editing in Editor, 1.0+ has supported multi-row deletion, but alongside the multi-row editing of 1.5 it is also possible to create multiple rows with a single call. The number of rows to be created is defined by the optional count parameter given to the create() method.

When using multi-row editing field values can be addressed individually through the row id, as discussed above - however, when creating new rows the rows do not have ids assigned until submitted to the server, so the rows are assigned indexes 0...N where N is the number of rows being created.

When creating multiple rows the UI behaviour is identical to the edit behaviour - i.e. input values via the UI are assigned to all items being created. Therefore, the API is far more likely to be useful when creating multiple row.

With that stated, I should be able to loop through my selected items and build a multi-row insert using Editor's create() API? Are there any examples I could look at where someone has done something similar? I didn't find anything that fit my exact use case in the Examples section.

Thanks in advance!

allan

I want to create a new row for each selected option in Input A.

That, I'm sorry to say, is not a feature that is provided by the Editor PHP and .NET libraries. The multi-row create information that you mention above allows you to create multiple rows, but as it also says, it is virtually useless via the UI since all rows would contain the same values.

A multi-value select element is considered to be a single value in terms of the Editor form and would be inserted into a single row (with the exception of a one-to-many join - but that still needs a single parent row).

If you need multiple rows to be created based on the values in a multi-select element there are two options I can think of:

1. Use two different Editor instances - one which presents the UI to the end user and let's the select the values. You would then use a second Editor instance with the multi-row create and use its API to set the values for the rows to be created and have that submit to the server.
2. Instead of using the provided PHP or .NET libraries for Editor, craft your own script that would take the data submitted by Editor and insert the rows as required.

Either way, I think there is going to be a little bit of work that needs to be done .

Allan

eanderson

Thanks for the quick reply, Allan. I think I'll go with option 2, as I have already written both scripts to insert multiple rows into a table with distinct values from a form. I should still be able to access the values in the Editor via some event handler like preSubmit, then split the arrays into distinct insert values, like this:

    var editor;
    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor({
        // options for Editor object omitted for brevity
        });
        editor.on('preSubmit', function(e, data, action) {
            // only alter default behavior on create action
            if(action === 'create') {
                // do some stuff with 'data' here

                // AJAX call to custom PHP script to do the actual inserts

                // return false to prevent Editor from continuing with submit
                return false;
            }
        });
    });

Thanks for confirming what I already thought though!

allan

Yes you could do it that way. Although personally I think I'd probably do the split at the server-side rather than the client.

I don't have a good reason for that though! Just a gut feeling .

Allan

eanderson

The only reason I decided to do client-side data manipulation is because of this statement as the purpose of the preSubmit event:

This event allows you to modify the data that will be submitted to the server.

Doing this allows me to split the array of values for the two multiple select inputs into their own objects, with other attributes (which are the same for all inserted rows), and pass them in an object which is easily consumable by a server-side script:

editor.on('preSubmit', function(e, data, action) {
    if(action === 'create') {
        var insertObj = {};
        var x = 0; // object counter
        // Split arrays for product_id and component_id
        for (var i = 0; i < data["data"]["0"]["component_product"]["product_id"].length; i++) {
            for (var j = 0; j < data["data"]["0"]["component_product"]["component_id"].length; j++) {
                // each insertObj is its own object with attributes for the inserted row
                insertObj[x] = {};
                insertObj[x]["product_id"] = data["data"]["0"]["component_product"]["product_id"][i];
                insertObj[x]["component_id"] = data["data"]["0"]["component_product"]["component_id"][j];
                insertObj[x]["start_date"] = data["data"]["0"]["component_product"]["start_date"];
                insertObj[x]["end_date"] = data["data"]["0"]["component_product"]["end_date"];
                x++;
            }
        }

        // Do the insert
        $.ajax({
            url: "multiinsert.php",
            type: "POST",
            data: insertObj,
            success: function(result) {
                // redraw the table as with normal insert
                editor.close();
                table.ajax.reload();
            },
            error: function(xhr, textStatus, error) {
                // error handling for AJAX call
            }
        });
    }
    return false;
});

On the server:

$config = parse_ini_file($_SERVER['DOCUMENT_ROOT'] . '/../configs/DynamicSKU.ini');
$dsn = 'dblib:host=' . $config['host'] . ';dbname=' . $config['db'];
$pdo = new PDO($dsn, $config['username'], $config['password']);

if($_SERVER['REQUEST_METHOD'] == 'POST') {
    $insert_sql = "INSERT INTO [DynamicSKU].[dbo].[component_product] ([product_id], [component_id], [start_date], [end_date]) VALUES (?, ?, ?, ?)";
    for ($i = 0; $i < count($_POST); $i++) {
        $row = [];
        array_push($row, $_POST[$i]['product_id'], $_POST[$i]['component_id'], $_POST[$i]['start_date'], $_POST[$i]['end_date']);
        $pdo->prepare($insert_sql)->execute($row);
    }
}
echo json_encode("Success");

One other thing I will mention here is that separate Editor instances should be used: one for create (with the multiple select dropdown(s)) and one for edit/delete (with single select dropdowns). The concept of multiple select does not really apply in an editing context, and you may run into some errors if you try to edit a single row which has multiple select inputs, as your destination table will be expecting a single string or integer or whatever and you are passing an array of values, which is likely not the intended behavior. In your Datatables initialization, make sure the buttons refer to the appropriate Editor instance, e.g.:

$('#productComponents').DataTable({
        ...
        buttons: [
            { extend: "create", editor: editor_create },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ]
});

Confirmed the rows were created successfully in the destination table. But there are many ways to skin this cat, as you mentioned earlier. Hopefully this example can help anyone else trying to achieve something similar!

allan

Awesome - thanks for posting your code. I'm sure others will find it useful!

Allan