计数记录每个月从mysql到html表 [英] Count record each day of a month from mysql into html table

问题描述

我想将所有mySql结果放在html表中。这是mySql：

I'd like to put all mySql results in a html table. This is mySql:

SELECT date(vwr_date) AS mon, date(vwr_date) AS date, count(vwr_cid) AS views 
FROM car_viewer 
WHERE Year(vwr_date)='2012' AND vwr_tid='18' 
GROUP BY date 
ORDER BY date DESC

从上面的查询，结果如下：

From the query above, it results like this:

date        views
2012-10-23  14
2012-10-22  339
2012-10-21  305
2012-10-20  354
....
2012-10-01  291
2012-09-30  246
2012-09-29  297
...
2012-09-01  281

mysql的结果非常好。现在这是一个问题。我试过很多次，不同的方式把这些意见，根据每个日期从car_viewer.vwr_date。所以我这样写：

The result from mysql is very good. And now this is a problem. I tried many times and different ways to put those views into according to each date from car_viewer.vwr_date. So I wrote like this:

<table border="0" cellpadding="3" cellspacing="1" width="100%">
        <thead>
        <tr>
            <th>Mon Year / Date</th>
            <?
            for($i=1;$i<32;$i++){
                echo "<th>$i</th>";
            }
            ?>
        </tr>
    </thead>
    <tbody>
        <?
        $_thisYear=date("Y");
        $sql_nViewer="select date(vwr_date) as mon, date(vwr_date) as date, count(vwr_cid) as views from car_viewer where Year(vwr_date)='$_thisYear' and vwr_tid='$_SESSION[admin_id]' GROUP BY date ORDER BY date desc";
        echo $sql_nViewer;
        $result_nViewer=mysql_db_query($dbname,$sql_nViewer);
        $rec_nViewer=mysql_fetch_array($result_nViewer);
        $_monYear=date("M Y",strtotime($rec_nViewer[mon]));
        $_monYear2=date("Y-n",strtotime($rec_nViewer[mon]));

            echo "<tr><td>$_monYear</td>";//month name
        $day=1;
        while($rec_nViewer=mysql_fetch_assoc($result_nViewer)){
            $_nViewerDBdate=$rec_nViewer['date'];
            $_nViewerDBviews=$rec_nViewer['views'];
            echo "
            <td>$_nViewerDBviews</td>
            ";$day++;
            if($day==$_daysInMon+1){
                echo "</tr>";
            }
        }
        ?>
        </tbody>
        </table>

上面的代码的结果是奇怪的 - 不是我想要的。我需要这样显示数据：

The result of codes above is weird - not as what I want. I need the data to be displayed like this:

所以，请建议我应该怎么做，使我的预期。

So please suggest what should I do to make it as I expected. Regards

推荐答案

您可以从mysql以此格式返回数据

You can return data in that format from mysql

SELECT MONTH_v, YEAR_V
GROUP_CONCAT(IF(day_v=1, views, null)) AS '1',
GROUP_CONCAT(IF(day_v=2, views, null)) AS '2',
----
GROUP_CONCAT(IF(day_v=31, views, null)) AS '31'
FROM
(
 SELECT DAY(vwr_date) AS day_v, 
 MONTH(vwr_date) AS MONTH_v, 
 Year(vwr_date) AS YEAR_V,
 date(vwr_date) AS date_v, 
 count(vwr_cid) AS views 
 FROM car_viewer 
 WHERE Year(vwr_date)='2012' AND vwr_tid='18' 
 GROUP BY date_v 
)
GROUP BY MONTH_v, YEAR_V 
ORDER BY MONTH_v, YEAR_V DESC

这篇关于计数记录每个月从mysql到html表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！