Search code examples
javascripthtmlcsscellbackground-color

Color the row of a HTML table from a cell value (from an SQL server)

I am trying to create a code which colors the row/line of a specific table depending on a cell`s value. The data comes from a Microsoft SQL server

For example: if the cell named "files_link" is with a value 0 - color the whole line red (or if it is possible, color another row from that line red)

The idea is if the cell is:

0 - red color

1 - green color

2 - yellow

The idea (half working code)

Sadly I cannot add a working version in the code snippet because I am getting the data from an SQL server.

$(function() {
    $('tr > td:odd').each(function(index) {
        var scale = [['no', 0], ['yes', 1]];
        var score = $(this).text();
        for (var i = 0; i < scale.length; i++) {
            if (score <= scale[i][1]) {
                $(this).addClass(scale[i][0]);
            }
        }
    });
});
table {
    width: 20em;
}

#score {
    width: 50%;
}

#name {
    width: 50%;
}

th {
    border-bottom: 2px solid #000;
    padding: 0.5em 0 0.1em 0;
    font-size: 1.2em;
}

td {
    border-bottom: 2px solid #ccc;
    padding: 0.5em 0 0.1em 0;
}

th:nth-child(even),
td:nth-child(even) {
    text-align: center;
}

.yes {
    background-color: #b6d8b0;
}

.half {
    background-color: #ffc16f;
}

.no {
    background-color: #CC5959;
}
<html>
<style>
td{text-align: center;}
.yes {
    background-color: #b6d8b0;
}

.half {
    background-color: #ffc16f;
}

.no {
    background-color: #CC5959;
}
</style>
<head>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="jo.js"></script>
<title>AV.31.U</title>
</head>

<body>
<table style="width: 100%; height:50%" border="1" cellpadding="3">
<caption>AV.31.U</caption>
<tr>
            <td><div id="current_date"></p>
<script>
date = new Date();
year = date.getFullYear();
month = date.getMonth() + 1;
day = date.getDate();
document.getElementById("current_date").innerHTML = day + "/" + month + "/" + year;
</script></td>
            <td colspan="6">Home</td>
            <td>&nbsp;</td>
        </tr>
<tr>
    <th>Проект
    <th>Машина
    <th>te
    <th>video_link
    <th>te
    <th>files_link
    <th>te
    <th>draw_link
</tr>
<?php
$username = 'censored';
$password = 'censored';
$servername = 'censored';
$database = 'censored';
  ini_set('display_errors', '1');
  error_reporting(E_ALL);
 $db = odbc_connect("Driver={SQL Server};Server=$servername;Database=$database;", $username, $password) or die ("could not connect<br />");

  $stmt = "Select * from machine";

  $result = odbc_exec($db, $stmt);

  if ($result == FALSE) die ("could not execute statement $stmt<br />");

  while (odbc_fetch_row($result)) // while there are rows
  {
     print "<tr>\n";
     print "  <td>" . odbc_result($result, "project_ID") . "\n";
     print "  <td>" . odbc_result($result, "machine_project_ID") . "\n";
     print "  <td>" . odbc_result($result, "video_link") . "\n";
     print "  <td>" . odbc_result($result, "files_link") . "\n";
     print "  <td>" . odbc_result($result, "draw_link") . "\n";
     print "  <td>" . odbc_result($result, 'has_draw') . "\n";
     print "  <td>" . odbc_result($result, "has_video") . "\n";
     print "  <td>" . odbc_result($result, "has_files") . "\n";
     print "</tr>\n";
  }

  odbc_free_result($result);

  odbc_close($db);
?>



</table>
</body>
</html>

Solution

  • First, place data in the <tbody> of the table. Second, dates can be formatted as Strings using toLocaleDateString. This function uses the Intl.DateTimeFormat formatting options.

    Now, you can get the value of the seventh column of each row and set a data attribute i.e. data-has-link. You can declare styles for rows i.e. <td> with a particular data attribute. This is much easier to work with than abusing classes.

    $(function() {
  var $table = $('table');
  $table.find('#current_date').text(getCurrentDate());
  colorRows($table); // Color the rows!
});

function getCurrentDate () {
  return new Date().toLocaleDateString('en-GB', {
    year: 'numeric',
    month: 'numeric',
    day: 'numeric'
  });
}

function colorRows($table) {
  var hasLink;
  $table.find('tbody > tr').each(function(rowIndex) {
    const $row = $(this);
    $row.find('td').each(function(colIndex) {
      const $cell = $(this).removeAttr('data-has-link');
      const cellValue = $cell.text().trim();
      if (isFinite(cellValue)) {
        // Color cell based on individual data
        const hasLink = cellHasLink(parseInt(cellValue, 10));
        if (hasLink !== 'maybe') {
          $cell.attr('data-has-link', hasLink);
        }
      }
    });
    // Color row based on 7th column
    var i = parseInt($row.find('td:nth-child(7)').text(), 10);
    $row.attr('data-has-link', cellHasLink(i));
  });
}

function cellHasLink(value) {
  switch (value) {
    case 0  : return 'no';
    case 1  : return 'yes';
    default : return 'maybe';
  }
}
    table { width: 20em; border-collapse: collapse; }

th {
  border-bottom: 2px solid #000;
  padding: 0.5em 0 0.1em 0;
  font-size: 1.2em;
}

td {
  border-bottom: 2px solid #ccc;
  padding: 0.5em 0 0.1em 0;
}

th:nth-child(n + 2),
td:nth-child(n + 2) {
  text-align: center;
}

[data-has-link="no"]    { background-color: #F77; }
[data-has-link="yes"]   { background-color: #7F7; }
[data-has-link="maybe"] { background-color: #FD7; }

#score, #name {  width: 50%; }
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<table style="width: 100%; height:50%" border="1" cellpadding="3">
  <caption>AV.31.U</caption>
  <thead>
    <tr>
      <td><div id="current_date"></div></td>
      <td colspan="6">Home</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <th>Project</th>
      <th>Machine</th>
      <th>te</th>
      <th>Video Link</th>
      <th>te</th>
      <th>File Link</th>
      <th>te</th>
      <th>Draw Link</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>22005</td>
      <td>22003652</td>
      <td><a href="#">Link</a></td>
      <td><a href="#">Open</a></td>
      <td><a href="#">Open</a></td>
      <td>0</td>
      <td>0</td>
      <td>1</td>
    </tr>
    <tr>
      <td>22012</td>
      <td>22003652</td>
      <td><a href="#">Link</a></td>
      <td><a href="#">Open</a></td>
      <td><a href="#">Open</a></td>
      <td>1</td>
      <td>1</td>
      <td>1</td>
    </tr>
    <tr>
      <td>22012</td>
      <td>22003652</td>
      <td><a href="#">Link</a></td>
      <td><a href="#">Open</a></td>
      <td><a href="#">Open</a></td>
      <td>1</td>
      <td>3</td>
      <td>1</td>
    </tr>
  </tbody>
</table>

    Modifying existing PHP

    Modify your PHP to wrap the table header and body appropriately.

    <table style="width: 100%; height:50%" border="1" cellpadding="3">
  <caption>AV.31.U</caption>
  <thead>
    <tr>
      <td><div id="current_date">
      <script>
      date = new Date();
      year = date.getFullYear();
      month = date.getMonth() + 1;
      day = date.getDate();
      document.getElementById("current_date").innerHTML = day + "/" + month + "/" + year;
      </script></td>
      <td colspan="6">Home</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <th>Проект</th>
      <th>Машина</th>
      <th>te</th>
      <th>video_link</th>
      <th>te</th>
      <th>files_link</th>
      <th>te</th>
      <th>draw_link</th>
    </tr>
  </thead>
  <tbody>
    <?php
    $username = 'censored';
    $password = 'censored';
    $servername = 'censored';
    $database = 'censored';

    ini_set('display_errors', '1');
    error_reporting(E_ALL);

    $db = odbc_connect("Driver={SQL Server};Server=$servername;Database=$database;", $username, $password) or die ("could not connect<br />");

    $stmt = "Select * from machine";
    $result = odbc_exec($db, $stmt);

    if ($result == FALSE) die ("could not execute statement $stmt<br />");

    while (odbc_fetch_row($result)) // while there are rows
    {
      print "<tr>\n";
      print "  <td>" . odbc_result($result, "project_ID") . "\n";
      print "  <td>" . odbc_result($result, "machine_project_ID") . "\n";
      print "  <td>" . odbc_result($result, "video_link") . "\n";
      print "  <td>" . odbc_result($result, "files_link") . "\n";
      print "  <td>" . odbc_result($result, "draw_link") . "\n";
      print "  <td>" . odbc_result($result, 'has_draw') . "\n";
      print "  <td>" . odbc_result($result, "has_video") . "\n";
      print "  <td>" . odbc_result($result, "has_files") . "\n";
      print "</tr>\n";
    }

    odbc_free_result($result);
    odbc_close($db);
    ?>
  </tbody>
</table>