Sum of tasks!!!
Posted: Thu Jul 01, 2004 10:44 am
Hi,
Actually I'm working on a time sheet. I have queries to get the report sort by date, project etc... but I need to find the sum of each activity as shown in the image below.

I have the table structure like this,
I have a php cose like this: which just sort the data from the db.
Can anyone help me to code it to display the sum like that in the image i have shown
thanks,
denis
Actually I'm working on a time sheet. I have queries to get the report sort by date, project etc... but I need to find the sum of each activity as shown in the image below.

I have the table structure like this,
Code: Select all
CREATE TABLE `tracker` (
`PkEcode` int(4) NOT NULL default '0',
`Date_txt` date NOT NULL default '0000-00-00',
`Team` varchar(30) NOT NULL default '',
`CourseCode` varchar(30) NOT NULL default '',
`Activity` varchar(100) NOT NULL default '',
`Slides` int(5) NOT NULL default '0',
`Time` decimal(5,2) default NULL,
`Recordcount` int(12) NOT NULL auto_increment,
PRIMARY KEY (`Recordcount`)
) TYPE=MyISAM AUTO_INCREMENT=165 ;
INSERT INTO `tracker` VALUES (4001, '2004-06-22', 'Audio', 'Project-1', 'Audio Editing', 12, '7.00', 145);
INSERT INTO `tracker` VALUES (4002, '2004-06-22', 'Audio', 'Project-1', 'Audio Editing', 12, '8.00', 146);
INSERT INTO `tracker` VALUES (4002, '2004-06-24', 'Audio', 'Project-1', 'Audio Cutting', 34, '10.00', 147);
INSERT INTO `tracker` VALUES (4003, '2004-06-22', 'Development', 'Project-1', 'Animation', 15, '8.00', 148);
INSERT INTO `tracker` VALUES (4004, '2004-06-24', 'Development', 'Project-1', 'Animation', 15, '8.00', 149);
INSERT INTO `tracker` VALUES (4005, '2004-06-22', 'QC', 'Project-1','QC-Level1', 24, '4.00', 154);
INSERT INTO `tracker` VALUES (4006, '2004-06-24', 'QC', 'Project-1','QC-Level2', 24, '4.00', 155);Can anyone help me to code it to display the sum like that in the image i have shown
Code: Select all
<?
$hostname = "localhost";
$username = "";
$password = "";
$dbName = "timetracker";
MYSQL_CONNECT($hostname, $username, $password) OR DIE("Unable to connect");
@MYSQL_SELECT_DB("$dbName") OR DIE("Unable to select database");
$report = "SELECT DATE_FORMAT(b.date_txt, ''%m-%d-%Y'' ) AS ''Date'' ,b.Team, a.username AS ''Name'', b.Activity, b.time FROM tracker b, users a where a.pkecode = b.pkecode ORDER BY b.date_txt";
if(!isset($start)) $start = 0;
$reports = $report;
$q= stripslashes($reports);
$query = $q. " LIMIT $start, 40";
$report1 = $q;
$query1 = $report1;
$result1 = mysql_query($query1);
$numrows = mysql_num_rows($result1);
$result = mysql_query($query);
$total_rows = mysql_num_rows($result);
$row = mysql_fetch_row($result);
$total_cols = count($row);
if (!$total_rows) {
print "<HTML><title>Reports</title><FONT size='4' color='#000000' face='Verdana, Arial, Helvetica, sans-serif'><b>No report found! Please search again.... </b></FONT></BODY></HTML>";
return;
}
print "<HTML><title>Reports</title><BODY text="#000000" link="#FFFFFF" vlink="#FFFFFF" alink="#FFFFFF" topmargin='0' marginheight='0'>";
print "<table width='500' border='0' cellspacing='0' cellpadding='3' align='center'> ";
print "<tr bgcolor="#61ABD0"><td colspan=$total_cols align=center><FONT size='3' color='#FFFFFF' face='Verdana, Arial, Helvetica, sans-serif'><b>$pagetitle</b></FONT></td></tr>";
print "<tr bgcolor="#A8D2F2">";
$count = mysql_num_fields($result);
for ($i = 0; $i < $count; $i++) {
print "<td><FONT size='2' face='Verdana, Arial, Helvetica, sans-serif'><b>";
$header = mysql_field_name($result, $i)."\t";
print $header;
print "</b></FONT></td>";
}
print "</tr>";
print "<tr>";
$i=0;
while($i < $total_cols){
print "<td><FONT size='1' face='Verdana, Arial, Helvetica, sans-serif'>";
print $row[$i];
print "</FONT></td>";
$i++;
}
print "</tr>";
$j = 1;
while($row = mysql_fetch_row ($result)) {
$j++;
$i = 0;
if ($j%2 == 0) {
print "<tr bgcolor="#EAF3FB">";
}else{
print "<tr bgcolor="#FFFFFF">";
}
while($i < $total_cols){
print "<td ><FONT size='1' face='Verdana, Arial, Helvetica, sans-serif'>";
print $row[$i];
print "</FONT></td>";
$i++;
}
print "</tr>";
}
print "<tr><td> ";
print "<tr bgcolor="#A8D2F2"><td colspan=$total_cols align=center>";
print "<table width='100%' border='0' cellspacing='0' cellpadding='2' align='center'> ";
print "<tr width='50%'><td align='left' width='25%'><FONT size='2' color='#FFFFFF' face='Verdana, Arial, Helvetica, sans-serif'>";
if($start > 0)
echo "<< <a href="" . $PHP_SELF . "?start=" . ($start - 40) ."&report=".$query1."">Previous</a>";
print "</FONT></td><td align='center' width='50%' height='20'><FONT size='2' color='#FFFFFF' face='Verdana, Arial, Helvetica, sans-serif'>";
print "<a href="javascript:void(window.print())"><img src="images\printicon.gif" border='0' alt='print'></a>";
print "</FONT></td><td align='right' width='25%' height='20'><FONT size='2' color='#FFFFFF' face='Verdana, Arial, Helvetica, sans-serif'>";
if($numrows > ($start + 40))
echo "<a href="" . $PHP_SELF . "?start=" . ($start + 40) ."&report=".$query1."">Next</a> >>";
print "</FONT></td></tr>";
print "</TABLE>";
print "</td></tr>";
print "</TABLE></BODY></HTML>";
?>thanks,
denis