How idSRC works

Sunilgoel

Hi Support,
I have slno unique key auto increment mysql table field.
I want to update row using editor using slno but do not want to display slno in table . can i use idSRC for this purpose or any other way to do this.

Regards.
Sunil

allan

Hi Sunil,

Are you using the Editor PHP or .NET libraries? If so, the manual describes how you can use a custom primary key column name.

Allan

Sunilgoel

Hi Allan,
I am using Editor PHP . I have done everything written in manual and using custom primary key but Editor allows me to edit one column at a time and after changed Editor moves changed row to end of table.

Regards.
Sunil

allan

Can you show me your PHP code and also give me a link to the page showing the issue so I can help to debug it please.

Regards,
Allan

Sunilgoel

Hi Allan, this is my code

<table id="targettable" class="table table-striped display" cellspacing="0" width="100%">
                <thead>
   <tr class="bg-blue-200">
    <th width=5%>Month</th>
    <th width=5%>Year</th>
    <th class='text-center' width=15%>New Accounts</th>
    <th class='text-center' width=35%>Margin Money</th>
    <th class='text-center' width=20%>Brokerage</th>
    <th class='text-center' width=20%>No of Orders</th>
    <th class='text-center' width=15%>Active Accounts</th>
    <th class='text-center' width=15%>Insurance</th>
    <th class='text-center' width=15%>Mutual Funds</th>
   </tr>
                </thead>
                
                <tbody>
                  
                </tbody>
        </table>

<script type="text/javascript">
var editor;
$( document ).ready(function() {

    editor = new $.fn.dataTable.Editor({ 
        ajax:  "user_targets_update.php",
        table: "#targettable",
        idSrc: "id",
        fields: [ 
        {
            label: 'Month',
            name: 'Month'
        },
        {
            label: 'Year',
            name: 'Year'
        },          
        {
            label: 'No of Accounts',
            name: 'noofaccounts'
        },
        {
            label: 'Margin Money',
            name: 'marginmoney'
        },
        {
            label: 'Brokerage',
            name: 'brokerage'
        },
        {
            label: 'No of Orders',
            name: 'nooforders'
        },
        {
            label: 'Active Accounts',
            name: 'noofactiveaccounts'
        },
        {
            label: 'Insurance',
            name: 'insurance'
        },
        {
            label: 'Mutual Funds',
            name: 'mutual_fund'
        }
       ]
  });       
    
  $('#targettable').on( 'click', 'tbody td:not(:first-child)', function (e) {
      editor.inline( this );
  });
        
  $('#usertargets_modal').on('show.bs.modal', function (e) {
        $msg=$(e.relatedTarget).attr('id');
        $sArray=$msg.split('-');
        $('#target_repid').val($sArray[0]);
        $('#target_repname').val($sArray[1]);
        var url="user_targets_fetchdata.php?repid="+$sArray[0];
        var table=$('#targettable').dataTable({
            "responsive": true,
            "processing": false,
            "searching": false,
            "bLengthChange": false,
            "paging": false,
            "bSort": false,
            "sAjaxSource": url,
            "columnDefs": [
               { "width": "10%", "targets": 0 },
               { "width": "10%", "targets": 1 },
               { "width": "10%", "targets": 2 },
               { "width": "10%", "targets": 3 },
               { "width": "10%", "targets": 4 },
               { "width": "10%", "targets": 5 },
               { "width": "10%", "targets": 6 },
               { "width": "10%", "targets": 7 },
               { "width": "10%", "targets": 8 },
            ],
            "columns": [
                   { "title" :"Month",data: 'month','sClass': 'text-primary' },
                   { "title": "Year",data: 'year','sClass': 'text-primary' },
                   { "title": "New Accounts",data: 'noofaccounts','sClass': 'text-primary text-right',render: $.fn.dataTable.render.number( ',', '.', 0, '' ) },
                   { "title": "Margin Money",data: 'marginmoney','sClass': 'text-primary text-right',render: $.fn.dataTable.render.number( ',', '.', 0, '' ) },
                   { "title": "Brokerage",data: 'brokerage','sClass': 'text-primary text-right',render: $.fn.dataTable.render.number( ',', '.', 0, '' ) },
                   { "title": "No of Orders",data: 'nooforders','sClass': 'text-primary text-right',render: $.fn.dataTable.render.number( ',', '.', 0, '' ) },
                   { "title": "Active Accounts",data: 'noofactiveaccounts','sClass': 'text-primary text-right',render: $.fn.dataTable.render.number( ',', '.', 0, '' ) },
                   { "title": "Insurance",data: 'insurance','sClass': 'text-primary text-right',render: $.fn.dataTable.render.number( ',', '.', 0, '' ) },
                   { "title": "Mutual Funds",data: 'mutual_fund','sClass': 'text-primary text-right',render: $.fn.dataTable.render.number( ',', '.', 0, '' ) }
            ]
      });
  });

//reinitialize error 
  $('#usertargets_modal').on('hidden.bs.modal', function (e) {
      var table=$('#targettable').dataTable();
      table.fnDestroy();
      table.empty();
      
  });   

  
});       
</script>
 ```

```php
Editor::inst( $db, 'crm_targets','id' )
    ->fields(
        Field::inst( 'slno'),
        Field::inst( 'month' ),
        Field::inst( 'year' ),
        Field::inst( 'noofaccounts' ),
        Field::inst( 'marginmoney' ),
        Field::inst( 'brokerage' ),
        Field::inst( 'nooforders'),
        Field::inst( 'noofactiveaccounts'),
        Field::inst( 'insurance'),
        Field::inst( 'mutual_fund')
    )
    ->process( $_POST )
    ->json();

