PHP Versions Question posted in
You can find a list of all versions here.

Php versions – MySQL query works directly in MYSQL but when running within php i get an error due to running a set query first

I am trying to run the following script within my PHP script to calculate a running total.

<?php // Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "set @csum := 0; select  date(`DATE`) as dadate,  Price , (@csum := @csum + Price) as cumulative_sum from Profits WHERE Strat = 'arm' order by dadate;";
$result = $conn->query($sql)or die($conn->error);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {

        echo "{time: '" . $row["Date"]. "', value: ".$row["cumulative_sum"]. "},<br>";
    }
} else {
    echo "0 results";
}
$conn->close(); ?>

I believe I am getting the error below because I am running 2 queries at once but am only supposed to execute one query at a time.

check the manual that corresponds to your MySQL server version for the
right syntax to use near ‘select date(daDATE), Price ,
(@csum := @csum + Price) as cumulative_’ at line 1

Is there any way around this is a need to set :csum to 0 to ensure it starts from 0

Tags:

2

Answers


  1. 0

    Strictly speaking, this is incorrect, but I cannot find an example of it not working (in MySQL versions prior to 8.0)…

    SELECT DATE(`daDATE`) date
     , Price
     , (@csum := @csum + Price) as cumulative_sum 
  from Profits 
     , (SELECT @csum := 0) vars 
 WHERE Strat = 'arm' 
 ORDER 
    BY daDATE;

    Note that because you’re passing the result to application code, you could just ad easily handle the cumulative arithmetic there

    Login or Signup to reply.
  2. 0

    Another initialization variant (without SELECT, primarily useful for UPDATE if you need it to be single-table):

    SELECT DATE(`daDATE`) date
     , Price
     , (@csum := @csum + Price) as cumulative_sum 
  from Profits
 WHERE Strat = 'arm' 
   AND (@csum := 0) = 0
 ORDER 
    BY daDATE;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search