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...

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.

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.

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?

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

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);

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

Member Avatar

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;

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

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.

Member Avatar

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.

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

Member Avatar

diafol

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

Member Avatar

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...

b996aeeed289b6bb831b1000dc474b77

Run this SQL...

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

So you get...

04c9914fa8e64a86c5b1e0c231cf54fc

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

Member Avatar

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.

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

Member Avatar

diafol

Ok Solved?