Row conditional formatting PHP

Question

I need solution for rows conditional formatting (background) based on time now and time difference, before 48 hours, 84 For example: if in cell is today's date and time 13:00 /background color =#ffffff if in cell value is -48 hours different color if in cell value is -84 different color. Here is what I have

<?php

$con = mysqli_connect("localhost", "test", "test");
    if (!$con) {
        die("Database Connection failed".mysqli_error());
    } else {
        $db_select = mysqli_select_db($con, 'test2');
        if (!$db_select) {
            die("Database selection failed".mysqli_error());
        } else {
        }
    }

$records = mysqli_query($con, "SELECT * FROM test2 ORDER by user_Name");
$timenow = new DateTime();

?>

<!DOCTYPE html>
<html>
<body> 

<table width="500" border="2" cellpadding="2" cellspacing='1'>
    <tr bgcolor="#2ECCFA">
        <th>User</th>
        <th>Device</th>
        <th>Last Contact</th>
    </tr>



<?php

if ($records) {
    while ($row = mysqli_fetch_assoc($records)) {
        $calc = $timenow - $row['date'];
        if ($calc > 12000) {
            $rowClass = "oldOrder";
        } else {
            $rowClass = "normalOrder";
        }
        echo '<tr class="'.$rowClass.'"><td>';
        echo $row['user_name'] . '';
        echo '</td><td>';
        echo $row['Device_ID'] . '';
        echo '</td><td>';
        echo $row['Last_Contact'] . '';
        echo '</td></tr>';
    }
}

?>
    <a href=”rowClass.css” rel=”stylesheet” type=”text/css”>
</table>

Field in DB date is additional column that is set as DATETIME I keep getting error

Notice: Object of class DateTime could not be converted to int in bla/bal line 50 that is this part

while ($row = mysqli_fetch_assoc($records)) {
    $calc = $timenow - $row['date'];
    if ($calc > 3) {
        $rowClass = "oldOrder";
    } else {
        $rowClass = "normalOrder";

Funny; you're querying as `ORDER by user_Name` but then later on, you're using `$row['user_name']` with all lower-case letters. That could be an issue here. Check for errors on the query, you're not doing that. — Funk Forty Niner, Jan 06 '19 at 00:29
The connection error function you used is incorrect, you need to use is shown here http://php.net/manual/en/mysqli.connect-error.php — Funk Forty Niner, Jan 06 '19 at 00:32
Possible duplicate of [How to calculate the difference between two dates using PHP?](https://stackoverflow.com/questions/676824/how-to-calculate-the-difference-between-two-dates-using-php) — miken32, Jan 06 '19 at 04:00

Ass3mbler · Accepted Answer · 2019-01-06T00:59:32.297

2

You can convert all the time information in the Unix Time format, using the time() function in PHP and the UNIX_TIME() function in MySQL and work this way:

<?php

$con = mysqli_connect("localhost","test","test" 'test2');

if ($con->connect_error) {
    die('Connect Error: ' . $con->connect_error);
}

$records = mysqli_query($con,"SELECT *, UNIX_TIMESTAMP(date) AS u_date FROM test2 ORDER by user_Name");
$timenow = time();

?>

<!DOCTYPE html>
<html>
<body> 

<table width="500" border="2" cellpadding="2" cellspacing='1'>
    <tr bgcolor="#2ECCFA">
        <th>User</th>
        <th>Device</th>
        <th>Last Contact</th>
    </tr>

<?php

if ($records) {
    while ($row = mysqli_fetch_assoc($records)) {
        $calc = $timenow - $row['u_date'];
        if ($calc > 12000) {
            $rowClass = "oldOrder";
        } else {
            $rowClass = "normalOrder";
        }
        echo '<tr class="'.$rowClass.'"><td>';
        echo $row['user_name'] . '';
        echo '</td><td>';
        echo $row['Device_ID'] . '';
        echo '</td><td>';
        echo $row['Last_Contact'] . '';
        echo '</td></tr>';
    }
}

?>
    <a href="rowClass.css" rel="stylesheet" type="text/css">
</table>

edited Jan 06 '19 at 00:59

answered Jan 06 '19 at 00:22

Ass3mbler

3,855
2
20
18

1

`mysqli_error()` that function requires a database connection. – Funk Forty Niner Jan 06 '19 at 00:30
The connection error function you used is incorrect, as did the OP. – Funk Forty Niner Jan 06 '19 at 00:31
Also don't copy the whole thing over to your answer. – Dharman Jan 06 '19 at 00:31
@Funk Forty Niner thank you for pointing it out. I was focusing on the date problem, I'll correct the answer – Ass3mbler Jan 06 '19 at 00:33
1

`” ”` < I just spotted those now at the end for their code; the curly quotes should be regular as `"`. This whole thing is growing bigger; bit of a mess on the OP's part to start with. – Funk Forty Niner Jan 06 '19 at 00:58

score -1 · Answer 2 · answered Jan 06 '19 at 00:36

You need to compare apples to apples. If your MySQL date field is of type date/datetime, you can just pass it as argument to DateTime and compare the objects:

$dateToCompare = new \DateTime("-3 days"); // 3 days ago
while ($row = mysqli_fetch_assoc($records)) {
    if ($dateToCompare > (new \DateTime($row['date']))) {
        $rowClass = "oldOrder";
    } else {
        $rowClass = "normalOrder";

Row conditional formatting PHP

2 Answers2