Summing numbers

Anthony1312002

I'm trying to find out how to sum a number contained in a MySql db using php. I keep getting an error message when I try to sum the hours in the database. The hours are in row 8 which I have in bold. What am I doing wrong and how do I fix this. I guess you can tell I'm totally new to php.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Retrieved Information</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<?php

$Query = "Select * FROM activitytracking sum(Hours) as TotalHours WHERE DateOfService BETWEEN '%".$POST[txtStartDate]."%'
AND '%".$POST[txtEndDate]."%' AND Department Like '%".$POST[Departments]."%'
AND SiteID Like '%".$POST[Sites]."%'
GROUP BY TeamLeader,Hours ORDER BY siteid,department,teamleader";

$TeamLeader = $Row[1];

$result = mysql_db_query ($DBName, $Query, $Link);

//Fetch the results frm the database
print "<b><p>These are the records for the criteria you selected.</b></p>";
print "$TeamLeader";
print "<table border=0><tr><th>Employee Number</th>";
print "<th>First Name</th><th>Last Name</th><th>Department</th><th>Team Leader</th>";
print "<th>Date Of Service</th><th>Hours</th><th>Activity</th><th>ORG</th><th>Comments</th>";
print "<th>Involved When</th><th>SITE</th></tr>";
while ($Row = mysql_fetch_row ($result)) {

print " <tr>";

print "<td> $Row[1]</td>
<td>$Row[2]</td>
<td>$Row[3]</td>
<td> $Row[4]</td>
<td> $Row[5]</td>
<td> $Row[6]</td>
<td align=Center> $Row[8]</td>
<td> $Row[9]</td>
<td> $Row[10]</td>
<td> $Row[11]</td>
<td> $Row[12]</td>
<td> $Row[13]</td>";

print "</tr>";
}

 mysql_close ($Link);

print "</table>";
?>
</body>
</html>

bad76

🙁
sum() function work only if there is a "group by" clause, like this:

"select sum(hours) from table group by users"

But when there is a "group by" clause you have to use a sum(), count(), avg() or one other of this function in EVERY field that appear on select.

it's wrong:
"select num_access, sum(hours) from table group by users"

it's rigth:
"select max(num_access), sum(hours) from table group by users"

Anthony1312002

Thanks for getting back to me. I'm now getting this error message.

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in c:\program files\apache group\apache\htdocs\communityservice\newreport.php on line 47

This is what I did. I've highlighted line 47. Does my sql statement look correct?

<?php

$Query = "Select * FROM activitytracking sum(NumberOfHours) AS TotalHours WHERE DateOfService BETWEEN '%".$POST[txtStartDate]."%'
AND '%".$POST[txtEndDate]."%' AND Department Like '%".$POST[Departments]."%'
AND SiteID Like '%".$POST[Sites]."%'
GROUP BY NumberOfHours
ORDER BY siteid,department,teamleader";

$TeamLeader = $Row[1];

$result = mysql_db_query ($DBName, $Query, $Link);

print " <tr>";

print "<td> $Row[1]</td>
<td>$Row[2]</td>
<td>$Row[3]</td>
<td> $Row[4]</td>
<td> $Row[5]</td>
<td> $Row[6]</td>
<td> $Row[8]</td>
<td> $Row[9]</td>
<td> $Row[10]</td>
<td> $Row[11]</td>
<td> $Row[12]</td>
<td> $Row[13]</td>";

print "</tr>";
}

 mysql_close ($Link);

print "</table>";
?>
</body>
</html>

laserlight

probably a problem with your query.

you should also use mysql_query() instead of mysql_db_query()

bad76

When you have this error
try to rewrite in this way:

$result = mysql_db_query ($DBName, $Query, $Link) or die("Error 1:".mysql_error()."</b><br><i>".$Query."</i><br>");

But i think the proble is "select *". May be don't work with "group by"...

Anthony1312002

Thanks for the reply. I changed my sql statement to what I have below and it works. But what do I need to do in order display the grand total number of hours?

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Retrieved Information</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<?php

$Query = "Select ANumber,FirstName,LastName,Department,TeamLeader,DateOfService,Sum(NumberOfHours) AS Hours,ActivityType,
Org,Description,TimeTakenWhen,SiteID
FROM activitytracking WHERE DateOfService BETWEEN '%".$POST[txtStartDate]."%'
AND '%".$POST[txtEndDate]."%' AND Department Like '%".$POST[Departments]."%'
AND SiteID Like '%".$POST[Sites]."%'
GROUP BY Department,TeamLeader,NumberOfHours";

$result = mysql_db_query ($DBName, $Query, $Link);

//Fetch the results frm the database
print "<b><p>These are the records for the criteria you selected.</b></p>";
print "$TeamLeader";
print "<table border=1><tr><th>Employee Number</th>";
print "<th>First Name</th><th>Last Name</th><th>Department</th><th>Team Leader</th>";
print "<th>Date Of Service</th><th>Hours</th><th>Activity</th><th>ORG</th><th>Comments</th>";
print "<th>Involved When</th><th>SITE</th></tr>";
while ($Row = mysql_fetch_row ($result)) {

print " <tr>";

print "<td align=Center> $Row[0]</td>
<td align=Center>$Row[1]</td>
<td align=Center>$Row[2]</td>
<td align=Center> $Row[3]</td>
<td align=Center> $Row[4]</td>
<td align=Center> $Row[5]</td>
<td align=Center> $Row[6]</td>
<td> $Row[7]</td>
<td> $Row[8]</td>
<td> $Row[9]</td>
<td align=Center> $Row[10]</td>
<td> $Row[11]</td>";

print "</tr>";
}

 mysql_close ($Link);

print "</table>";
?>
</body>
</html>

bad76

Not work in this way, sorry.🙁

This may be work:

$Query = "Select Department,TeamLeader,Sum(NumberOfHours) AS Hours FROM activitytracking WHERE DateOfService BETWEEN '%".$_POST[txtStartDate]."%' 
AND '%".$_POST[txtEndDate]."%' AND Department Like '%".$_POST[Departments]."%' 
AND SiteID Like '%".$_POST[Sites]."%' 
GROUP BY Department,TeamLeader";

because the only selected field are:
- OR a group by field
- OR getted with sum(), avg(), count(), max(), min() etc... functions

see you

P.S: for more debug you add this to your db_query:

$result = mysql_db_query ($DBName, $Query, $Link) or die(mysql_error());