Volunteer Hours

chrislabeard

Hello, I am trying to create a script to later be used as a plugin for wordpress. In this script I will have users create volunteer history with the fields (Organization, Hours, Date). Right now I have them listed in DESC order by hours and the hours are color coded by how many they have. One problem I have is when I try to use SUM to combine the hours and group by organization, the order in which they are listed is not correct and the css class they are all given is blue, I cannot figure out what I'm doing wrong and please if I have missed something else or my code is not correct please let me know.

<html>
<head>

<title>My codez</title>
<LINK REL=StyleSheet HREF="style.css" TYPE="text/css" MEDIA=screen>
<script src="scripts/jquery.js"></script>
<script src="slide.js"></script>
</head>

<body>
<div id="nav">
</div>
 <div id="add-record-window">
 <?php include "add.php";?>
	</div>
<div id="container">
 <h2>List Of Hours</h2>
 <fieldset>
 <ul>
<?php
require "config.php";

   // Connects to your Database 
mysql_connect($host, $username, $password) or die(mysql_error()); 
mysql_select_db($db_name) or die(mysql_error()); 

$q = "SELECT organization, hours, month, day, year FROM member_info ORDER BY hours DESC";
$result = mysql_query( $q );

// WE HAVE THREE LEVELS
$levels = array( 'red', 'green', 'blue' );

$max = 0;

while( $user = mysql_fetch_assoc( $result ) ) :

 if ( $max == 0 )
  $max = $user[ 'hours' ] ?>

<?php $even_odd = ( 'odd' != $even_odd ) ? 'odd' : 'even';?>

  <li class="<?php echo $even_odd;?>">
  <?php echo $user[ 'organization']?> 
  <span class="<?php echo $levels[ ceil( ( $user[ 'hours' ] / $max ) * count( $levels ) )  - 1 ]?>">
  <?php  echo $user[ 'hours' ] ?> hours</span> - 
  <?php echo $user['month']?>/<?php echo $user['day']?>/<?php echo $user['year']?></li>

 <?php endwhile ?>

 <li><FORM METHOD="LINK" ACTION="add.php">
<INPUT TYPE="submit" VALUE="Add More Hours">
</FORM></li>

 </ul>

  <h2>Total Hours</h2>

   <ul>

<?php 
$q_sum = "SELECT organization, SUM(hours) as sum_hours FROM member_info GROUP BY organization ORDER BY hours DESC";

$result_sum = mysql_query( $q_sum );

while( $user_sum = mysql_fetch_assoc( $result_sum ) ) :

$max_sum = 0;

if ( $max_sum == 0 )

$max_sum = $user_sum[ 'sum_hours' ] ?>

 <?php $even_odd = ( 'odd' != $even_odd ) ? 'odd' : 'even';?>
   <li class="<?php echo $even_odd;?>">
   <?php echo $user_sum[ 'organization']?>
   <span class="<?php echo $levels[ ceil( ( $user_sum[ 'sum_hours' ] / $max_sum ) * count( $levels ) )  - 1 ]?>">
   <?php  echo $user_sum[ 'sum_hours' ] ?> hours</span>
   </li>

<?php endwhile ?>
 </ul>
 </fieldset>
 </div><!--#Container -->

</body>
</html>

chrislabeard

The code i think that is having the problem is

<?php echo $levels[ ceil( ( $user[ 'hours' ] / $max ) * count( $levels ) )  - 1 ]?>

Is it possible to do something like if ( $user['hours'] > 50) some way to do 3 different levels like hours >50, hours > 150, hours < 250. I'm not too sure what the best way to do it is.

wilzy1

Your code is littered with errors... largely the absence of ' ; ' at the end of each line.

bradgrafelman

wilzy1;10949963 wrote:
Your code is littered with errors... largely the absence of ' ; ' at the end of each line.

There are no parse errors in the code he posted.

chrislabeard

So there are no errors? Any ideas of how I can get this to work?

bradgrafelman

Your problem is in the ceil() statement. You have:

( $user_sum[ 'sum_hours' ] / $max_sum )

But if you look at where $max_sum is defined, you have this:

$max_sum = $user_sum[ 'sum_hours' ]

Therefore, your ceil() statement basically contains:

( $user_sum[ 'sum_hours' ] / $user_sum[ 'sum_hours' ] )

which, assuming the sum isn't 0, basic math tell us that will always be equal to 1. That's why everyone has the same CSS style, because you're multiplying 1 by the number of levels every time.

Also note that there's no need to run a second SQL query that just contains an aggregate value of all of the data you just retrieved. Instead, just create an array to hold a running sum as you're outputting each member... e.g.:

$sum = array(); // define an array before you start retrieving members

// then, as you process each member in the table, do something like:
if(isset($sum[$user['organization']]))
    $sum[$user['organization']] += $user['hours'];
else
    $sum[$user['organization']] = $user['hours'];