Insert values to mysql from select query

Question

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

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

I have an insert query

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

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

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 are trying to insert the query into your table rather than the result. Run the query separately and then save just the result. — Niraj Shah
– Niraj Shah, Commented Mar 14, 2017 at 11:02
In above $query = "Select .....", You have written the query but dose't execute that query. Excute the written query and pass that value — Muthu17
– Muthu17, Commented Mar 14, 2017 at 11:03

ScaisEdge · Accepted Answer · 2017-03-14 12:43:58Z

3

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

edited Mar 14, 2017 at 12:43

answered Mar 14, 2017 at 11:06

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

valleymanbs Over a year ago

This way you won't add the $sales value to the SUM of sales... Anyway it's the correct way to do so... But i think he's doing this in 2 different steps.

P. Tzik Over a year ago

Yeap i prefer on two different steps

P. Tzik Over a year ago

The only suggestion that worked but i need it in 2 steps

ScaisEdge Over a year ago

I don't undertsand because you need two step .. (one step is better ,, more fast and readable..) but anyway i have update the answer with a suggestio for two step

Community · Accepted Answer · 2017-05-23 12:17:21Z

0

You should run that query first, save the results in an array, cycle through the array and add the result in the correct field of your database.

what you are doing now is to add the text of your query in the total_sales column of your daily table.

You can refer to this answer to inspire yourself: Get sum of MySQL column in PHP

Also this page explains very well what you have to do, scroll down until you reach the example titled "Inserting the result of a query in another table with group by".

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Mar 14, 2017 at 11:06

valleymanbs

4973 silver badges16 bronze badges

4 Comments

P. Tzik Over a year ago

I've tried $query = "SELECT SUM(sales) FROM daily WHERE date BETWEEN '2017-01-01' AND '2017-01-31'"; $row=mysql_fetch_array($query); but didn't work

valleymanbs Over a year ago

i've edited my answer linking an accepted answer that will help you figuring this out...

P. Tzik Over a year ago

I can't manage it. I tried most of them. $query = mysql_query("SELECT SUM(sales) as total FROM daily WHERE date BETWEEN '2017-01-01' AND '2017-01-31'"); $row = mysql_fetch_array($query); $sum = $row['total']; Where is my mistake?

valleymanbs Over a year ago

Your mistake is not wanting to understand how this works, but wanting us to tell you how to do this. Read a tutorial on PHP and MySQL, use PDO, since mysql_query has been deprecated since years now. It seems to me that you don't have a clear idea of what you are doing in PHP, leaving out MySQL. To help you a bit, in your last comment, what you are doing is saving the data you need inside the $sum variable. Try to understand better what you are doing first. You need basics...

P. Tzik · Accepted Answer · 2017-03-14 14:11:04Z

0

The code that worked was simple:

$result= mysqli_query($link, "SELECT SUM(sales) as valuesum FROM daily);
$row = mysqli_fetch_assoc($result); 
$total_sales = $row['valuesum'];

I couldn't manage it to work because there was other errors which i found from phpfpm-error.log

answered Mar 14, 2017 at 14:11

P. Tzik

1431 gold badge3 silver badges12 bronze badges

Comments

Abdullah Al Shakib · Accepted Answer · 2017-03-14 11:58:19Z

-1

Try this way:

$sales = mysqli_real_escape_string($link, (int)$_POST['sales']);
$query = "SELECT SUM(sales) FROM daily WHERE date BETWEEN '2017-01-01' AND '2017-01-31'"

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

edited Mar 14, 2017 at 11:58

answered Mar 14, 2017 at 11:13

Abdullah Al Shakib

2,0442 gold badges15 silver badges16 bronze badges

2 Comments

P. Tzik Over a year ago

I tried $sql = "INSERT INTO daily set date = CURRENT_TIMESTAMP, sales = '$sales', total_sales = '$query'"; but it doesn't insert values on db

Abdullah Al Shakib Over a year ago

You missed the brace (). just run as same as the query. "INSERT INTO daily SET date = CURRENT_TIMESTAMP, sales = ' ".$sales." ', total_sales = ($query)"

Collectives™ on Stack Overflow

Insert values to mysql from select query

4 Answers 4

4 Comments

4 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related