Problem comparing two dates

PHP
1

For every row in the database I have a field that contains a timestamp of when that row has been inserted into the database. The date’s format is “YYYY-MM-DD HH-MM-SS”.

This is because I want the new rows to appear with a different style in the website. For example, I want all the rows inserted in the last 24 hours to appear with a different background color. I wrote this code but it counts every table row as new:


.
.
.
$now = date('Y-m-d G:i:s');
$epoch1 = mktime($now);		
$sql = "SELECT * FROM table";
$result = mysqli_query($conn,$sql);
while ($row = mysqli_fetch_array($result)) {
	$epoch2 = mktime($row['dateofinsert']);
	$diffseconds = $epoch2 - $epoch1;
	if ($diffseconds <= 86400) {
		change background color
	} else {
		don't change background color
	}
.
.
.

The idea is to convert both dates to a UNIX timestamp and then check if their different is less than 24 hours. This would mean that the row is new. Can you spot the mistake?

2

why don’t you let the database do the calculation

SELECT something
     , somethingelse
     , dateofinsert
     , CASE WHEN dateofinsert >= 
               CURRENT_TIMESTAMP - INTERVAL 24 HOUR
            THEN 1
            ELSE 0 END    AS new_in_last_24_hours
SELECT 
  FROM daTable
3

Thank you, I didn’t know that I could let the database do the calculation!

But now, how can I change the class of the table row based on the output of the query?

4

well, i don’t do php, but i assume it would be something like this …

if $row['new_in_last_24_hours'] == 1 {
   change background color
} else {
   don't change background color
}
5

Maybe I’m doing something wrong, but your query doesn’t work, I get an empty table…

6

well, my query had a bunch of imaginary columns

can i see the exact query you ran?

7

I understand that XD

This is my query:


SELECT name
     , date
     , dateofinsert
     , CASE WHEN dateofinsert >= 
               CURRENT_TIMESTAMP - INTERVAL 24 HOUR
            THEN 1
            ELSE 0 END    AS new_in_last_24_hours
SELECT 
  FROM appuntamenti
8

aaaaaaarghhhhhh, my bad :blush:

remove the word SELECT just in front of the word FROM

by the way, with that extra word in there, you should have received a syntax error, which tells me that your error detection in php isn’t working

9

Ok, now it works :slight_smile:

by the way, with that extra word in there, you should have received a syntax error, which tells me that your error detection in php isn’t working

My error detection in PHP is like this:


/*error_reporting(E_ALL);
#var_dump( $_POST );
ini_set('display_errors', 1);*/

and as you can see it is currently commented out :smiley:

10

Rudy’s PHP was very close for someone who doesn’t do PHP. Just need some parenthesis:


if($row['new_in_last_24_hours'] == 1) { 
   echo '<tr class="new">';
} else { 
   echo '<tr>';
}