Insert unique code to a field while import csv file

Sarbast · January 2021

Hi,
I have two php functions which generate a unique code, so I use these function with the datatables editor and it work fine, but when import data as csv file the field of unique code get the same code and when add manually the code would be unique.

Note: I added the two functions at the begining of the server site file and use "setValue( generateKey() )" to call function for unique code and save into db.

Thanks in advance

colin · January 2021

Could you post your code, please, so we can take a look.

Colin

Sarbast · January 2021

Hi colin,

Here my full code:

client side:

<script type="text/javascript" language="javascript" class="init">
    


var editor; // use a global for the submit and return data rendering in the examples

// Display an Editor form that allows the user to pick the CSV data to apply to each column
function selectColumns ( editor, csv, header ) {
    var selectEditor = new $.fn.dataTable.Editor();
    var fields = editor.order();
    
    var toUpdate = []; // array of records to update
    var toUpdateIds = []; // array of the IDs of the records to update
    var toCreate = []; // array of the records to create
 
    var table = $('#students').DataTable();
    var fieldData;

    
    // Function to split the records in the import file into those to update and those to create
    function parseCSV(PKField) {
        // first_name is column position 0 in our test environment
        var colData = table.column(0).data({
            order: 'index'
        }).toArray();
        var pos;
 
        for (var j = 0; j < csv.length; j++) {
            pos = colData.indexOf(csv[j][PKField]);
            if (pos > -1) {
                // it exists, so we need to add to update list
                toUpdate.push(csv[j]);
                toUpdateIds.push(pos);
            } else {
                // doesn't exist, so add to create list
                toCreate.push(csv[j]);
            }
        }
    }
 
    // function that updates the existing records
    function updateRecords() {
        if (toUpdate.length > 0) {
            editor.edit(toUpdateIds, {
                title: 'Confirm update',
                buttons: 'Update',
                message: 'Click the <i>Submit</i> button to confirm the update of ' + toUpdate.length + ' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
            });
 
            for (var i = 0; i < fields.length; i++) {
                var field = editor.field(fields[i]);
                var mapped = fieldData[field.name()];
 
                for (var j = 0; j < toUpdate.length; j++) {
                    field.multiSet(table.row(toUpdateIds[j]).id(), toUpdate[j][mapped]);
                }
            }
        }
    }
 
    // function to create the records (same as the example on website)
    function createRecords() {
        if (toCreate.length > 0) {
            editor.create(toCreate.length, {
                title: 'Confirm import',
                buttons: 'Submit',
                message: 'Click the <i>Submit</i> button to confirm the import of ' + toCreate.length + ' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
            });
 
            for (var i = 0; i < fields.length; i++) {
                var field = editor.field(fields[i]);
                var mapped = fieldData[field.name()];
 
                for (var j = 0; j < toCreate.length; j++) {
                    field.multiSet(j, toCreate[j][mapped]);
                }
            }
        }
    }
 
    for (var i = 0; i < fields.length; i++) {
        var field = editor.field(fields[i]);
 
        selectEditor.add({
            label: field.label(),
            name: field.name(),
            type: 'select',
            options: header,
            def: header[i]
        });
    }
 
    selectEditor.create({
        title: 'Map CSV fields',
        buttons: 'Import ' + csv.length + ' records',
        message: 'Select the CSV column you want to use the data from for each field.'
    });
 
    selectEditor.on('submitComplete', function(e, json, data, action) {
        // Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
        fieldData = data;
 
        // using first_name as the PK in this example
        parseCSV(data[editor.field('student_ku').name()]);
 
        // If any records to update, do them first
        if (toUpdate.length > 0) {
            // and then queue the creation for after the updates have been submited
            editor.one('submitComplete', function() {
                createRecords();
            });
            updateRecords();
        } else {
            // nothing to update, so just jump in and create the records
            createRecords();
        }
    });
}

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        "ajax": "../controllers/students.php",
        "table": "#students",
        "fields": [ 
             {
                "label": "Student (Ku.):",
                "name": "student_ku"
             },{
                "label": "Student (Ar.):",
                "name": "student_ar"
             },{
                "label": "Student (En.):",
                "name": "student_en"
             }
        ]
    } );

// Buttons array definition to create previous, save and next buttons in
    // an Editor form
    var backNext = [
        {
            text: "&lt;",
            action: function (e) {
                // On submit, find the currently selected row and select the previous one
                this.submit( function () {
                    var indexes = table.rows( {search: 'applied'} ).indexes();
                    var currentIndex = table.row( {selected: true} ).index();
                    var currentPosition = indexes.indexOf( currentIndex );

                    if ( currentPosition > 0 ) {
                        table.row( currentIndex ).deselect();
                        table.row( indexes[ currentPosition-1 ] ).select();
                    }

                    // Trigger editing through the button
                    table.button( 1 ).trigger();
                }, null, null, false );
            }
        },
        'Save',
        {
            text: "&gt;",
            action: function (e) {
                // On submit, find the currently selected row and select the next one
                this.submit( function () {
                    var indexes = table.rows( {search: 'applied'} ).indexes();
                    var currentIndex = table.row( {selected: true} ).index();
                    var currentPosition = indexes.indexOf( currentIndex );

                    if ( currentPosition < indexes.length-1 ) {
                        table.row( currentIndex ).deselect();
                        table.row( indexes[ currentPosition+1 ] ).select();
                    }

                    // Trigger editing through the button
                    table.button( 1 ).trigger();
                }, null, null, false );
            }
        }
    ];


