php - 从选择查询中将值插入到 mysql

Question

我有一个 php 结果页面，它从提交的 php 表单中获取值，例如

$sales = mysqli_real_escape_string($link, (int)$_POST['sales']);

我有一个插入查询

$sql = "INSERT INTO daily (date, sales) VALUES (CURRENT_TIMESTAMP, '$sales')";
if(mysqli_query($link, $sql)){
    "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

现在我想向数据库添加一个额外的字段，该字段将基于新的选择查询

$query = "SELECT SUM(sales) FROM daily WHERE date BETWEEN '2017-01-01' AND '2017-01-31'";

我尝试添加它来插入sql但没有结果

$sql = "INSERT INTO daily (date, sales, total_sales) VALUES (CURRENT_TIMESTAMP, '$sales', '$query')";
if(mysqli_query($link, $sql)){
    "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}

Answer 1

您可以使用插入/选择

$sql = "INSERT INTO daily (date, sales, total_sales) 
        SELECT 
            CURRENT_TIMESTAMP,
             '$sales',
            SUM(sales) 
       FROM daily 
       WHERE date BETWEEN '2017-01-01' AND '2017-01-31'";

分两步，您可以执行查询，获取值并分配给插入...例如:

$query = "SELECT SUM(sales) as tot FROM daily WHERE date BETWEEN '2017-01-01' AND '2017-01-31'";
mysqli_query($link, $query) ; 
$row = mysql_fetch_array($result, MYSQL_NUM);
$myTotal  = $row[0]
$sql = "INSERT INTO daily (date, sales, total_sales) VALUES (CURRENT_TIMESTAMP, '$sales', '$myTotal')";