从表一表中选择数据并插入另一个表

I have two tables; result and total.

我有两张桌子;结果和总数。

result(student_code,mark)
total(student_code,total)

The total column should contain total marks of a student.

总列应包含学生的总分。

So I have written an SQL statement to insert data from result table into total table by using a SELECT statement which should take a specific student_code and his/her marks as a summation and then store it in the total table.

所以我编写了一个SQL语句,通过使用SELECT语句将结果表中的数据插入到总表中,该语句应该将特定的student_code和他/她的标记作为求和,然后将其存储在总表中。

But, whenever I try, the query either fails or just takes only one student

但是,每当我尝试时,查询失败或只需要一个学生

When I use this query, it works but for only one student, whose student_code is 1, but I need to take all the students and their marks as total

当我使用这个查询时,它只适用于一个学生,其student_code为1,但我需要将所有学生及其分数作为总数

<?php

// copy student student_code and summation of his/her mark  from result table into total table
$query = 'INSERT INTO total
(student_code, total)
SELECT  student_code, SUM(mark)
FROM
result
WHERE
student_code = 1';
$sql = mysql_query($query) or (mysql_error());
?>

And when I use a student code as a variable I get nothing

当我使用学生代码作为变量时,我什么也得不到

<?php

// copy student student_code and summation of his/her mark  from result table into total table
$query = 'INSERT INTO total
(student_code, total)
SELECT  student_code, SUM(mark)
FROM
result
WHERE
student_code = "' . $student_code . '"';
$sql = mysql_query($query) or (mysql_error());
?>

1 个解决方案

#1

You must perform two different queries first query selects the data and then store it to a php variable and then later insert it into another table using the second query

您必须执行两个不同的查询,首先查询选择数据,然后将其存储到php变量,然后使用第二个查询将其插入另一个表

UPDATE----

Try this code

试试这个代码

<?php
for($i = 1; $i < 102; $i++)
{
$query = 'INSERT INTO total (student_code, total) SELECT  student_code, SUM(mark) FROM result WHERE student_code = "$i"';
$sql = mysql_query($query) or (mysql_error());
}
?>

It must work

它必须工作

#1