MySQL -将平均值从一个表更新到另一个表

Question

我是一个MYSQL/PHP新手，我相信这是一个简单的问题。我正在尝试从一个表中计算几个问题和受访者的平均值，并使用该值更新Group表。

例如，表答案由表B中的(name，group_id，TaskClarity1，TaskClarity2，TaskClarity3) I want (group_id，avg(TaskClarity1，TaskClarity2，TaskClarity3))组成。

这就是我得到的..。

$avg_task_clarity_1 = mysql_query("SELECT AVG(TaskClarity1) WHERE gruppid = '$group_id'");
$avg_task_clarity_2 = mysql_query("SELECT AVG(TaskClarity2) WHERE gruppid = '$group_id'");
$avg_task_clarity_3 = mysql_query("SELECT AVG(TaskClarity3) WHERE gruppid = '$group_id'");
$avg_task_clarity = ($avg_task_clarity_1+$avg_task_clarity_2+$avg_task_clarity_3)/3;
$print_task_clarity_1" UPDATE results SET results.TaskClarity = '$avg_task_clarity'"; 

if (mysql_query($print_task_clarity_1)) { echo $print_task_clarity_1; } else { echo "Error TaskClarity1: " . mysql_error(); 

Answer 1

首先，mysql_query()返回一个资源，然后需要从中提取信息。您的查询没有管理任何表名(我称之为MyTable)。此外，您还可以通过一个查询获得所有三个平均值。

下面是我如何开始的：

$table = "MyTable";
$sql = "SELECT AVG(TaskClarity1) AS avgClarity1,
               AVG(TaskClarity2) AS avgClarity2,
               AVG(TaskClarity3) AS avgClarity1
        FROM $table WHERE gruppid = '$group_id'";

$resource = mysql_query($sql);    //execute the query

if (! $resource = mysql_query($sql) ){
    echo "Error reading from table $table";
    die;
}

if (! mysql_num_rows($resource ) ){
    echo "No records found in $table";
}
else {
    $row = mysql_fetch_assoc($resource);   // fetch the first row
    $avg_task_clarity_1 = $row['avgClarity1'];
    $avg_task_clarity_2 = $row['avgClarity2'];
    $avg_task_clarity_3 = $row['avgClarity3'];
    $avg_task_clarity =
        ($avg_task_clarity_1+$avg_task_clarity_2+$avg_task_clarity_3)/3;

    //...
    // other stuff you want to do
}

如果这不够有帮助，请评论，我会修改我的答案。