Regards.
Sunil

Sunilgoel

Hi Allan,
I can show you issue via team viewer if you prefer .

Regards.
Sunil

allan

I can show you issue via team viewer if you prefer .

Sure. That would be covered by the support options.

Regarding the code. First of all remove:

idSrc: "id",

That is only needed if you aren't using the provided PHP (or .NET) libraries.

Secondly, if your primary key is called slno why have you set the third parameter in the Editor:inst() constructor to be id?

Allan

Sunilgoel

slno is not my primary key , primary key is id , slno is sequence no for our internal use only.

after remove idSRC , Editor stopped editing .

Regards.
Sunil

Sunilgoel

Hi Allan,
I have purchased basic support option as suggested. Now you can see via team viewer.

Regards.
Sunil

Sunilgoel

Hi Allan,

I want to add below code in user_targets_update.php

$result=mysql_query("select * from crm_targets where account_id='$repid'");
$num=mysql_num_rows($result);
if ($num==0) {
      $result=mysql_query("insert into crm_targets (account_id,recordtype,noofaccounts,marginmoney,brokerage,nooforders,noofactiveaccounts,
                       insurance,mutual_fund,month,year,month_year,slno) 
                       select '$repid',recordtype,noofaccounts,marginmoney,brokerage,nooforders,noofactiveaccounts,insurance,mutual_fund,
                       month,year,month_year,slno 
                       from crm_targets where account_id='000'");
}

if you remember , you have removed fetch data and told me that you can directly use update to fetch data .

Please suggest how can i add above code in update php.

Actually while fetch data of particular account_id if no record exists then dataTables insert records in table before fetch data send for Edit.

Regards.
Sunil

allan

HI Sunil,

Under what circumstances do you want to use the code above? Only when editing, creating? Server-side events are perfect for that sort of thing.

Thanks,
Allan

Sunilgoel

Hi Allan,
when DataTables goes for fetchdata to populate . If No records found in database then insert from crm_targets table where account_id='000'.

Regards.
Sunil

allan

I think I understand - this you basically want to have a template and if there is no data for the current user it should copy in new settings. Is that correct?

If so, what we could do is something like this:

if ( ! isset( $_POST['action'] ) ) {
  // ... run your code
}

Basically all that is doing it checking if there is a action parameter in the POST parameters. If there isn't then DataTables is doing a data fetch request, and that's when you would run your code.

Insert that immediately before the Editor::inst( ... ); code, that that should be all that is needed!

Regards,
Allan

Sunilgoel

Hi Allan,
I am able to insert record from template but i have to make seprate database connection to do this , can i use $db .

Regards.
Sunil

allan

Hi Sunil,

Yes, absolutely you could use the $db parameter. That is a reference to the Database class that is defined in the Editor PHP libraries - it is fully documented here.

For example you might use:

$res = $db->select( 'crm_targets', '*', [ "account_id" => $repid ] )->fetchAll();
if ( count( $res ) === 0 ) {
  $db->sql( "insert into ..." );
}

We need to use the sql method rather than one of the abstraction methods for the insert since you are combining it with a select. You could do a separate select then insert, but this is morefii.

Allan