从选择查询向 mysql 插入值 [英] Insert values to mysql from select query

问题描述

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

I have a php results page which gets values from submited php form like

$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);
}

现在我想向 db 添加一个额外的字段，它将基于一个新的选择查询

Now i want to add an extra field to db which will be based on a new select query

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

我尝试将其添加到插入 sql 中，但没有结果

I've tried to add it to insert sql with no result

$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);
}

推荐答案

您可以使用插入/选择

You could use a insert/select

$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'";

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

in two step you could execute your, query get the value and assign to insert ... eg:

$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')";

这篇关于从选择查询向 mysql 插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！