help with creating average between data in fields from mysql DB

webguync · June 1, 2010

I have a database with 8 fields all containing numbers from 1-7 and I have another field called 'average' where I am seeking to average the data. I am getting an error with the code I have which is "Warning: Cannot use a scalar value as an array in /var/www/vhosts/etsi-dataservices.com/httpdocs/NNPrinceton/Phase1A_May27_2010/report/Individual_Scores.php on line 59

Warning: Cannot use a scalar value as an array in /var/www/vhosts/etsi-dataservices.com/httpdocs/NNPrinceton/Phase1A_May27_2010/report/Individual_Scores.php on line 62"

not sure what this means. Anyway what I have is not working! Here is the entire code.

<html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Phase1A Award Results - May 2010</title>
<link href="report.css" rel="stylesheet" type="text/css" />
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
</head>
<body>
<html>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Phase1A Award Results - May 2010</title>
<link href="css/report.css" rel="stylesheet" type="text/css" />
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
</head>
<body>
<?php
ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);
$con = mysql_connect("localhost","username","pw");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("DBName", $con);


$result = mysql_query("SELECT employee_id,employee_name, roster_Phase1A_May27_2010.title,assessor_id,assessor_name,score1,score2,score3,score4,score5,score6,score7,score8,average,date_created,date_uploaded FROM rpc1A_May27_2010 LEFT JOIN  roster_Phase1A_May27_2010 USING (employee_id) ORDER BY employee_id, date_uploaded ASC") or die(mysql_error());
echo "<table>
<tr>

<th>Employee ID</th>
<th>Employee Name</th>
<th>Employee Title</th>
<th>score 1</th>
<th>score 2</th>
<th>score 3</th>
<th>score 4</th>
<th>score 5</th>
<th>score 6</th>
<th>score 7</th>
<th>score 8</th>
<th>Average Score (1-</th>
<th>Assessor Name</th>
<th>Assessor ID</th>
<th>Call Number (1-4)</th>
<th>Date Created</th>
<th>Date Uploaded</th>
</tr>";
//take sum
$row = 0;
$sum = 0;
for ($i = 1; $i <= 8; $i++){
$sum += $row['score'.$i];
}
$row['average'] = $sum/8;

//round $average
$row['average'] = round($row['average'], 1);

//count Call Numbers and list by date entered
$count = 0;
$CallNumber = 1;

while($row = mysql_fetch_array($result))
  {

$currentID = $row['employee_id'];

    if($count == 0 || $currentID != $previousID) {
        $CallNumber = 1; }
    else {
        $CallNumber++;
	}

  echo "<tr>";

  echo "<td>" . $row['employee_id'] . "</td>";
  echo "<td>" . ucwords($row['employee_name']) . "</td>";

  echo "<td>" . $row['title'] . "</td>";
  echo "<td>" . $row['score1'] . "</td>";
  echo "<td>" . $row['score2'] . "</td>";
  echo "<td>" . $row['score3'] . "</td>";
  echo "<td>" . $row['score4'] . "</td>";
  echo "<td>" . $row['score5'] . "</td>";
  echo "<td>" . $row['score6'] . "</td>";
  echo "<td>" . $row['score7'] . "</td>";
  echo "<td>" . $row['score8'] . "</td>";

  echo "<td>" . $row['average'] . "</td>";
  echo "<td>" . $row['assessor_name'] . "</td>";
  echo "<td>" . $row['assessor_id'] . "</td>";
  echo "<td>" . $CallNumber . "</td>";
  echo "<td>" . $row['date_created'] . "</td>";
  echo "<td>" . $row['date_uploaded'] . "</td>";
  echo "</tr>";

$previousID = $currentID;
    
    $count++;

  }
echo "</table>";
mysql_close($con);

?>
</body>
</html>




</body>
</html>

the specific code I am trying to do the averaging is...


//take sum
$row = 0;
$sum = 0;
for ($i = 1; $i <= 8; $i++){
$sum += $row['score'.$i];
}
$row['average'] = $sum/8;

//round $average
$row['average'] = round($row['average'], 1)

kenrbnsn · June 1, 2010

You can't use a variable as both a scalar (single value) and an array (multiple values) at the same time:

<?php
$row = 0;
$sum = 0;
for ($i = 1; $i <= 8; $i++){
$sum += $row['score'.$i];
}
$row['average'] = $sum/8;

//round $average
$row['average'] = round($row['average'], 1);
?>

Instead of using $row['average'] just use a normal variable:

<?php
$average = 0;
$sum = 0;
for ($i = 1; $i <= 8; $i++){
$sum += $row['score'.$i];
}
$average = round($sum/8);
?>

But this code isn't going to work where you have it. It has to be inside the while loop where you're fetching the rows.

Ken

webguync · June 1, 2010

thanks. I have having trouble figuring out where in the while loop this code needs to go to work. I tried right after the left curly brace here:

while($row = mysql_fetch_array($result))
  {

but that didn't work at all.

kenrbnsn · June 1, 2010

That's where it should go, but you have to change

<?php
echo "<td>" . $row['average'] . "</td>";
?>

to

<?php
echo "<td>$average</td>";
?>

Ken

webguync · June 1, 2010

the way I have it now, the only row being displayed is the average score, but it shows up as all zeros. Here is my while loop.

while($row = mysql_fetch_array($result))
  {
  //take sum
$row = 0;
$sum = 0;
for ($i = 1; $i <= 8; $i++){
$sum += $row['score'.$i];
}
$average = round($sum/8);

//round $average
$average = round($row['average'], 1);
//count Call Numbers and list by date entered
$count = 0;
$CallNumber = 1;

$currentID = $row['employee_id'];

    if($count == 0 || $currentID != $previousID) {
        $CallNumber = 1; }
    else {
        $CallNumber++;
	}

  echo "<tr>";

  echo "<td>" . $row['employee_id'] . "</td>";
  echo "<td>" . ucwords($row['employee_name']) . "</td>";

  echo "<td>" . $row['title'] . "</td>";
  echo "<td>" . $row['score1'] . "</td>";
  echo "<td>" . $row['score2'] . "</td>";
  echo "<td>" . $row['score3'] . "</td>";
  echo "<td>" . $row['score4'] . "</td>";
  echo "<td>" . $row['score5'] . "</td>";
  echo "<td>" . $row['score6'] . "</td>";
  echo "<td>" . $row['score7'] . "</td>";
  echo "<td>" . $row['score8'] . "</td>";

  echo "<td>" . $average . "</td>";
  echo "<td>" . $row['assessor_name'] . "</td>";
  echo "<td>" . $row['assessor_id'] . "</td>";
  echo "<td>" . $CallNumber . "</td>";
  echo "<td>" . $row['date_created'] . "</td>";
  echo "<td>" . $row['date_uploaded'] . "</td>";
  echo "</tr>";

$previousID = $currentID;
    
    $count++;

  }
echo "</table>";
mysql_close($con);

?>

Sign In

help with creating average between data in fields from mysql DB

Recommended Posts

webguync

Link to comment

Share on other sites

kenrbnsn

Link to comment

Share on other sites

webguync

Link to comment

Share on other sites

kenrbnsn

Link to comment

Share on other sites

webguync

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information