php - 如何获取一列中所有值的mysql总和和减去

Question

我为会计应用程序创建了一个 API。我将收入和支出放在一栏中。如何将所有收入和支出相加并减去？

这是我的代码:

private function list_recap() {

    if($this->get_request_method() != "GET") {
        $this->response('', 406);
    }

    $year  = $this->_request['year'];
    $month = $this->_request['month'];

    $sqlPlus = $this->db->query("SELECT SUM(nominal) 
            FROM lap_keuangan 
                WHERE YEAR(tanggal) = '$year' 
                AND MONTH(tanggal) = '$month' 
           AND kategori = 1");

    $sqlMin  = $this->db->query("SELECT SUM(nominal) 
            FROM lap_keuangan 
                WHERE YEAR(tanggal) = '$year' 
                AND MONTH(tanggal) = '$month' 
                AND kategori = -1");


    $result = array();
    while($rowPlus = $sqlPlus->fetch(PDO::FETCH_ASSOC)) {
        $plus = $result[] = $rowPlus;
    }

    while($rowMin = $sqlMin->fetch(PDO::FETCH_ASSOC)) {
        $minus = $result[] = $rowMin;
    }

    $this->response($this->json($result), 200);
}

Answer 1

使用一个 SELECT 语句来检索“加”、“减”和“总计”。我们可以使用条件聚合。给定 WHERE 子句中的一个条件，保证 kategori 的值为 1 或 -1，我们可以将 nominal 乘以该值，然后将其与 SUM 相加.

当我们这样做时，我们应该清理 SQL 注入(inject)漏洞。我们永远不想在 SQL 文本中包含潜在不安全的值。

https://www.owasp.org/index.php/SQL_Injection

我们要么在包含这些值之前对其进行正确转义，要么使用带有绑定(bind)占位符的准备好的语句。 (这并不难；而且没有正当理由不做其中之一。)

为了提高性能，我们更喜欢在裸列上设置条件，而不是将列包装在表达式中。如果 tanggal 是 DATE、DATETIME 或 TIMESTAMP 数据类型，我们可以将该列上的条件指定为值范围。 (如果有合适的索引，这允许 MySQL 使用范围扫描操作，而不是在表中的每一行上计算表达式。)

我可能会将 $year 和 $month 值转换为 yyyy-mm-01 格式的字符串，然后将该日期字符串传递到查询中。但是我们可以将 $year 和 $month 值传递到查询中，并让 MySQL 将其转换为日期。

类似这样的事情:

# static SQL text (no variable interpolation) mitigates SQL Injection 
$sql = 
"SELECT SUM( k.nominal * IF(k.kategori=  1 ,1,0) )  AS kat_plus
      , SUM( k.nominal * IF(k.kategori= -1 ,1,0) )  AS kat_minus 
      , SUM( k.nominal * k.kategori              )  AS kat_total
   FROM lap_keuangan k 
  WHERE k.tanggal >= STR_TO_DATE(CONCAT( ? ,'-', ? ,'-01'),'%Y-%m-%d')
    AND k.tanggal <  STR_TO_DATE(CONCAT( ? ,'-', ? ,'-01'),'%Y-%m-%d') + INTERVAL 1 MONTH
    AND k.kategori IN (-1,1)";

# prepare
$sth = $this->db->prepare($sql);

使用bindValue为每个绑定(bind)占位符提供值并执行

# execute
$sth->bindValue(1, $year    ,PDO::PARAM_STR);
$sth->bindValue(2, $month   ,PDO::PARAM_STR);
$sth->bindValue(3, $year    ,PDO::PARAM_STR);
$sth->bindValue(4, $month   ,PDO::PARAM_STR);
$sth->execute(); 

或者使用位置绑定(bind)，我们可以跳过bindValue并仅传递一个数组来执行:

# execute
$sth->execute(array($year,$month,$year,$month));

然后获取行，并访问数组成员

$row = $sth->fetch(PDO::FETCH_ASSOC);

$kat_plus  = $row['kat_plus'];
$kat_minus = $row['kat_minus'];
$kat_total = $row['kat_total'];

<小时/>

如果 PDO 未配置为抛出异常，即

$this->$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

然后添加对每个 PDO 函数调用(准备、执行、获取)成功/失败的检查并处理代码中的错误。