Page 1 of 1

Sum of tasks!!!

Posted: Thu Jul 01, 2004 10:44 am
by deniscyriac
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.
Image

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

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

Posted: Thu Jul 01, 2004 10:49 am
by feyd

Code: Select all

<?php

preg_match_all('#^\s*(\d+)\s+hrs\s*$#i',$data,$matches);

$sum = 0;
for($x = 0, $y = count($matches[1]); $x < $y; $x++)
  $sum += (int)$matches[1][$x];

echo $sum.' hrs';

?>

Posted: Fri Jul 02, 2004 6:23 am
by deniscyriac
Bech100 | Please learn how to use BBCode properly, thanks. Use for MySQL stuff. Read: :arrow: Posting Code in the Forums[/color]