php - 执行计算最近行总和并将结果插入新列的查询时出现问题

Question

我是 PHP 和 MySQL 的新手，在此先抱歉。我正在尝试执行一个查询，计算一列最近 30 行的总和，并将结果插入表中的不同列。

代码如下:

$link = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME);

$sql1 = ("SELECT sum(open) FROM (SELECT date,open FROM AA ORDER BY date DESC LIMIT 30) sumopen30");
$result = mysqli_query($link,$sql1);

$resultarray = mysqli_fetch_assoc($result);
$attempts = $resultarray["open"];

echo $attempts;

$sql2 = "INSERT INTO AA (sum) VALUES ('$attempts')";
mysqli_query($link,$sql2);

我没有收到任何结果、更新或错误。如果我将查询更改为“SELECT open FROM AA WHERE open > 40”，则会回显数据，但我认为查询存在问题。任何建议将不胜感激。

Answer 1

问题是，总和未存储在 open 中 - 该字段名为 sum(open)

您还可以简化查询并给 sum(open) 一个新名称

$link = mysqli_connect(DBHOST, DBUSER, DBPASS, DBNAME);

$sql1 = ("SELECT sum(open) as sum_open FROM AA ORDER BY date DESC LIMIT 30");
$result = mysqli_query($link,$sql1);

$resultarray = mysqli_fetch_assoc($result);
$attempts = $resultarray["sum_open"];

echo $attempts;

$sql2 = "INSERT INTO AA (sum) VALUES ('$attempts')";
mysqli_query($link,$sql2);