change cell color with depends on variable

[trektrak]

Hello,

I need some help on my table report using php..

So this is my coding

<?php

while($row=mysql_fetch_array($result))
{

echo "</td><td>";

echo $row['full_name'];

echo "</td><td>";

echo $row['section'];

echo "</td><td>";

echo $row['time'];

echo "</td><td>";

echo $row['reason'];


echo "</td></tr>";

}

echo "</table>";

?>

so, this is what i need to do .... I need to have changing on cell table color depends on variable.. say for example..

say for example.. if time is more than 08:00:00 the table cell will change to orange... please help me

Tq...

[broj1]

Something like:

<?php

// set styles the way you want
if($time < 8) {
    $tdStyle='background-color:green;';
} else {
    $tdStyle='background-color:red;';
}

while($row=mysql_fetch_array($result))
{    
    echo "</td><td style=\"$tdStyle\">";        
    echo $row['full_name'];        
    echo "</td><td style=\"$tdStyle\">";        
    echo $row['section'];        
    echo "</td><td style=\"$tdStyle\">";        
    echo $row['time'];        
    echo "</td><td style=\"$tdStyle\">";        
    echo $row['reason'];        
    echo "</td></tr>";
}
echo "</table>";  
?>

Please note the escaped double quotes so you can use them directly in a double quoted string.

[EvolutionFallen]

My only additional advice is to use this to set classes for your TDs instead. This way you can change the actual appearance in your stylesheet.

[trektrak]

Thank you guys for the feedback... it works.. Now I'm stuck with the timestamp format.. for my table, I'm using this kind of format '0000-00-00 00:00:00' ..

So, how am i going to do this ? if I need to focus on the time only?

[mchourishi]

<?php
//$time = "08:00:00";
$convert_time = strtotime($time);
if($convert_time > 1383877800)
{
    $tdStyle='background-color:green;';
}
else
{
    $tdStyle='background-color:red;';
}
?>

[diafol]

This is dependent on your DB date format. From what I can gather you have a timestamp or a datetime format in it.

In order to get the 'time' element, you could do this in MySQL...

SELECT field1,field2,TIME(datetimefield) AS timepart FROM table

That way you have the $row['timepart'] as the isolated time field. Or if the time is in timestamp...

SELECT field1,field2,FROM_UNIXTIME(timestampfield, '%H:%i:%s') AS timepart FROM table

Otherwise do the conversions within PHP:

For datetime:

$time = date('H:i:s',strtotime($datetime));

For timestamp:

$time = date('H:i:s',$datestamp);

[trektrak]

I am using a timestamp format within my sql... ermm.. I'm still stuck

[diafol]

OK, so you can use this...

SELECT field1,field2,FROM_UNIXTIME(timestampfield, '%H:%i:%s') AS timepart FROM table

I'll illustrate with mysql_* functions, but consider mysqli_* or PDO...

$r = mysql_query("SELECT field1,field2,FROM_UNIXTIME(timestampfield, '%H:%i:%s') AS timepart FROM table");
$output = '...';
while($d = mysql_fetch_assoc($r)){
    $class = ($d['timepart'] > '08:00:00') ? ' class="redBg"' : '';
    $output .= "<tr><td$class>{$d['field1']}</td></tr>";
}
$output .= '...';
echo $output;

[trektrak]

im so sorry .. i dont get it .. you want me to make another query out of my previous table ?

[EvolutionFallen]

I think diafol is suggesting you update your existing query. In your SELECT query, replace time with FROM_UNIXTIME(time, '%H:%i:%s') AS time. I'm not sure why you would need to change anything else.

[diafol]

The SQL was an example of what you could do - a generic example at that. It just creates a new field called timepart which contains just the time part of your timestamp - it's just a convenient vehicle for delivering that specific piece of data. Alternatively, you can use date() or the DateTime object to extract the time element of the timestamp. Up to you.

[trektrak]

Thanks diafol, i got a NULL... what seems to be the problem yea?

Ok this is my mysql query

$result = mysql_query("SELECT `members`.`full_name`,`members`.`section`,`time_in`.`time`,`time_in`.`reason` FROM members\n"
    . "INNER JOIN `log_time`.`time_in` ON `members`.`code` = `time_in`.`code` ORDER BY `time_in`.`time` DESC");

So .. I need to focus on the time_in.time

[diafol]

OK, give me 10 minutes while I try to replicate...

[diafol]

Here's a sample table...

CREATE TABLE `timestamp2time` (
  `ts_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `timest` INT(11) DEFAULT NULL,
  PRIMARY KEY (`ts_id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Here's the data contained within...

Run this SQL...

SELECT ts_id,timest,TIME(FROM_UNIXTIME(timest)) AS t FROM timestamp2time

So you get...

[trektrak]

for my timestamp field.. what should be the format ? should i put timestamp ? because by default.. this is the way it should be CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAM

[diafol]

I don't know where you're going with this.

The 'timestamp' type for a MySQL, confusingly is this format...

0000-00-00 00:00:00

But I've been using an integer (int) type for the MySQL field holding the unix format timestamp.

So if you show the format that YOU'RE using in your current table - is it

0000-00-00 00:00:00 

or is it just an integer field?

I've got a feeling that you think you need to alter your current table - you don't. This 'time' field is calculated on the fly during a query - it is not permanent. That's now to say that it couldn't be, but just that it doesn't need to be. Hope that's not too confusing.

[trektrak]

Ok done.. Thankd diafol for the hint ..really appreaciate it.. this is what I did.. instead of using .. TIME(FROM_UNIXTIME(timest)) AS t FROM timestamp2time

Im using TIME(TIMESTAMP(timest)) AS t FROM timestamp2time from there I can trim the timestamp to time only .. and I make variable out of it ..

it got confuse when i used FROM_UNIXTIME

Thanks :D

[diafol]

Ok Solved?

[trektrak]

Solved tq!!