How best to select a cell from database row column value

Datagaard · December 2021

I want to default a selected cell within a data table based on looking through the underlying data to locate a (row, column) value.
Are there any examples of selecting a specific cell based on data (row, column) value?

colin · December 2021

To be clear, is this setting a cell value on initialisation based on another value in the same row? Or in a different row? Or some other criteria?

Colin

Datagaard · December 2021

Hi Colin.

Neither, my aim is to select the cell in question after it shows, so it is selected by default based on data found in the data set.

By iterate through the underlying data, when the (row, column) data matches a set value, then select that cell.

Hope that makes sense.

colin · December 2021

I think that make sense Could you look at this example, please - I'm selecting all cells that contain 'London' when the table is initialised. Could you look at that, please, and see if it helps. If it's still not working for you, please can you update my example, or link to your page, so that we can see the problem.

Cheers,

Colin

Datagaard · December 2021

Hi Colin,

Nearly... not quite.

Yes that will select every cell that contains 'London', but what if we just want to select the cell which contains 'London' and where row name = Angelica Ramos (which in your eaxmple is the first row)

Regards

colin · December 2021

Ha, we're getting there. You can do something like this - this is just expanding the conditional:

      api.cells().every(function() {
        var idx = this.index();
        if (this.data() == 'London' && api.cell(idx.row, 0).data() == 'Angelica Ramos') {
          this.select();
        }
      })

Colin

Datagaard · December 2021

Hi Colin,

We get this error now, where we didn't before:

Uncaught ReferenceError: api is not defined.

kthorngren · December 2021

Add the statement var api = this.api(); like in Colin's example.

Kevin

Datagaard · December 2021

Thanks, now all I have to do is get the cell to look like it has been selected by the user, which has the highlighted boarder

Image 1 is the cell selected, image 2 is what I would like

Regards

colin · December 2021

Please update my test case to demonstrate the issue, with instructions on how to reproduce the issue,

Colin

Datagaard · December 2021

HTML page

<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        <title>DataTables Editor - terms_codes</title>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/dt-1.10.21/b-1.6.2/b-html5-1.6.2/r-2.2.5/sp-1.1.1/sl-1.3.1/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="css/generator-base.css">
        <link rel="stylesheet" type="text/css" href="css/editor.bootstrap4.min.css">
        <link href="https://nightly.datatables.net/select/css/select.dataTables.css" rel="stylesheet" type="text/css" />

        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.11.0/umd/popper.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/bs4-4.1.1/jq-3.3.1/moment-2.18.1/dt-1.10.21/b-1.6.2/b-html5-1.6.2/r-2.2.5/sp-1.1.1/sl-1.3.1/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/editor.bootstrap4.min.js"></script>
        <script src="https://nightly.datatables.net/select/js/dataTables.select.js"></script> 
        <script type="text/javascript" charset="utf-8" src="js/table.terms_codes.js"></script>
    </head>
    <body class="bootstrap4">
        <div class="container">

            <h1>
                DataTables Editor <span>terms_codes</span>
            </h1>
            
            <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="terms_codes" width="100%">
                <thead>
                    <tr>
                        <th>termscode</th>
                        <th>tdescription</th>
                        <th>duedatecutoff</th>
                        <th>duefixeddom</th>
                        <th>dueinsomanydays</th>
                        <th>duemethodcode</th>
                        <th>termscutoffdom</th>
                        <th>termsday</th>
                        <th>termsfixeddom</th>
                        <th>termspercent</th>
                        <th>tmethodcode</th>
                    </tr>
                </thead>
