Question

PHP+MYSQL循环输出一个月的消费记录，并统计每一天的支出与收入总合

现有一张消费记录表（my_money），记录每一天的支出与收入情况。表结构如下图：

我写的SQL查询只能显示当天的消费情况，而我想要的是循环显示出一个月的消费情况，并将每一天的支出与收入做统计。
<?php
$daybegin=strtotime(date('Y-m-d'));
$dayend=$daybegin+86400;

//查询当天的数据
$sql="select * from my_money where time < $dayend and time > $daybegin";

//统计当天支出总额
$sql_out="select sum(cash) as cashout from my_money where time < $dayend and time > $daybegin and type=1";

//统计当天收入总额
$sql_in="select sum(cash) as cashin from my_money where time < $dayend and time > $daybegin and type=2";

?>

Answer 1

//统计当天支出总额
$sql_out="select sum(cash) as cashout from my_money where time < $dayend and time > $daybegin and type=1 group by FROM_UNIXTIME(time,'%y-%m-%d')";

//统计当天收入总额
$sql_in="select sum(cash) as cashin from my_money where time < $dayend and time > $daybegin and type=2 group by FROM_UNIXTIME(time,'%y-%m-%d')";