phpmyadmin Question posted in
The official documentation can be found here.

How to use SQL cummulative sum in PHP database update – Phpmyadmin

I’m working on a simple account report with cumulative sum . I got the sql working in phpmyadmin .
set @csum := 0;
update accountssetup
set BAL = (@csum := @csum + DR) WHERE Accounttype = ‘EXPENSES’ AND date =’2019-07-19′
order by id;

when i put this SQL in PHP

i get error

// i removed the connection part

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// This is the where i am having issues ! ! !
$sql = "
set @csum := 0
update accountssetup
set BAL = (@csum := @csum + DR)
order by id;";

if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error; //$error ='1';

}
$conn->close();

I expect the Cummulative sum to work by updating BAL with the cumulative sum but i get this error

Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘update accountssetup set BAL = (@csum := @csum + DR) order by id’ at line 2

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This is working perfectly guys thanks 

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

}
$sql1 = "set @csum := 0";
$sql2 = "update accountssetupn"

. "set BAL = (@csum := @csum + DR)n"

. "order by id";

if ($conn->query($sql1)&& $conn->query($sql2) === TRUE)

{
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;

    //$error ='1';

    }
$conn->close();
                ?>

    I actually used multi query by setting two variables . $sql1 and $sql2 . This allow the two Sql to run at the same time.


  2. 0

    seems you missed a semicolon after set
    use set = 0 (and not set := 0) because is an initialization and try use () around (@csum + DR)

    $sql = "
set @csum = 0;
update accountssetup
set BAL = (@csum := (@csum + DR));";

    but be sure your db driver allow multiple sql statements in a a query
    you don’t need order by

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search