<tbody>
<tr id="row_1" role="row" class="odd"><td class="sorting_1 dtr-control">1</td><td>7 DAYS</td><td>31</td><td>14</td><td>0</td><td>3</td><td>0</td><td style="display: none;">0</td><td style="display: none;">0</td><td style="display: none;">5.00</td><td style="display: none;">1</td></tr><tr id="row_10" role="row" class="even"><td class="sorting_1 dtr-control">10</td><td>1.5% STRATCO</td><td>31</td><td>31</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">31</td><td style="display: none;">1.50</td><td style="display: none;">2</td></tr><tr id="row_14" role="row" class="odd"><td class="sorting_1 dtr-control">14</td><td>5% 14DYS/30NETT</td><td>31</td><td>30</td><td>0</td><td class="selected">3</td><td>0</td><td style="display: none;">14</td><td style="display: none;">0</td><td style="display: none;">5.00</td><td style="display: none;">1</td></tr><tr id="row_2" role="row" class="even"><td class="sorting_1 dtr-control">2</td><td>21/2 30DAYS</td><td>31</td><td>30</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">30</td><td style="display: none;">2.50</td><td style="display: none;">2</td></tr><tr id="row_3" role="row" class="odd"><td class="sorting_1 dtr-control">3</td><td>3%</td><td>31</td><td>30</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">30</td><td style="display: none;">3.00</td><td style="display: none;">2</td></tr><tr id="row_30" role="row" class="even"><td class="sorting_1 dtr-control">30</td><td>30 DAYS NETT</td><td>31</td><td>30</td><td>0</td><td>3</td><td>0</td><td style="display: none;">0</td><td style="display: none;">0</td><td style="display: none;">0.00</td><td style="display: none;">1</td></tr><tr id="row_5" role="row" class="odd"><td class="sorting_1 dtr-control">5</td><td>21/2 30 DAYS</td><td>31</td><td>31</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">30</td><td style="display: none;">2.50</td><td style="display: none;">2</td></tr><tr id="row_7" role="row" class="even"><td class="sorting_1 dtr-control">7</td><td>STRAIGHT 7 DAYS</td><td>0</td><td>0</td><td>7</td><td>1</td><td>0</td><td style="display: none;">0</td><td style="display: none;">0</td><td style="display: none;">0.00</td><td style="display: none;">1</td></tr><tr id="row_CO" role="row" class="odd"><td class="sorting_1 dtr-control">CO</td><td>2% 30 DAYS</td><td>31</td><td>14</td><td>0</td><td>3</td><td>31</td><td style="display: none;">0</td><td style="display: none;">14</td><td style="display: none;">2.00</td><td style="display: none;">2</td></tr>
</tbody>
            </table>

        </div>
    </body>
</html>

JS File

