How to Change cell color based on value of another cell (server side proccessing )

m0sfet · August 2021

Hi
I'm new to coding and I'm trying to work out some conditional formatting for my table based on these criteria:

if column 1 == "buy" then column 0 background-color : green
else if column 1 == "sell" then column 0 background-color : Red
also i like to hide column 1 too
if column 7 value > 0 then color green
else if column 7 value < 0 then color red
column 8 same as column 7
if column 9 value >= 1 then color green
else if column 9 value < 1 then color red

Best regards:

<!doctype html>
<html>
<head>
<title>Hot Money</title>
<link rel="icon" href="img/favico.png" type="image/png" sizes="16x16">
<link rel="stylesheet" href="DataTables/styles.css">
<meta charset="utf-8">
<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="DataTables/datatables.min.css">

<!-- jQuery library -->
<script src="js/jquery.min.js"></script>

<!-- DataTables JS library -->
<script type="text/javascript" src="DataTables/datatables.min.js"></script>

</head>
<body>
<div class="container">
    <table id="HMtable" class="display" style="width:100%">
        <thead>
            <tr class=xl19130776>
                <th>name</th>
                <th>buy<br>sell</th>
                <th>total<br>value</th>
                <th>count</th>
                <th>value</th>
                <th>alarm</th>
                <th>last<br>price</th>
                <th>price<br>change%</th>
                <th>pcp</th>
                <th>power</th>
                <th>time</th>
            </tr>
        </thead>
    </table>
</div>
</body>
</html>

<script>
$(document).ready(function(){
  $('#HMtable').DataTable({
    "order": [[ 10, "desc" ]],
    "processing": true,
    "serverSide": true,
    "ajax": "getData.php"
  });
  setInterval(function(){$('#HMtable').DataTable().ajax.reload(null, false);}, 1000);
});
</script>

<style>
.container{padding: 20px;}

.xl19130776{
    padding:0px;
    mso-ignore:padding;
    color:white;
    font-size:10.0pt;
    font-weight:700;
    font-style:normal;
    text-decoration:none;
    font-family:Calibri, sans-serif;
    mso-generic-font-family:auto;
    mso-font-charset:178;
    mso-number-format:General;
    text-align:center;
    vertical-align:middle;
    background:#404040;
    mso-pattern:black none;
    white-space:normal;}
    
th, td {
  text-align: center;
  direction: ltr;
  padding: 16px;
}

tbody>tr>:nth-child(3){
  background-image:url('img/logo1.png');
  background-size:contain;
  background-repeat: no-repeat;
  background-position: left;
}

tbody>tr>:nth-child(4){
  background-image:url('img/logo2.png');
  background-size:contain;
  background-repeat: no-repeat;
  background-position: center;
}

tbody>tr>:nth-child(5){
  background-image:url('img/logo3.png');
  background-size:contain;
  background-repeat: no-repeat;
  background-position: right;
}

</style>

getData.php return buy for 1
and sell for 0