// Upload Editor - triggered from the import button. Used only for uploading a file to the browser
    var uploadEditor = new $.fn.dataTable.Editor( {
        fields: [ {
            label: 'CSV file:',
            name: 'csv',
            type: 'upload',
            ajax: function ( files ) {
                // Ajax override of the upload so we can handle the file locally. Here we use Papa
                // to parse the CSV.
                Papa.parse(files[0], {
                    header: true,
                    skipEmptyLines: true,
                    complete: function (results) {
                        if ( results.errors.length ) {
                            console.log( results );
                            uploadEditor.field('csv').error( 'CSV parsing error: '+ results.errors[0].message );
                        }
                        else {
                            uploadEditor.close();
                            selectColumns( editor, results.data, results.meta.fields );
                        }
                    }
                });
            }
        } ]
    } );

    var table = $('#students').DataTable( {
         mark: true,
        "bLengthChange": true,
        "fixedHeader": true,
        "responsive": true,
         colReorder: true,
        "ordering": true,
        "stateSave": true,
        dom: "Bfrtip",
        ajax: "../controllers/students.php",
        columns: [
            { data: "student_ku" },
            { data: "student_ar" },
            { data: "student_en" }
        ],
        select: true,
        buttons: [
        { extend: "create", editor: editor },
        {
                extend: 'selected',
                text:   'Edit',
                action: function () {
                    var indexes = table.rows( {selected: true} ).indexes();

                    editor.edit( indexes, {
                        title: 'Edit',
                        buttons: indexes.length === 1 ?
                            backNext :
                            'Save'
                            
                    } );
                }
        },
        { extend: "remove", editor: editor }
        ]
    } );
} );

</script>

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Sarbast · January 2021

server side:

<?php session_start(); ?>
<?php
require ('../../../dbc/ConnectwDB.php');
$DB = new ConnectwDB; 
$connwdb = $DB->connectDB();

function checkKey($connwdb, $randStr) {
$keyExists = "";
$sql = $connwdb->prepare("SELECT * FROM students"); 
$sql->execute();

while($row = $sql->fetch(PDO::FETCH_ASSOC)){
if($row['student_code'] == $randStr){
$keyExists = true;
break;
} else{
$keyExists = false;
}
}
return $keyExists;
}

function generateKey($connwdb){

$keyLength = 8;
$str = "1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ";
$randStr = substr(str_shuffle($str), 0, $keyLength);

$checkKey = checkKey($connwdb, $randStr);
while($checkKey == true){
$randStr = substr(str_shuffle($str), 0, $keyLength);
$checkKey = checkKey($connwdb, $randStr);
}
return $randStr;
}


/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "../../plugins/datatables-editor/lib/DataTables.php" );

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

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'students' )
    ->fields(
        Field::inst( 'student_ku' ),
        Field::inst( 'student_ar' ),
        Field::inst( 'student_en' ),
        Field::inst( 'student_code' )
             ->setValue( generateKey($connwdb) ),
        Field::inst( 'stage' )
             ->setValue( $_SESSION['stage'] ),
        Field::inst( 'dept_id' )
            ->setValue( $_SESSION['dept_id'] )
    )
        ->where( 'dept_id', $_SESSION['dept_id'])
        ->where( 'stage', $_SESSION['stage'])
        ->on( 'preEdit', function ( $e, $id, $values ) {
             $e->field( 'student_code' )->set( false );
           })
    ->process( $_POST )
    ->json();

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan · January 2021

but when import data as csv file the field of unique code get the same code and when add manually the code would be unique.

I’m sorry, it is probably too early in the morning here, but I’m not understanding that. Could you explain a bit more please?

Allan

Sarbast · January 2021

Hi allan,
I meant when insert several rows into table via "import csv" the field "student_code" take the same code and it must be defferent at each row.
Example:

First row: student_name= Mark, student_code= RR12TYUI
Second row: student_name= Jane, student_code= RR12TYUI
And so on
As you see the "student_code" take the same code.

thanks

allan · February 2021

Right - I think I understand what is going on now - thanks for the clarification. The way the CSV import example works is that it uses Editor's multi-row editing ability. That means it will submit all of the imported rows in a single request.

You are currently only calling generateKey() once and setting the value to be the same for all rows. Hence why it is the same for all rows from an import.

What I think you will need to do here is use a preCreate event listener in the PHP code to modify the value for each row being submitted - e.g.:

->on( 'preCreate', function ( $editor, &$values ) {
  $values['student_code'] = generateKey($connwdb);
} )

This will work as preCreate is called once per row submitted.

Allan

Sarbast · February 2021

thanks allan

Sarbast · February 2021

Hi allan
it worked perfectly after adding "use ($connwdb)" because this variable is out of function's scope.

thanks again

Sarbast · February 2021

Hi allan,

I have another problem with "import csv" I link the "students" table with "gender" table when import csv to "students" table all text and number fields such as name, email, mobile number become number, so my question here does import csv work with linked tables or not?
if yes, could you please help me to solve this problem.

Thanks

Sarbast · February 2021

allan · February 2021

I always forget about the variable scoping in PHP! Good to hear that (almost) worked).

The example import to CSV doesn’t take into account linked tables. If your CSV had the linked primary key in it, then it would just work as is. But if you need to lookup the linked primary key, that is something that would need to be added into the import processing code at the server-side before Editor sees it to insert into the database - in preCreate for example.

Allan

Sarbast · February 2021

Thanks alot
allan

Insert unique code to a field while import csv file

Insert unique code to a field while import csv file

Replies

Howdy, Stranger!

Categories

DataTables

Insert unique code to a field while import csv file

Insert unique code to a field while import csv file

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables