Menu
Hi,
I have to divide the table date wise and show the rows fetched from the database in datewise for a particular table
I have the code below..
Please help me how can I proceed.
I have attached the image like how the table should look like.
[CODE]
//$start_date=’2008-03-18′;
//$end_date=’2008-03-20′;
$start_date = $_REQUEST[“date1”];
$end_date = $_REQUEST[“date2”];
$records=array();
$res_date=mysql_query(“select distinct(date(timestamp)) from `table1` where date(timestamp)>= ‘”.$start_date.”‘ and date(timestamp) <= ‘”.$end_date.”‘”);
while($res2=mysql_fetch_array($res_date)){
$res_head=”select distinct(name) from `table1` where date(timestamp)='”.$res2[0].”‘ and status=0″;
$res1 = mysql_query($res_head);
while($res = mysql_fetch_array($res1)){
$sql = “SELECT status,timestamp,name FROM `table1` WHERE name='”.$res[0].”‘ and date(timestamp)='”.$res2[0].”‘”;
$row = mysql_query($sql);
$previousStatus = ”;
$currentStatus = ”;
$previoustime=”;
$currentTime=”;
while($result = mysql_fetch_array($row))
{
$sql_ip=”select ip from `table2` where name='”.$res[0].”‘”;
$sql_ip1=mysql_query($sql_ip);
while($ip=mysql_fetch_array($sql_ip1)){
$currentStatus = $result[‘status’];
$currentTime = $result[‘timestamp’];
$previousStatus = $previousStatus == ” ? $currentStatus : $previousStatus;
$previoustime = $previoustime == ” ? $currentTime : $previoustime;
if($previousStatus <> $currentStatus)
{
$d = strtotime($currentTime) – strtotime($previoustime);
array_push($records,”$result[2]#$ip[0]#$result[2]#$previoustime#$result[1]#$d”);
}
$previousStatus = $currentStatus;
}
}
}
}
header(“Pragma: public”);
header(“Expires: 0”);
header(“Cache-Control: must-revalidate, post-check=0, pre-check=0”);
header(“Cache-Control: public”);
// header(“Content-Description: File Transfer”);
header(“Content-Type: application/msword”);
//$header=”Content-Disposition: attachment; filename=”.$filename.”;”;
header($header);
// header(“Content-Transfer-Encoding: binary”);
// header(“Content-Length: “.$len);
echo “<html>”;
echo “head>”;
echo “<META HTTP-EQUIV=”REFRESH” CONTENT=”1″>”;
echo “</head>”;
//header(“Content-Disposition: attachment; filename=worddoc.doc”);
echo “<html><head><title>REPORT</title></head><body bgcolor=”#FFFFFF” text=”#000000″>”;
echo “<TABLE BORDER=2 CELLPADDING=4 width=”100%” bordercolor=”#000000″><TR><TH COLSPAN=2>DATE AS ON </TH><th COLSPAN=6>Status(Down)</th></TR><TR><TH COLSPAN=8>NETWORK</TH></TR><TR><TD>SL NO</TD><TD>NAME</TD><TD>IP</TD><TD>NAME1</TD><TD>START TIME</TD><TD>END TIME</TD><TD>DURATION in (days:hours:minutes)</TD></TR>”;
$x=count($records);
for($i=0;$i<count($records);$i++){
$data=split(“#”,$records[$i]);
$seconds=$data[5];
$hours = floor($seconds/3600);
$days=floor($hours/24);
$hoursleft= $hours%24;
$timeleft = $seconds – ($hours*3600);
$minutes = floor($timeleft/60);
$seconds = $timeleft – ($minutes*60);
echo “<tr>”;
//echo “<td>$i</td>”;
echo “<td>”.($i+1).”</td>”;
echo “<td>$data[0]</td>”;
echo “<td>$data[1]</td>”;
echo “<td>$data[2]</td>”;
echo “<td>$data[3]</td>”;
echo “<td>$data[4]</td>”;
echo “<td>$days:$hoursleft:$minutes</td>”;
}
echo “</tr></table>”;
echo “</body></html>”;
[upl-file uuid=8167ec59-ece0-4a06-ae3c-dba3f2f4ce74 size=7kB]table.gif