$columns = array(
    array( 'db' => 'name', 'dt' => 0 ),
    array(
        'db'        => 'typehm',
        'dt'        => 1,
        'formatter' => function( $d, $row ) {
                        if($d == 1){
                            return "buy";
                        }else{
                            return "sell";
                        }
                    }
    ),
.
.
.

allan · August 2021

If it is just cell based colours, then use columns.createdCell to add a class based on the condition. If you need to consider the whole row, use createdRow.

Allan

m0sfet · August 2021

tnx allan

your solution seems much better

these is how i did it before your post

<script>
$(document).ready(function(){
  $('#HMtable').DataTable({
    "order": [[ 10, "desc" ]],
    "processing": true,
    "serverSide": true,
    "ajax": "getData.php",
    "columnDefs": [{
            "render": function ( data, type, row, meta ){
                if (row[9] == "buy"){
                return '<span style="color:' + 'green' + '">' + row[0] + '</span>';
                }
                else if (row[9] == "sell"){
                return '<span style="color:' + 'red' + '">' + row[0] + '</span>';
                }
            },
                "targets": 0
            },
            { "visible": true,  "targets": [ 9 ] }
        ],
    columns: [
        {
                data: '0'
            },
        {
                data: '1'
            },
        {
                data: '2'
            },
        {
                data: '3',
                render: function ( data, type, row ) {
                let color = 'black';
                if (data.replace(/[\$,]/g, '') * 1 > 1000) {
                    if (row[9] == "buy"){
                    color = 'green';
                    }
                    else if (row[9] == "sell"){
                    color = 'red';
                    }
                }
                return '<span style="color:' + color + '">' + data + '</span>';
            }
            },
        {
                data: '4'
            },
        {
                data: '5'
            },
        {
                data: '6',
                render: function ( data, type, row ) {
                let color = 'black';
                if (data > 0) {
                    color = 'green';
                }
                else if (data < 0) {
                    color = 'red';
                }
                return '<span style="color:' + color + '">' + data + '</span>';
            }
            },
        {
                data: '7',
                render: function ( data, type, row ) {
                let color = 'black';
                if (data > 0) {
                    color = 'green';
                }
                else if (data < 0) {
                    color = 'red';
                }
                return '<span style="color:' + color + '">' + data + '</span>';
            }
            },
        {
                data: '8',
                render: function ( data, type, row ) {
                if (data >= 1) {
                    color = 'green';
                }
                else if (data < 1) {
                    color = 'red';
                }
                return '<span style="color:' + color + '">' + data + '</span>';
            }
            },
        {
                data: '9'
            },
        {
                data: '10'
            }
    ]
  });
  setInterval(function(){$('#HMtable').DataTable().ajax.reload(null, false);}, 1000);
});
</script>

after

<script>
$(document).ready(function(){
  $('#HMtable').DataTable({
    "order": [[ 10, "desc" ]],
    "processing": true,
    "serverSide": true,
    "ajax": "getData.php",
    "columnDefs": [ 
    {
        "targets": [ 9 ],
        "visible": false
    },
    {
    "targets": 0,
    "createdCell": function (td, cellData, rowData, row, col) {
      if ( rowData[9] == 'buy' ) {
        $(td).css('background-color', 'green')
      }
      else if ( rowData[9] == 'sell' ) {
        $(td).css('background-color', 'red')
      }
    }
    },
    {
    "targets": 1,
    "createdCell": function (td, cellData, rowData, row, col) {
        if ( cellData.replace(/[\$,]/g, '') * 1 > 1000 ) {
            if ( rowData[9] == 'buy' ) {
                $(td).css('color', 'green')
            }
            else if ( rowData[9] == 'sell' ) {
                $(td).css('color', 'red')
            }
        }
    }
    },
    {
    "targets": 3,
    "createdCell": function (td, cellData, rowData, row, col) {
        if ( cellData.replace(/[\$,]/g, '') * 1 > 1000 ) {
            if ( rowData[9] == 'buy' ) {
                $(td).css('color', 'green')
            }
            else if ( rowData[9] == 'sell' ) {
                $(td).css('color', 'red')
            }
        }
    }
    },
    {
    "targets": 6,
    "createdCell": function (td, cellData, rowData, row, col) {
      if ( cellData > 0 ) {
        $(td).css('color', 'green')
      }
      else if ( cellData < 0 ) {
        $(td).css('color', 'red')
      }
    }
    },
    {
    "targets": 7,
    "createdCell": function (td, cellData, rowData, row, col) {
      if ( cellData > 0 ) {
        $(td).css('color', 'green')
      }
      else if ( cellData < 0 ) {
        $(td).css('color', 'red')
      }
    }
    },
    {
    "targets": 8,
    "createdCell": function (td, cellData, rowData, row, col) {
      if ( cellData >= 1 ) {
        $(td).css('color', 'green')
      }
      else if ( cellData < 1 ) {
        $(td).css('color', 'red')
      }
    }
    }
    ]
  });
  setInterval(function(){$('#HMtable').DataTable().ajax.reload(null, false);}, 1000);
});
</script>

colin · September 2021

Another optimisation, is to join the common codes together with multiple values in columns.targets, something like:

{
    "targets": [6, 7],
    "createdCell": function (td, cellData, rowData, row, col) {
      if ( cellData > 0 ) {
        $(td).css('color', 'green')
      }
      else if ( cellData < 0 ) {
        $(td).css('color', 'red')
      }
    }
},

Colin

m0sfet · September 2021

tnx Colin

is there a way to chande defual first sorting from ASC to DESC?
not defualt sorting like "order": [[ 0, 'desc' ]],

i mean when click on column's header, first sort by DESC

colin · September 2021

Yep, gotcha, you can use columns.orderSequence for that, you'll need to apply it to all the columns.

Colin

m0sfet · September 2021

you're a lifesaver
tnx agian Colin

it's done with some optimisation

"columnDefs": [
    { "orderSequence": [ "desc", "asc" ], "targets": [ 3, 4, 5, 6 ] },

m0sfet · September 2021

with python we can order string (numbers that saved as VARCHAR) as number by adding 0 to column

select col from table order by col + 0

do we have these option here too?

kthorngren · September 2021

You have "serverSide": true, set. Your server script is responsible for handling the sorting, searching and paging functions. Do you need server side processing enabled? Is your server script built for the server side processing protocol?

Kevin

m0sfet · September 2021

yes kevin

colin · September 2021

If you want to treat a numeric column as a string, you can set columns.type,

Colin

kthorngren · September 2021

Are you using Datatables supplied server side processing scripts or your own?

Sounds like you are asking for help with creating a SQL statement like select col from table order by col + 0 in your server side script, correct?

Kevin

m0sfet · September 2021

no i'm using server side processing scripts

i have a column with large numbers that stored as varcher(255) and it can't be stored as int
i like to sort it as number not string

also no chance with columns.type option

$('#example').dataTable( {
  "columnDefs": [
    { "type": "num-fmt", "targets": 3 }
  ]
} );

colin · September 2021

You shouldn't need to do that, DataTables should still recognise it as numbers. It would help if we could see this. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

m0sfet · September 2021

tnx colin,

you help me a lot and i appreciate you

DataTables dosen't recognise it as numbers beacuace it saved as varchar(255) on sql

these is my solution
for now

"columnDefs": [
{ orderable: false, targets: 3 },]

kthorngren · September 2021

If you are using one of the Datatatbles supplied server side processing scripts please post it so the developers can help update it to sort that column as numeric. If you don't want to post it here you can PM @allan or @colin directly with the script.

If the SSP script is created by a third party then please work with that developer for help with updating the SQL queries to sort and fetch the data.

Kevin

colin · September 2021

Thanks for the link (private mail), but as Kevin explained, you've enabled serverSide, so the sorting would be performed by that server-side script, not the client - to resolve it, we would need to see that script.

That said, your table only has 362 records. You only need serverSide if your table has in excess of 10-20k records. Because of that, just remove the serverSide, your performance will improve and I suspect your ordering will be correct.

Colin

m0sfet · September 2021

Thanks agian colin
solved

m0sfet · September 2021

hi
how can i add Custom filtering - range search to my table

column 7 data:
20210101
20210105
20210115
20210122
20210203
20210208
20210211
20210219
20210220
20210228
...

$.fn.dataTable.ext.search.push(
    function( settings, data, dataIndex ) {
        var min = parseInt( $('#min').val(), 10 );
        var max = parseInt( $('#max').val(), 10 );
        var date = parseFloat( data[7] ) || 0; // use data for the date column
 
        if ( ( isNaN( min ) && isNaN( max ) ) ||
             ( isNaN( min ) && date <= max ) ||
             ( min <= date   && isNaN( max ) ) ||
             ( min <= date   && date <= max ) )
        {
            return true;
        }
        return false;
    }
);
$(document).ready(function(){
  $('#SHtable').DataTable({
    "order": [[ 7, "desc" ],[ 0, "asc" ],[ 6, "desc" ]],
    "pdateLength": 50,
    "processing": false,
    "serverSide": true,
    "ajax": "getData.php",
    "columnDefs": [
    { orderable: true, targets: 3 },
    { "orderSequence": [ "desc", "asc" ], "targets": [ 3, 4, 5, 6, 7 ] },
    {
        "targets": [ 2 ],
        "visible": false
    },
    {
    "targets": 1,
    "createdCell": function (td, cellData, rowData, row, col) {
      if ( rowData[2] == 'io+' ) {
        $(td).css('background-color', 'green')
      }
      else if ( rowData[2] == 'io-' ) {
        $(td).css('background-color', 'red')
      }
    }
    },
    {
    "targets": [ 3, 5, 6 ],
    "createdCell": function (td, cellData, rowData, row, col) {
      if ( cellData.replace(/[\$,]/g, '') > 0 ) {
        $(td).css('color', 'green')
      }
      else if ( cellData.replace(/[\$,]/g, '') < 0 ) {
        $(td).css('color', 'red')
      }
    }
    }
    ]
  });
  var table = $('#SHtable').DataTable();
  $('#min, #max').keyup( function() {
        table.draw();
    } );
});

kthorngren · September 2021

The search plugins work with client side processing only. Your server side processing script performs all searching. You can pass the inputs to the server using ajax.data. See this example. In the event handler for your input you can just call draw() which will send an ajax request to the server script passing the parameters of the inputs. In your server script you will need to get the parameters and use them as appropriate in your database query.

Kevin

m0sfet · September 2021

tnx Kevin
i can't underestand it
can you help me more

m0sfet · September 2021

is these right?

$(document).ready(function(){
  $('#SHtable').DataTable({
    "order": [[ 7, "desc" ],[ 0, "asc" ],[ 6, "desc" ]],
    "pdateLength": 50,
    "processing": false,
    "serverSide": true,
    "ajax": {
            "url": "getData.php",
            "data": function ( d ) {
                d.myKey = "myValue";
                d.min = $('#max').val();
                d.max = $('#max').val();
            }
        },

kthorngren · September 2021

You don't need the d.myKey = "myValue"; but it looks right. Use the browser's network inspector tool to see the XHR request. You should see all the server side parameters along with the min and max parameters.

Next step is to retrieve the min and max parameters in your server side script. Then use those parameters for your query data ranges. How to do that depends on what you are using server side.

Kevin

m0sfet · September 2021

tnx agian kevin
looking for something like this

but more simple,without filter button and gender filter
also i need persian calendar

How to Change cell color based on value of another cell (server side proccessing )

How to Change cell color based on value of another cell (server side proccessing )

Replies

Howdy, Stranger!

Categories

DataTables

How to Change cell color based on value of another cell (server side proccessing )

How to Change cell color based on value of another cell (server side proccessing )

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables