[SOLVED] display colored text from select statements in a html table. code sample include

[Calvus]

Hiya's,

Nice little community you have got going here  Im just new to php but have coded in many other languages in the past.

Im trying to call some data from a table in my database which is working fine and displaying it in a table.

My problem is though Im trying to color some dates depending if tabledates = today or < today but for some reason some dates seem to slip though? This is the code Im using:

 
// issue the query
$sql = "SELECT inv_no, name, vehicle, DATE_FORMAT(arrival,'%a %d/%m'), DATE_FORMAT(deliver,'%a %d/%m'), status, staff, pay, notes, xs, parts
         FROM Jobs 
	 WHERE status <> 'Complete'
	 AND location = 'Coomera'
	 ORDER BY deliver asc, arrival asc";
$q = $db->query($sql);
if (DB::iserror($q)) {
   die($q->getMessage(  ));
}

// generate the table
while ($q->fetchInto($row)) {
?>
<tr><td><?= $row[0] ?></td>
    <td><?= $row[1] ?></td>
    <td><?= $row[2] ?></td>
<td><?
	 $todaytxt = "<b><font color='FF0000'>$row[3]</font></b>";
	 $comingtxt = "<b><font color='66FF66'>$row[3]</font></b>";
	 $todate = date("D d/m");
	 if ($row[3] == $todate) {
		echo $todaytxt;
	 }elseif(date($row[3]) < date($todate)) {
		echo $comingtxt;
	 }else{
		echo $row[3];
	 }
				?></td>
    <td><?
	$dtodaytxt = "<b><font color='FF0000'>$row[4]</font></b>";
	$dcomingtxt = "<b><font color='66FF66'>$row[4]</font></b>";
	if ($row[4] == date('D d/m')){
		echo $dtodaytxt;
	 }elseif($row[4] < date('D d/m')) {
		echo $dcomingtxt;
	 }else{
		echo $row[4] ;
	 } ?></td>
    <td><?= $row[5] ?></td>
    <td><?= $row[6] ?></td>
    <td><?= $row[7] ?></td>
    <td><?= $row[8] ?></td>
    <td><?= $row[9] ?></td>
    <td><?= $row[10] ?></td>
    <td><?= $row[11] ?></td>
</tr>
<?php
}
?>

Am I best using some other if statement comparisons, or maybe coloring the text in the display(echo) line which I havnt worked out how to do yet?

 

Any help appreciate as this works sometimes but not 100%, not really acceptable as a solution to my problem.

 

TIA

Calvus

 

PS: check out my site if interested www.scalesandfins.com its a free community for fishy fans.

[Barand]

D d/m isn't a great format for date comparisons. Select the date in that format for display but also select the raw date (yyyy-mm-dd) to use for the comparisons.

[Calvus]

Hi Barand,

 

Thanks for your speedy reply 

 

D d/m isn't a great format for date comparisons. Select the date in that format for display but also select the raw date (yyyy-mm-dd) to use for the comparisons.

Ive sort of work that much out  ???

 

Ive compared the dates in raw format but then cant color the text as required on the display(echo) line whatever the correct term is?

This code doesn't work, of course you might say:

 // issue the query
$sql = "SELECT inv_no, name, vehicle, arrival, deliver, status, staff, pay, notes, xs, parts
         FROM Jobs 
	 WHERE status <> 'Complete'
	 AND location = 'Coomera'
	 ORDER BY deliver asc, arrival asc";
	 //group by arrival - (IF(dayofweek(arrival)-2 < 0,6,dayofweek(arrival)-2))
$q = $db->query($sql);
if (DB::iserror($q)) {
   die($q->getMessage(  ));
}

// generate the table
while ($q->fetchInto($row)) {
?>
<tr><td><?= $row[0] ?></td>
    <td><?= $row[1] ?></td>
    <td><?= $row[2] ?></td>
<td><?
	 $todate = date("Y-m-d");
	 if ($row[3] == $todate) {
		echo '<b><font color='FF0000'>$row[3]</font></b>';
	 }elseif(date($row[3]) < date($todate)) {
		echo '<b><font color='66FF66'>$row[3]</font></b>';
	 }else{
		echo $row[3];
	 }
				?></td>

Im a total newbie and a bit lost how to go about this? I can get both to work but not together, lol.

 

Thanks!

 

[Barand]

Is your date column DATE or DATETIME?

 

If there are time elements in there you won't match with "==" unless you select just the date part.

 

SELECT ..., DATE(arrival) as arrival, ...

 

and your < test needs to be

 

}elseif ($row[3] < $todate) {

[Calvus]

Hiya,

 

The if statement seems to work like that I can get it working with america date formate 2008-08-22 with colors but cant seem to convert the format?

Testing below with no luck $varrival seems to loose $row[3] however I pass it, no luck like this either?

$varrival = date('D d/m',row[3]);

	 $testdate = $row[3];
	 $varrival = date('D d/m',$testdate);
	 $todaytxt = "<b><font color='FF0000'>$varrival</font></b>";
	 $comingtxt = "<b><font color='66FF66'>$varrival</font></b>";
	 $todate = date("Y-m-d");
	 if ($row[3] == $todate) {
		echo $todaytxt ;
	 }elseif($row[3] < $todate) {
		echo $varrival; 
	 }else{
		echo $row[3];
	 }

 

how should this work? $varrival = date('D d/m',row[3]) tried lots of different ways and read the manual, lol.

 

 

thanks for your help!

[Barand]

need to convert date string from db to unixtime

 

$varrival = date('D d/m', strtotime(row[3]));

[Calvus]

Thanks for your help!

 

Got it working:

	<td><?
	 $varrival = date('D d/m', strtotime($row[3]));
	 $todaytxt = "<b><font color='FF0000'>$varrival</font></b>";
	 $comingtxt = "<b><font color='66FF66'>$varrival</font></b>";
	 $todate = date("Y-m-d");
	 if ($row[3] == $todate) {
		echo $todaytxt ;
	 }elseif($row[3] < $todate) {
		echo $comingtxt; 
	 }else{
		echo $row[3];
	 }