On duplicate key update

Khalid Teli

Hello,
I have a table which hold the information about balances.
So, when I insert the data in table , after inserting the balance for first month, it needs to be updated each month. I know I can do it using edit window but data is quite large, so I would like to upload a csv file and update records.

Is there a way to use on duplicate key update? or any other way round?
My client side code looks like this:

<script type="text/javascript" language="javascript">
var editor; // use a global for the submit and return data rendering in the contractss

function selectColumns ( editor, csv, header ) {
    var selectEditor = new $.fn.dataTable.Editor();
    var fields = editor.order();

    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.
        editor.create( csv.length, {
            title: 'Confirm import',
            buttons: 'Submit',
            message: 'Click the <i>Submit</i> button to confirm the import of '+csv.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 = data[ field.name() ];

            for ( var j=0 ; j<csv.length ; j++ ) {
                field.multiSet( j, csv[j][mapped] );
            }
        }
    } );
}


$(document).ready(function() {

   //function for using global filtering
  function filterColumn ( i ) {
    $('#contracts').DataTable().column( i ).search(
        $('#col'+i+'_filter').val()
    ).draw();
}

  editor = new $.fn.dataTable.Editor( {

    "ajax":

       {
        url:"/Editor/Editor-1.9.5/controllers/contracts/contracts_import_balance_fetch.php",


     },

    "table": "#contracts",
    "fields": [
      {
          label: "Product ID:",
          name: "product_id_fk"
        },
         {
          label: "Contract ID :",
          name: "contract_id_fk"
        },
         {
          label: "M1:",
          name: "m1"
        },
        {
         label: "M2:",
         name: "m2"
       },
       {
        label: "M3:",
        name: "m3"
      },
      {
       label: "M4:",
       name: "m4"
     }


    ]
  } );

// 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 tablebalance = $('#contracts').DataTable( {

       "processing": true,
       "serverSide": true,
        "scrollX":true,
        "scrollCollapse": true,
        "ordering": [],
        "stateSave": false,
        "info":   true,
         "ajax":

    {
     url:"/Editor/Editor-1.9.5/controllers/contracts/contracts_import_balance_fetch.php",
           type:'POST',
            "data": function ( d ) {

         var start_date = $('#start_date').val();
                var end_date = $('#end_date').val();

          d.start_date = start_date;
                d.end_date = end_date;

            }

    },
          "dom": 'Brtlip',
   "columns": [
       { data: "contract_id_fk" },
      { data: "product_id_fk" },
      { data: "product_name" },
      { data: "pack_size" },
      { data: "member_name" },
      { data: "contract_prod" },
      { data: "start_date" },
         { data: "end_date" },
           { data: "m1" },
           { data: "m2" },
           { data: "m3" },
           { data: "m4" }

     ],

   select: true,
   buttons: [


     { extend: "create", editor: editor },
     { extend: "edit",   editor: editor },
     { extend: "remove", editor: editor },
      {
                text: 'Import CSV',
                action: function () {
                    uploadEditor.create( {
                        title: 'CSV file import'
                    } );
                }
            },
             buttons: [
            'csv'
        ]
   ]

 } );


})

</script>

server side code looks like this:

<?php

include( "../../lib/DataTables.php" );

use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate,
  DataTables\Editor\ValidateOptions,
    DataTables\Editor\SearchPaneOptions;

$editor = Editor::inst( $db, 'crg_contracts_balance', 'contract_bal_id' )
    ->field(

       Field::inst( 'crg_products.product_code', 'product_code'),
       Field::inst( 'crg_products.product_name', 'product_name' ),
        Field::inst( 'crg_contracts.member_name', 'member_name' ),
        Field::inst( 'crg_contracts.start_date','start_date' ),
        Field::inst( 'crg_contracts.end_date','end_date' ),
        Field::inst( 'supplier.supplier_name','supplier_name' ),
        Field::inst( 'crg_contracts.contract_prod','contract_prod' ),
        Field::inst( 'crg_products.pack_size' , 'pack_size'),
         Field::inst( 'crg_contracts_balance.product_id_fk' , 'product_id_fk'),
        Field::inst( 'crg_products.product_id', 'product_id' ),
        Field::inst( 'crg_contracts_balance.contract_id_fk','contract_id_fk' ),
        Field::inst( 'crg_contracts.contract_id','contract_id' ),
       Field::inst( 'crg_contracts_balance.m1','m1' ),
       Field::inst( 'crg_contracts_balance.m2' ,'m2'),
       Field::inst( 'crg_contracts_balance.m3','m3' ),
       Field::inst( 'crg_contracts_balance.m4','m4' ),


      );

if (isset($_POST['start_date']) && isset($_POST['end_date']) && !empty($_POST['start_date']) && !empty($_POST['end_date']))
{

$editor->where( function ( $q ) {
    $q->where('crg_contracts_balance.start_date' ,$_POST["start_date"], '>=' );
     $q->where('crg_contracts_balance.start_date' ,$_POST["end_date"], '<=' );
} );

}
else{

}

 $editor
   ->leftJoin( 'crg_contracts', 'crg_contracts.contract_id', '=', 'crg_contracts_balance.contract_id_fk' )

  ->leftJoin( 'crg_products', 'crg_products.product_id', '=', 'crg_contracts_balance.product_id_fk' )

  ->leftJoin( 'supplier', 'supplier.supplier_id', '=', 'crg_products.supplier_id_fk' )

 ->debug(true)
  ->process( $_POST )
  ->json();

Thank you

Khalid Teli

Hi,
in relation to the above question , I realised that I think i need something like this but not sure how can this be implemented using csv import

UPDATE crg_contracts SET m1 ='$m_1', m2 ='$m_2',m3 ='$m_3',m4 ='$m_4'

        where   start_date = 'match field from csv file' AND product_id_fk = 'match field from csv file' AND member_name = 'match field from csv file'  " )