PHP Result Data / Table/Output Format - help pls...
Posted: Wed Jan 06, 2010 7:46 pm
Hi,
I have a table with 4 db columns: productname,checkpoints(ex: 11am,3pm), seconds,date.
where I am storing the product tasks and checkpoints and seconds.
I am trying to write a search query so that I can have an output table with the following format-
column1 - productname
column2 - date1 (current date)
column3 - date2 (yesterday)
column4 - date3 (current date -2)
under each date column, I am listing the seconds. I am able to specify the date columns with appropriate dates and I can also query the seconds but I don't think the task names and seconds are properly aligned.
Here's the current output
---
Task Name | Monitoring/Checkpoints | 2010-01-06 | 2010-01-05 | 2010-01-04
Click on Submit PPR link | 03:00 pm(IST) |50 | 2 | 33
Click on Create Receipt Link| 05:00 pm(IST) 3 33
GAM Dashboard |03:00 pm(IST) 33 33
Posting-Request ID Generation | 11:00 am(IST) 40 33
Click on Create invoice link | 11:00 am(IST) 10 33
Click on Create invoice link | 11:00 am(IST) 3
Any help would be really really appreciated. TIA
###################################
<?php
$con = mysql_connect("localhost","***","***");
if (!$con)
{
die('Database is down: ' . mysql_error());
}
mysql_select_db("test",$con);
$result = mysql_query("SELECT productname,checkpoints FROM environment_performance");
$row = mysql_fetch_array($result);
//$prod = $row['productname'];
$result1 = mysql_query("SELECT date FROM environment_performance where date = CURDATE()");
$row1 = mysql_fetch_array($result1);
$date = $row1['date'];
$result2 = mysql_query("SELECT date FROM environment_performance where date = CURDATE()-1");
$row2 = mysql_fetch_array($result2);
$date2 = $row2['date'];
$result3 = mysql_query("SELECT date FROM environment_performance where date = CURDATE()-2");
$row3 = mysql_fetch_array($result3);
$date3 = $row3['date'];
echo "<center>";
echo "<h3> Performance Results</h3>";
echo "<table border='1' bgcolor=#C1CDCD>";
echo "<tr>";
echo "<th>Task Name</th>";
echo "<th>Monitoring/Checkpoints</th>";
echo "<th>$date</th>";
echo "<th>$date2</th>";
echo "<th>$date3</th>";
echo "</tr>";
$firstdate = mysql_query("SELECT seconds FROM environment_performance where date=CURDATE()");
$fdate = mysql_fetch_array($firstdate);
$cur = $fdate['seconds'];
$seconddate = mysql_query("SELECT seconds FROM environment_performance where date=CURDATE()-1");
$sdate = mysql_fetch_array($seconddate);
$oneday = $sdate['seconds'];
$thirddate = mysql_query("SELECT seconds FROM environment_performance where date=CURDATE()-2");
$kdate = mysql_fetch_array($thirddate);
$twodays = $kdate['seconds'];
$i=0;
while ($row = mysql_fetch_array($result) and ($fdate = mysql_fetch_array($firstdate) or $sdate = mysql_fetch_array($seconddate) or $kdate = mysql_fetch_array($thirddate))) {
echo "</tr>";
echo "<td>" . $row['productname'] ."</td>";
echo "<td>" . $row['checkpoints'] ."</td>";
// echo "<td>" . $cur ."</td>";
echo "<td>" . $fdate['seconds'] ."</td>";
echo "<td>" . $sdate['seconds'] ."</td>";
echo "<td>" . $kdate['seconds'] ."</td>";
echo "</tr>";
}
$i++;
echo "</table>";
mysql_close($con);
?>
###################################
I have a table with 4 db columns: productname,checkpoints(ex: 11am,3pm), seconds,date.
where I am storing the product tasks and checkpoints and seconds.
I am trying to write a search query so that I can have an output table with the following format-
column1 - productname
column2 - date1 (current date)
column3 - date2 (yesterday)
column4 - date3 (current date -2)
under each date column, I am listing the seconds. I am able to specify the date columns with appropriate dates and I can also query the seconds but I don't think the task names and seconds are properly aligned.
Here's the current output
---
Task Name | Monitoring/Checkpoints | 2010-01-06 | 2010-01-05 | 2010-01-04
Click on Submit PPR link | 03:00 pm(IST) |50 | 2 | 33
Click on Create Receipt Link| 05:00 pm(IST) 3 33
GAM Dashboard |03:00 pm(IST) 33 33
Posting-Request ID Generation | 11:00 am(IST) 40 33
Click on Create invoice link | 11:00 am(IST) 10 33
Click on Create invoice link | 11:00 am(IST) 3
Any help would be really really appreciated. TIA
###################################
<?php
$con = mysql_connect("localhost","***","***");
if (!$con)
{
die('Database is down: ' . mysql_error());
}
mysql_select_db("test",$con);
$result = mysql_query("SELECT productname,checkpoints FROM environment_performance");
$row = mysql_fetch_array($result);
//$prod = $row['productname'];
$result1 = mysql_query("SELECT date FROM environment_performance where date = CURDATE()");
$row1 = mysql_fetch_array($result1);
$date = $row1['date'];
$result2 = mysql_query("SELECT date FROM environment_performance where date = CURDATE()-1");
$row2 = mysql_fetch_array($result2);
$date2 = $row2['date'];
$result3 = mysql_query("SELECT date FROM environment_performance where date = CURDATE()-2");
$row3 = mysql_fetch_array($result3);
$date3 = $row3['date'];
echo "<center>";
echo "<h3> Performance Results</h3>";
echo "<table border='1' bgcolor=#C1CDCD>";
echo "<tr>";
echo "<th>Task Name</th>";
echo "<th>Monitoring/Checkpoints</th>";
echo "<th>$date</th>";
echo "<th>$date2</th>";
echo "<th>$date3</th>";
echo "</tr>";
$firstdate = mysql_query("SELECT seconds FROM environment_performance where date=CURDATE()");
$fdate = mysql_fetch_array($firstdate);
$cur = $fdate['seconds'];
$seconddate = mysql_query("SELECT seconds FROM environment_performance where date=CURDATE()-1");
$sdate = mysql_fetch_array($seconddate);
$oneday = $sdate['seconds'];
$thirddate = mysql_query("SELECT seconds FROM environment_performance where date=CURDATE()-2");
$kdate = mysql_fetch_array($thirddate);
$twodays = $kdate['seconds'];
$i=0;
while ($row = mysql_fetch_array($result) and ($fdate = mysql_fetch_array($firstdate) or $sdate = mysql_fetch_array($seconddate) or $kdate = mysql_fetch_array($thirddate))) {
echo "</tr>";
echo "<td>" . $row['productname'] ."</td>";
echo "<td>" . $row['checkpoints'] ."</td>";
// echo "<td>" . $cur ."</td>";
echo "<td>" . $fdate['seconds'] ."</td>";
echo "<td>" . $sdate['seconds'] ."</td>";
echo "<td>" . $kdate['seconds'] ."</td>";
echo "</tr>";
}
$i++;
echo "</table>";
mysql_close($con);
?>
###################################