/*
 * Editor client script for DB table terms_codes
 * Created by http://editor.datatables.net/generator
 */

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
        ajax: 'php/table.terms_codes.php',
        table: '#terms_codes',
        fields: [
            {
                "label": "termscode:",
                "name": "termscode"
            },
            {
                "label": "tdescription:",
                "name": "tdescription"
            },
            {
                "label": "duedatecutoff:",
                "name": "duedatecutoff"
            },
            {
                "label": "duefixeddom:",
                "name": "duefixeddom"
            },
            {
                "label": "dueinsomanydays:",
                "name": "dueinsomanydays"
            },
            {
                "label": "duemethodcode:",
                "name": "duemethodcode"
            },
            {
                "label": "termscutoffdom:",
                "name": "termscutoffdom"
            },
            {
                "label": "termsday:",
                "name": "termsday"
            },
            {
                "label": "termsfixeddom:",
                "name": "termsfixeddom"
            },
            {
                "label": "termspercent:",
                "name": "termspercent"
            },
            {
                "label": "tmethodcode:",
                "name": "tmethodcode"
            }
        ]
    } );

    var table = $('#terms_codes').DataTable({
        ajax: 'php/table.terms_codes.php',
        columns: [
            {
                "data": "termscode"
            },
            {
                "data": "tdescription"
            },
            {
                "data": "duedatecutoff"
            },
            {
                "data": "duefixeddom"
            },
            {
                "data": "dueinsomanydays"
            },
            {
                "data": "duemethodcode"
            },
            {
                "data": "termscutoffdom"
            },
            {
                "data": "termsday"
            },
            {
                "data": "termsfixeddom"
            },
            {
                "data": "termspercent"
            },
            {
                "data": "tmethodcode"
            }
        ],
        select: {
            style: 'single',
            selector: 'td:not(:last-child)' /* no row selection on last column */
            //items: 'cells'
        },
        initComplete: function() {
            var api = this.api();
            this.api().cells().every(function() {
                var idx = this.index();
                if (this.data() == '3' && api.cell(idx.row, 0).data() == '14') {
                    this.select();
                }
                
                //var cellObj = this;
                //var cellRowNo = cellObj[0][0].row;
                //var cellColNo = cellObj[0][0].column;
                //var rowDataObj = table.rows().data()[cellRowNo]; // same as row(0).data()
                //if (rowDataObj.termscode == 'CO' && cellColNo == '4') {
                //  if (this.data() == '0') {
                //      this.select();
                //  }
                //}
            });
        },
        keys: true,
        lengthChange: true,
        autoWidth: true,
        responsive: true,
    });

    //window.setTimeout(function ()
    //{
    //  table.cell( ':eq(0)' ).focus(); // As javascript is synchronus this ensures focus is the last thing done.
    //}, 0);    
    
    
    new $.fn.dataTable.Buttons( table, [
        { extend: "create", editor: editor },
        { extend: "edit",   editor: editor },
        { extend: "remove", editor: editor }
    ] );

    table.buttons().container()
        .appendTo( $('.col-md-6:eq(0)', table.table().container() ) );
} );

}(jQuery));

Hope thats all you require Colin, tried formatting correctly but not so.

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

Datagaard · December 2021

When code runs. the cell is selected, but if you select another cell in another row you get the cell highlighted with a boarder, that is how we are trying to get the cell to appear in the first place.

Datagaard · December 2021

Oh the php page for terms_codes is:

'''
<?php

/*
* Editor server script for DB table terms_codes
* Created by http://editor.datatables.net/generator
*/

// DataTables PHP library and database connection
include( "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;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
$db->sql( "CREATE TABLE IF NOT EXISTS terms_codes (
tcid int(10) NOT NULL auto_increment,
termscode varchar(255),
tdescription varchar(255),
duedatecutoff numeric(9,2),
duefixeddom numeric(9,2),
dueinsomanydays numeric(9,2),
duemethodcode varchar(255),
termscutoffdom numeric(9,2),
termsday numeric(9,2),
termsfixeddom numeric(9,2),
termspercent numeric(9,2),
tmethodcode varchar(255),
PRIMARY KEY( tcid )
);" );

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'terms_codes', 'termscode' )
->fields(
Field::inst( 'termscode' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 3, ValidateOptions::inst()
->message( 'Terms Code length must be between 1 & 3 Characters in length' ) ) ),
Field::inst( 'tdescription' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 15 ) ),
Field::inst( 'duedatecutoff' )
->validator( Validate::minMaxNum( 0, 99 , "", ValidateOptions::inst()
->message( 'The due date cut off must be 2 digits or less' ) ) ),
Field::inst( 'duefixeddom' )
->validator( Validate::minMaxNum( 0, 99 ) ),
Field::inst( 'dueinsomanydays' )
->validator( Validate::minMaxNum( 0, 9999 ) ),
Field::inst( 'duemethodcode' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 1 ) ),
Field::inst( 'termscutoffdom' )
->validator( Validate::minMaxNum( 0, 99 ) ),
Field::inst( 'termsday' )
->validator( Validate::minMaxNum( 0, 9999 ) ),
Field::inst( 'termsfixeddom' )
->validator( Validate::minMaxNum( 0, 99 ) ),
Field::inst( 'termspercent' )
->validator( Validate::minMaxNum( 0, 9999.99 ) ),
Field::inst( 'tmethodcode' )
->validator( Validate::notEmpty() )
->validator( Validate::maxLen( 1 ) )
)
->process( $_POST )
->json();

<?php > ''' ?>

