[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.

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>";
?>

---

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>

---

Leo, your script worked fine. Thanks. =]