如何在PDO查询中获得列的总和
我想从我的数据库表中获得列的总和。但我只得到0.00。我的代码是:如何在PDO查询中获得列的总和
$total_income_query = $db->prepare("select sum(credit) from accounts WHERE STR_TO_DATE(date, '%d/%m/%Y') BETWEEN STR_TO_DATE(:startDate, '%d/%m/%Y') AND STR_TO_DATE(:endDate, '%d/%m/%Y')");
$stmt->bindParam(':startDate',$startDate);
$stmt->bindParam(':endDate',$endDate);
$stmt->execute();
$total = $stmt->fetch(PDO::FETCH_NUM);
echo $total_income = $total[0];
它应该回显306700.80。我检查了查询。它在数据库中正常工作,但不在我的PHP脚本中。它出什么问题了?
OK列,问题是我的PDO的代码。我应该写$ total_income_query而不是$ stmt。该代码应该是这样的......
$total_income_query = $db->prepare("select sum(credit) from accounts WHERE STR_TO_DATE(date, '%d/%m/%Y') BETWEEN STR_TO_DATE(:startDate, '%d/%m/%Y') AND STR_TO_DATE(:endDate, '%d/%m/%Y')");
$total_income_query->bindParam(':startDate',$startDate);
$total_income_query->bindParam(':endDate',$endDate);
$total_income_query->execute();
$total = $total_income_query->fetch(PDO::FETCH_NUM);
echo $total_income = $total[0];
编辑:我的解释是错的,我想。但遗憾的是,不要在数据库中重新格式化日期。 那就是:我想您存储日期类型DATE/DATETIME
<?php
$total_income_query = $db->prepare("
SELECT SUM(credit)
from accounts
WHERE date BETWEEN STR_TO_DATE(:startDate, '%d/%m/%Y')
AND STR_TO_DATE(:endDate, '%d/%m/%Y')");
$stmt->bindParam(':startDate',$startDate);
$stmt->bindParam(':endDate',$endDate);
$stmt->execute();
$total = $stmt->fetch(PDO::FETCH_NUM);
echo $total_income = $total[0];
但它在数据库中正常工作。我只是再次检查。 “从STR_TO_DATE(日期,'%d /%m /%Y')BETWEEN STR_TO_DATE('01/05/2017','%d /%m /%Y')和STR_TO_DATE之间的帐户中选择总额(借项) ('31/05/2017','%d /%m /%Y')“ –
日期列的类型是Varchar –
告诉我们什么类型是列日期,如果不是日期:您如何存储信息? –
尝试用'$ stmt->取(PDO :: FETCH_ASSOC);' –
**向我们展示了什么是'$ startDate'和'$ endDate' ** – RiggsFolly
'您在STR_TO_DATE(:startDate,'%d /%m /%Y')和STR_TO_DATE(:endDate,'%d /%m /%Y')之间的日期已将日期列中的值更改为某些文本看起来不像日期。因此,您无法将其与start enddate进行比较 –