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 )

m0sfetm0sfet Posts: 13Questions: 0Answers: 0

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

  1. 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
  2. if column 7 value > 0 then color green
    else if column 7 value < 0 then color red
  3. column 8 same as column 7
  4. 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";
                        }
                    }
    ),
.
.
.

Replies

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0

    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>
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    edited 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

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0

    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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Colin

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0

    you're a lifesaver
    tnx agian Colin <3 <3 <3 <3

    it's done with some optimisation :p

    "columnDefs": [
        { "orderSequence": [ "desc", "asc" ], "targets": [ 3, 4, 5, 6 ] },
    
  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0

    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?

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    edited 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

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0

    yes kevin

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Colin

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    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

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0
    edited 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 }
      ]
    } );
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0
    edited 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 :D
    for now

    "columnDefs": [
    { orderable: false, targets: 3 },]
    
  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    edited 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

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    edited 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

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0

    Thanks agian colin
    solved <3

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0

    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();
        } );
    });
    
  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    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

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0

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

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0
    edited 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();
                }
            },
    
  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    edited 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

  • m0sfetm0sfet Posts: 13Questions: 0Answers: 0

    tnx agian kevin
    looking for something like this

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

Sign In or Register to comment.