-
[PHP]Add all Hours
I need help trying to add up all the hours for a certain id. Um so ill show an example of my 2 tables.
=-Community-=
id l name
*Example*
1 l Jane Doe
=-Service-=
id l keyid l hours l approved
*Example*
4 l 1 l 3 l 1
5 l 1 l 2 l 1
The keyid corresponds to the id in community. I need it to add up the hours for all the keyid and then show the name as well.
So it should look like this.
l Name l Hours l
l-Jane Doe l 5 -l
l----------------------l
ive tried a lot of ways but it messes up.
PHP Code:
<?php
require_once('connect.php');
//Finding the Sum
$query9 = "SELECT hours FROM service WHERE approved = '1'";
$result9 = mysql_query($query9);
while($row9 = mysql_fetch_array($result9)) {
$rand9 = rand(0,1000);
$hours9[$rand9] = $row9['hours'];
}
$hours9 = array_sum($row9);
echo "Our school currently entered <b>".$hours9."</b> hours.";
//Log Script
echo "<table border='3'>";
echo "<tr> <th>Name</th> <th>Hours</th> </tr>";
$query = "SELECT * FROM service, community WHERE service.approved = '1' ORDER BY SUBSTRING_INDEX(community.name, ' ', -1)";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)) {
//Finding the Sum
while($row2 = mysql_fetch_array($result)) {
$rand = rand(0,1000);
$hours[$rand] = $row2['hours'];
}
$hours2 = array_sum($hours);
// Print out the contents of each row into a table
echo "<tr><td>";
echo $row['name'];
echo "</td><td>";
echo $hours2;
echo "</td></tr>";
}
echo "</table><br>";
echo "<center>";
?>
ive tried this but doesnt quite work. please help.
-
Re: [PHP]Add all Hours
I really have no clue what you're trying to due, but this is some guess-work. I re-wrote your entire script, but it might not work as I'm really sleepy, haha.
What it's supposed to do, is first give you a sum of all hours of the entire school, and then after that a table will form and will list off each person alongside with their total sum of hours. Queries may break if there's no hours inputted for an existing user.
PHP Code:
<?php
require_once("connect.php");
//Sum of total school hours
$sql = mysql_query("SELECT SUM(`hours`) FROM `service` WHERE `approved`='1'");
$sqlly = mysql_fetch_row($sql);
echo "Our school currently entered <b>".$sqlly[0]."</b> hours.<br>";
//Log script? lolwat?
echo "<table border=\"3\">
<tr><td>Name</td><td>Hours</td></tr>";
$sql2 = mysql_query("SELECT * FROM `community`");
while($sqlly2 = mysql_fetch_array($sql2)){
$sql3 = mysql_query("SELECT SUM(`hours`) FROM `service` WHERE `keyid`='".$sqlly2["id"]."' AND `approved`='1'");
$sqlly3 = mysql_fetch_row($sql3);
echo "<tr><td>
{$sqlly2["name"]}
</td><td>
{$sqlly3[0]}
</td></tr>";
}
echo "</table><br><center>";
?>
-
Re: [PHP]Add all Hours
I haven't worked with SQL in a long time, so this may not work.
PHP Code:
<?php
require_once 'connect.php';
$q = 'SELECT SUM(hours) FROM service WHERE (approved = 1)';
$row = mysql_fetch_row(mysql_query($q));
echo "Our school currently entered <b>{$row[0]}</b> hours.";
?>
<table border='3'>
<tr>
<th>Name</th>
<th>Hours</th>
</tr>
<?php
$q = 'SELECT community.name AS name, IFNULL(SUM(service.hours), 0) AS hours FROM community LEFT JOIN service ON (service.keyid = community.id AND service.approved = 1)';
$result = mysql_query($q);
while ($row = mysql_fetch_assoc($result)): ?>
<tr>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['hours']; ?></td>
</tr>
<?php endwhile; ?>
</table><br>
<center>
-
Re: [PHP]Add all Hours
Leo, your script worked fine. Thanks. =]