我想在PHP/MYSQL上得到一个结果我有一张订单表,上面有total_paid 的价格
我需要询问当前日期的所有价格总额,我无法插入正确的日期。我在这里停下来,不工作。。。thnaks
....
$date = date("Y-m-d");
$query = "SELECT SUM(total_paid) FROM ps_orders WHERE delivery_date = '%$date%'";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result);
echo mysql_result($result, 0);
尝试
$query = "SELECT SUM(total_paid) FROM ps_orders WHERE delivery_date = '$date'";
或
$query = "SELECT SUM(total_paid) FROM ps_orders WHERE delivery_date LIKE '%$date%'";
根据delivery_date字段的设置方式以及$date变量中使用的内容(时间戳与m-d-y),查询where子句看起来不错,但您也可以尝试:
SELECT SUM(total_paid)
FROM ps_orders
WHERE delivery_date = $date;
如果使用日期时间字段来表示delevery_date,则必须更深入地使用一个范围:
SELECT SUM(total_paid)
FROM ps_orders
where (delivery_date > $date
and deliver_date < $date +interval 1 day)
这个链接在处理日期时也会对你有所帮助:http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-添加
您可以使用以下选项之一:
-
mysql中的DATE函数将
2013-11-20 10:54:12
转换为2013-11-20
,即在日期中截断一个时间$query="SELECT SUM(total_paid)FROM ps_orders WHERE DATE(delivery_DATE)='$DATE'";
-
或者使用LIKE时$date后仅使用一个"%",因此此值将与
2013-11-20 10:12:13
:等日期匹配$query="SELECT SUM(total_paid)FROM ps_orders WHERE delivery_date like'$date%'";
-
或者使用字符串mysql函数SUBSTRING
$query="SELECT SUM(total_paid)FROM ps_orders WHERE SUBSTRING(delivery_date1,1,10)='$date'";
-
使用datetime和时间以及BETWEN-mysql比较运算符:
$query="在'$date 00:00:00'和'$date 23:59:59'之间从ps_orders WHERE delivery_date1中选择SUM(total_paid)