phpmyadmin Question posted in
The official documentation can be found here.

Javascript coloring rows based on MySQL date values and today – Phpmyadmin

I’m showing MySQL table results on my page and I’m trying to highlight the rows where the Next review date is earlier than today’s date, these rows should be colored red (later I would want another coloring as well, today+10 days should be in orange). I know that I would need to use Javascript for that, however, I have tried 10+ solutions but none of them are working, could you help me? The Next review date is in date format in phpmyadmin (YYYY-MM-DD).

Here is what I have tried currently (the id of my table is table):

var table = document.getElementById("table");
      for(var i = 0; i < table.rows.length; i ++) {
        var status = new Date(table.rows[i].cells[4].innerHTML).getTime();
        var now = new Date().getTime();
        console.log(now>status)
        if (now>status) { 
            table.rows[i].style.background = "red";
        } 
        };

If you would need more code or explanation please indicate it and I will edit the post. Thank you very much for your help!

This is how the table looks like now (coloring was never successful):

if ($_SESSION['user_type'] == 'admin') {
    echo '<div class="table" id="table"> <table>
        <tr>
        <th>
            <a href="?orderBy=name">Asset name</a>
        </th>
        <th>
            <a href="?orderBy=classification">Classification</a>
        </th>   
        <th>
            <a href="?orderBy=tag">Tag</a>
        </th>
        <th>
            <a href="?orderBy=department">Department</a>
        </th>   
        <th>
            <a href="?orderBy=nextreview">Next review date</a>
        </th>   
        <th>
            <a href="?orderBy=responsible">Responsible</a>
        </th>   
        <th>Update</th>
        </tr>';


    $sql = "SELECT * 
    FROM `assets`
    ORDER BY id";

    if (isset($_GET['orderBy'])) {
        $orderby = $_GET['orderBy'];

        if ($orderby == 'name')
        {
            $sql = "SELECT * FROM `assets`ORDER BY Asset_name";
        }
        elseif ($orderby == 'classification')
        {
            $sql = "SELECT * FROM `assets`ORDER BY Classification";
        }
        elseif ($orderby == 'tag')
        {
            $sql = "SELECT * FROM `assets`ORDER BY Tag";
        }
        elseif($orderby == 'department')
        {
            $sql = "SELECT * FROM `assets`ORDER BY Department";
        }
        elseif($orderby == 'nextreview')
        {
            $sql = "SELECT * FROM `assets` ORDER BY Review_date asc";
        }
        elseif($orderby == 'responsible')
        {
            $sql = "SELECT * FROM `assets`ORDER BY Responsible";
        }
    }
    if (!($result=mysqli_query($conn, $sql)))
    { die("Could not show the required data" . mysqli_error($conn));}
    elseif (mysqli_num_rows($result) > 0) {

        while($result2=mysqli_fetch_array($result))
        {echo '<tr>
        <td>'.$result2["Asset_name"].'</td>
        <td>'.$result2["Classification"].'</td>
        <td>'.$result2["Tag"].'</td>
        <td>'.$result2["Department"].'</td>
        <td class="status">'.$result2["Review_date"].'</td>
        <td>'.$result2["Responsible"].'</td>
        <td><a href="editassets.php?id='.$result2['id'].'">Edit</a> | <a href="deleteassets.php?id='.$result2['id'].'" onclick="return confirm('Do you want to delete this asset?');">Delete</a></td>
        </tr>';
        }
        echo '</table></div>';
        }

Current look:

Tags:

2

Answers


  1. 0
    • The table should have the ID of table, not some wrapper – please wrap the THs in a THEAD and the rest of the table in TBODY
    • I use a class instead of background colour
    • I use querySelectorAll to get the cells directly
    • I use string comparison. MUCH faster than date comparison

    Scroll down for a version that is compatible with older IEs
    Scroll further down for a sortable table

    // setup
const pad = (num) => ("0" + num).slice(-2);
const now = new Date();
const nowString = now.getFullYear() + "-" + pad(now.getMonth() + 1) + "-" + pad(now.getDate());

// loop
// remove closest("tr"). to just highlight the cell

[...document.querySelectorAll("table tr td:nth-child(5)")].forEach(cell =>
  cell.closest("tr").classList.toggle("red", nowString > cell.textContent.trim())
);
    .red {
  background-color: red
}

table {
  border-collapse: collapse;
}

td {
  border: none;
}

tr  th, td {
  border: 1px solid black;
  padding: 4px;
}
    <table id="table">
  <thead>
    <tr>
      <th><a href="?orderBy=name">Asset name</a></th>
      <th><a href="?orderBy=classification">Classification</a></th>
      <th><a href="?orderBy=tag">Tag</a></th>
      <th><a href="?orderBy=department">Department</a></th>
      <th><a href="?orderBy=nextreview">Next review date</a></th>
      <th><a href="?orderBy=responsible">Responsible</a></th>
      <th>Update</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2019-07-08</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2019-07-09</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2019-07-10</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2019-07-11</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2021-07-10</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2022-07-11</td>
      <td></td>
      <td></td>
    </tr>
  </tbody>
</table>

    ES<5 version

    // setup
var pad = (num) => ("0" + num).slice(-2);
var now = new Date();
var nowString = now.getFullYear() + "-" + pad(now.getMonth() + 1) + "-" + pad(now.getDate());

// loop

var cells = document.querySelectorAll("table tr td:nth-child(5)")

for (var i=0;i<cells.length;i++) {
  var row = cells[i].parentNode; // use cells[i].classList to just highlight the cell
  row.classList.toggle("red", nowString > cells[i].textContent.trim())
}
    .red {
  background-color: red
}

table {
  border-collapse: collapse;
}

td {
  border: none;
}

tr  th, td {
  border: 1px solid black;
  padding: 4px;
}
    <table id="table">
  <thead>
    <tr>
      <th><a href="?orderBy=name">Asset name</a></th>
      <th><a href="?orderBy=classification">Classification</a></th>
      <th><a href="?orderBy=tag">Tag</a></th>
      <th><a href="?orderBy=department">Department</a></th>
      <th><a href="?orderBy=nextreview">Next review date</a></th>
      <th><a href="?orderBy=responsible">Responsible</a></th>
      <th>Update</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2019-07-08</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2019-07-09</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2019-07-10</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2019-07-11</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2021-07-10</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td></td>
      <td></td>
      <td></td>
      <td></td>
      <td>2022-07-11</td>
      <td></td>
      <td></td>
    </tr>
  </tbody>
</table>

    Sortable

    Sorting HTML table with JavaScript

    Note the numeric cells will be sorted alphabetically here, more code needed to handle the 90 below

    // setup
const pad = (num) => ("0" + num).slice(-2);
const now = new Date();
const nowString = now.getFullYear() + "-" + pad(now.getMonth() + 1) + "-" + pad(now.getDate());

// loop
// remove closest("tr"). to just highlight the cell

[...document.querySelectorAll("table tr td:nth-child(5)")].forEach(cell =>
  cell.closest("tr").classList.toggle("red", nowString > cell.textContent.trim())
);

function sortTable(table, col, reverse) {
    var tb = table.tBodies[0], // use `<tbody>` to ignore `<thead>` and `<tfoot>` rows
        tr = Array.prototype.slice.call(tb.rows, 0), // put rows into array
        i;
    reverse = -((+reverse) || -1);
    tr = tr.sort(function (a, b) { // sort rows
        return reverse // `-1 *` if want opposite order
            * (a.cells[col].textContent.trim() // using `.textContent.trim()` for test
                .localeCompare(b.cells[col].textContent.trim())
               );
    });
    for(i = 0; i < tr.length; ++i) tb.appendChild(tr[i]); // append each row in order
}
function makeSortable(table) {
    var th = table.tHead, i;
    th && (th = th.rows[0]) && (th = th.cells);
    if (th) i = th.length;
    else return; // if no `<thead>` then do nothing
    while (--i >= 0) (function (i) {
        var dir = 1;
        th[i].addEventListener('click', function () {sortTable(table, i, (dir = 1 - dir))});
    }(i));
}
makeSortable(document.getElementById("table"))
    .red {
  background-color: red
}

table {
  border-collapse: collapse;
}

td {
  border: none;
}

tr  th, td {
  border: 1px solid black;
  padding: 4px;
}
    <table id="table">
  <thead>
    <tr>
      <th>Asset name</th>
      <th>Classification</th>
      <th>Tag</th>
      <th>Department</th>
      <th>Next review date</th>
      <th>Responsible</th>
      <th>Update</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>B</td>
      <td>200</td>
      <td></td>
      <td></td>
      <td>2019-07-08</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td>A</td>
      <td>100</td>
      <td></td>
      <td></td>
      <td>2019-07-10</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td>Q</td>
      <td>111</td>
      <td></td>
      <td></td>
      <td>2019-07-11</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td>Z</td>
      <td>302</td>
      <td></td>
      <td></td>
      <td>2021-07-10</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td>V</td>
      <td>90</td>
      <td></td>
      <td></td>
      <td>2022-07-11</td>
      <td></td>
      <td></td>
    </tr>
    <tr>
      <td>N</td>
      <td></td>
      <td></td>
      <td></td>
      <td>2019-07-09</td>
      <td></td>
      <td></td>
    </tr>
    
  </tbody>
</table>
    Login or Signup to reply.
  2. 0

    As others start answering, I will post what I discussed with you in comments:

    Your HTML has:

    <div class="table" id="table"> <table>

    Your JavaScript however defines:

    var table = document.getElementById("table");

    … and continues to treat that table as the table element, but it isn’t. In your HTML you can see it is a <div> element, not a <table> element.

    So align both. Either change your HTML, so that the <table> has the id="table":

    <div class="table"> <table  id="table">

    … or change your JavaScript so that it takes the child element of the <div> with that id:

    var table = document.getElementById("table").firstElementChild;

    NB: Don’t make both changes: choose the one that best suits you.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search