PHP MYSQL Foreach 日期列表

Question

我有两个表，“table1”和“table2”。 “table1”中是项目名称，“table2”中是日期和项目接收数量

SQL:

SELECT t2.title, t1.price, t1.quantity, t1.date
                  FROM table2 t2
                  JOIN table1 t1
                    ON t1.id = t2.t_id
                 WHERE t2.date BETWEEN '2017-07-12' AND '2017-07-15'

现在我得到这样的结果:

title date quantity
item1 2017-07-12 100
item2 2017-07-12 120
item3 2017-07-12 150
item1 2017-07-13 200
item2 2017-07-13 320
item3 2017-07-13 450

但我想得到这样的结果:

现在我在 PHP 中有了这段代码:

            <?php foreach ($AmmunitionDate as $key => $row): ?>
            <tr>
                <td> <?=$row['id']?> </td>
                <td> <?=$row['title']?> </td>
                <td> <?=$row['quantity']?> </td>
                <td>  </td>
            </tr>
            <?php endforeach ?>

我该如何解决这个任务？

谢谢

Answer 1

在mysql查询中这样试试

SELECT t2.title, Group_concat( `t1.date` order by `t1.date`) as date, 
Group_concat( t1.quantity order by `t1.date`) as quantity  ,sum(`t1.quantity`) as total 
FROM table2 t2
JOIN table1 t1
ON t1.id = t2.t_id
WHERE t2.date BETWEEN '2017-07-12' AND '2017-07-15' group by `t2.title`

它产生输出为

title    date                    quantity     total
item1   2017-07-12,2017-07-13    100,200       300
item2   2017-07-12 ,2017-07-13   120,320       440
item3   2017-07-12 ,2017-07-13   450,150       600

然后将逗号分隔值进行爆破，显示成你想要的样子。