Datagaard · December 2021

Here's the ajax data:

{"data":[{"DT_RowId":"row_1","termscode":"1","tdescription":"7 DAYS","duedatecutoff":"31","duefixeddom":"14","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"0","termsday":"0","termsfixeddom":"0","termspercent":"5.00","tmethodcode":"1"},{"DT_RowId":"row_10","termscode":"10","tdescription":"1.5% STRATCO","duedatecutoff":"31","duefixeddom":"31","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"31","termspercent":"1.50","tmethodcode":"2"},{"DT_RowId":"row_14","termscode":"14","tdescription":"5% 14DYS\/30NETT","duedatecutoff":"31","duefixeddom":"30","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"0","termsday":"14","termsfixeddom":"0","termspercent":"5.00","tmethodcode":"1"},{"DT_RowId":"row_2","termscode":"2","tdescription":"21\/2 30DAYS","duedatecutoff":"31","duefixeddom":"30","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"30","termspercent":"2.50","tmethodcode":"2"},{"DT_RowId":"row_3","termscode":"3","tdescription":"3%","duedatecutoff":"31","duefixeddom":"30","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"30","termspercent":"3.00","tmethodcode":"2"},{"DT_RowId":"row_30","termscode":"30","tdescription":"30 DAYS NETT","duedatecutoff":"31","duefixeddom":"30","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"0","termsday":"0","termsfixeddom":"0","termspercent":"0.00","tmethodcode":"1"},{"DT_RowId":"row_5","termscode":"5","tdescription":"21\/2 30 DAYS","duedatecutoff":"31","duefixeddom":"31","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"30","termspercent":"2.50","tmethodcode":"2"},{"DT_RowId":"row_7","termscode":"7","tdescription":"STRAIGHT 7 DAYS","duedatecutoff":"0","duefixeddom":"0","dueinsomanydays":"7","duemethodcode":"1","termscutoffdom":"0","termsday":"0","termsfixeddom":"0","termspercent":"0.00","tmethodcode":"1"},{"DT_RowId":"row_CO","termscode":"CO","tdescription":"2% 30 DAYS","duedatecutoff":"31","duefixeddom":"14","dueinsomanydays":"0","duemethodcode":"3","termscutoffdom":"31","termsday":"0","termsfixeddom":"14","termspercent":"2.00","tmethodcode":"2"}],"options":[],"files":[],"searchPanes":{"options":[]}}

colin · December 2021

No, we'd need to see the code running, it's easier to debug that way. Please update my test case, or create your own, so we can see the issue you want help with,

Colin

Datagaard · December 2021

Hi Colin,

Merry Christmas, hope you had a wonderful time.

I have updated the test case, but can you correct the links and script src in the html to the appropriate location on the nightly side for my editions. Hoping that will show case the example. It works as is, but I can't get the down arrow, and right arrow keys to select the next row, column... probably due to the incorrect references to the nightly build locations.

Regards

colin · December 2021

Merry Christmas to you too. You'll need to post the link, as the version strings update on the edits.

Colin

Datagaard · December 2021

Hi Colin,

My bad, not quiet used to all this, here is the link example

Regards

kthorngren · December 2021

You are using the KeyTable extension which is highlighting the cell with just the border. Sounds like you want to use the KeyTalbe cell().focus() API instead of the select cell().select() API. Like this:
http://live.datatables.net/xamigesi/5/edit

BTW, I added the KeyTable library to the test case.

Kevin

Datagaard · December 2021

Thanks Kevin,

Spot on.

How best to select a cell from database row column value

How best to select a cell from database row column value

Answers

Howdy, Stranger!

Categories

DataTables

How best to select a cell from database row column value

How best to select a cell from database row column value

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables