PHP Forum

**ffsja** · 07:32 AM

I'm brand-new to PHP. I'm trying to create a page that displays averaged values for a sliding scale of values from 1-5 that users have previously filled out and entered into the database. I'm able to enter the values using a form into the MySQL DB, but this script is giving me a large headache...please take a look and give comments (and thank you):

PHP Code:


			
 <html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
 <title>Survey Results</title>
 <link href="stylesheet.css" rel="stylesheet" type="text/css" />
 </head>
 
 <body bgcolor="#FFFFFF" text="#999999" link="#336699" vlink="#666666">
 
 <?
 
 $db_name = "dbname";
 
 $table_name = "tablename";
 
 $connection = @mysql_connect("hostname", "username", "password") OR die('Could not connect to MySQL: ' . mysql_error());
 
 $db = @mysql_select_db($db_name, $connection) or die(mysql_error());
 
 $TotalSum = 0;
 $TotalCount = 0;
 
 $resultlist = "SELECT ID,surveydate,client,servicedesc,servicedate FROM $table_name ORDER BY surveydate desc";
 
 $totalresponses = "SELECT COUNT(client) as COUNT from $table_name";
 
 $knowledgescores = "SELECT COUNT(*) as RecordCount, sum(knowledge) as sum from $table_name WHERE knowledge > 0";
 
 if ($knowledgescores.recordcount > 0) {
 $TotalCount = $TotalCount + $knowledgescores.recordcount;
 $TotalSum = $TotalSum + $knowledgescores.sum;
 }
 
 $responsescores = "SELECT COUNT(*) as RecordCount, sum(response) as sum from $table_name WHERE response > 0";
 
 if ($responsescores.recordcount > 0) {
 $TotalCount = $TotalCount + $responsescores.recordcount;
 $TotalSum = $TotalSum + $responsescores.sum;
 }
 
 $courtesyscores = "SELECT COUNT(*) as RecordCount, sum(courtesy) as sum from $table_name WHERE courtesy > 0";
 
 if ($courtesyscores.recordcount > 0) {
 $TotalCount = $TotalCount + $courtesyscores.recordcount;
 $TotalSum = $TotalSum + $courtesyscores.sum;
 }
 
 $commscores = "SELECT COUNT(*) as RecordCount, sum(comm) as sum from $table_name WHERE comm > 0";
 
 if ($commscores.recordcount > 0) {
 $TotalCount = $TotalCount + $commscores.recordcount;
 $TotalSum = $TotalSum + $commscores.sum;
 }
 
 $ownershipscores = "SELECT COUNT(*) as RecordCount, sum(ownership) as sum from $table_name WHERE ownership > 0";
 
 if ($ownershipscores.recordcount > 0) {
 $TotalCount = $TotalCount + $ownershipscores.recordcount;
 $TotalSum = $TotalSum + $ownershipscores.sum;
 }
 
 $servicescores = "SELECT COUNT(*) as RecordCount, sum(service) as sum from $table_name WHERE service > 0";
 
 if ($servicescores.recordcount > 0) {
 $TotalCount = $TotalCount + $servicescores.recordcount;
 $TotalSum = $TotalSum + $servicescores.sum;
 }
 
 function SymRound($theNumber) {
 $x = 0;
 $rounded_down = 0;
 
 $NumDigits = 0;
 if(ArrayLen($Arguments) >= 2) {
     $numDigits = $Arguments[2];
 }
 
 $x = Abs($theNumber * (pow(10,$NumDigits)));
 $rounded_down = Int($x);
 
 if(($x - $rounded_down = 0.5) AND (! $rounded_down % 2)) {
     $x = $rounded_down;
 }
 else {
 $x = round($x);
 
 return $x/(pow(10,$NumDigits) * Sgn($theNumber));
 }
 
 ?>
 
 <table width="705" border="0" align="center" cellpadding="0" cellspacing="0">
   <tr>
 <td colspan="2"><a href="http://www.alaska.edu/uacp/"><img src="i/uacp.gif" name="uacp" width="291" height="116" border="0" id="uacp" /></a><img src="i/line.gif" alt="" name="line" width="353" height="116" border="0" id="line" /><a href="survey.cfm"><img src="i/home.gif" alt="home page" name="home" width="61" height="116" border="0" id="home" /></a></td>
   </tr>
   <tr>
     <td colspan="2">&nbsp;</td>
   </tr>
   <tr>
     <td width="38">&nbsp;</td>
     <td width="667"><table border="1" cellpadding="3" cellspacing="0">
         <tr bgcolor="Silver">
           <td colspan="2"><h3>Response Summary To Date</h3></td>
         </tr>
         <tr>
           <td>Survey Count</td>
           <td><div align="right"><? $totalresponses.count ?></div></td>
         </tr>
         <tr>
           <td>Overall Average</td>
           <td><div align="right">
           <? if ($TotalCount > 0) {
               $TotalAvg = SymRound(($TotalSum/$TotalCount), 2);
               print $TotalAvg;
               } else {
               print 0;
               }
               ?>
             </div></td>
         </tr>
         <tr>
           <td>Knowledge</td>
           <td> <div align="right">
           <? if ($knowledgescores.recordcount > 0) {
               $average = SymRound(($knowledgescores.sum/$knowledgescores.recordcount), 2);
               print $average;
               } else {
               print 0;
               }
               ?></div></td>
         </tr>
         <tr>
           <td>Response</td>
           <td> <div align="right">
           <? if ($responsescores.recordcount > 0) {
              $average = SymRound(($responsescores.sum/$responsescores.recordcount), 2);
                         print $average;
                         } else {
                         print 0;
                         }
               ?></div></td>
         </tr>
         <tr>
           <td>Courtesy</td>
           <td> <div align="right">
                     <? if ($courtesyscores.recordcount > 0) {
                       $average = SymRound(($courtesyscores.sum/$courtesyscores.recordcount), 2);
                       print $average;
                       } else {
                       print 0;
                       }
               ?></div></td>
         </tr>
         <tr>
           <td>Communication</td>
           <td> <div align="right">
                               <? if ($commscores.recordcount > 0) {
                                $average = SymRound(($commscores.sum/$commscores.recordcount), 2);
                                print $average;
                                } else {
                                print 0;
                                }
               ?></div></td>
         </tr>
         <tr>
           <td>Sense of Ownership</td>
           <td> <div align="right">
                               <? if ($ownershipscores.recordcount > 0) {
                                $average = SymRound(($ownershipscores.sum/$ownershipscores.recordcount), 2);
                                print $average;
                                } else {
                                print 0;
                                }
               ?></div></td>
         </tr>
         <tr>
           <td>Service</td>
           <td> <div align="right">
                               <? if ($servicescores.recordcount > 0) {
                                $average = SymRound(($servicescores.sum/$servicescores.recordcount), 2);
                                print $average;
                                } else {
                                print 0;
                                }
               ?></div></td>
         </tr>
       </table></td>
   </tr>
   <tr>
     <td colspan="2">&nbsp;</td>
   </tr>
   <tr>
     <td>&nbsp;</td>
     <td><table border="1" cellpadding="3" cellspacing="0">
         <tr bgcolor="Silver">
           <th>Survey Date</th>
           <th>Client</th>
           <th>Description of Service</th>
           <th>Date of Service</th>
         </tr>
           <tr>
             <td><a href="survey_detail.php?id=$ID"><? $surveydate ?></a></td>
             <td><? $client ?></td>
             <td><? $servicedesc ?></td>
             <td><div align="center"><? $servicedate ?></div></td>
           </tr> </table></td>
   </tr>
   <tr>
     <td colspan="2">&nbsp;</td>
   </tr>
 </table>
 <p>&nbsp;</p>
 
 </body>
 </html>

**0beron** · 07-12-2005, 07:40 AM

For a start, you are not actually executing any queries here, just assigning a bunch of strings. Also, if all you want from the db is an average, you can do this directly in MySQL. You are calculating a whole bunch of different averages here, but if you take one of them and change this:

PHP Code:


			
 $knowledgescores = "SELECT COUNT(*) as RecordCount, sum(knowledge) as sum from $table_name WHERE knowledge > 0";
 
 if ($knowledgescores.recordcount > 0) {
 $TotalCount = $TotalCount + $knowledgescores.recordcount;
 $TotalSum = $TotalSum + $knowledgescores.sum;
 }

into this:

PHP Code:


			
 $sql = "SELECT AVG(knowledge) as average from $table_name WHERE knowledge > 0";
 $result = mysql_query($sql) or die(mysql_error());
 $avg_knowledge = mysql_result($result,0,'average');

then that will get you your average for the knowledge category, and the other categories can be changed similarly.

**ffsja** · 07-12-2005, 12:25 PM

Thanks Oberon - that makes a lot of sense!