Query Help

[underparnv]

Hey everyone. I have a query question. First the table:
[code]id | page | hits | date |[/code]
This would be sample data:
[code]1, /dir/page.php, 50, 2006-03-02
2, /dir/page.php, 40, 2006-03-01
3, /dir/page2.php, 20, 2006-03-02
4, /dir/page2.php, 15, 2006-03-01[/code]
And so on.

What I would like to do is be able to grab all of the stats for the current month, then display the top 5 pages for that month. This is what I have so far:

[code]<?php
$month = date("m");
    $year = date("Y");
    $end_this_month = date('t',mktime(0,0,0,$month,1,$year));
    
    $start = $year . "-" . $month . "-01";
    $end = $year . "-" . $month . "-" . $end_this_month;
    
    $query = mysql_query("SELECT * FROM stats__pv WHERE date>='$start' AND date<='$end' ORDER BY page DESC");
    $pages = array();
    $prev_page = "";
    while($r=mysql_fetch_array($query)) {
        $current_page = $r["page"];
        $views = $r["nb"];
        if ($current_page == $prev_page) {
            $pages["$current_page"]["views"] = $pages["$current_page"]["views"] + $views;
        } else {
            $pages["$current_page"]["views"] = $views;
        }
        $prev_page = $current_page;
    }
?>[/code]
Now I have no way of sorting it (at least not that I can figure out).

So maybe this isn't so much a query question as an array sort question...

Any ideas?

[wickning1]

MySQL can do all of that.

[code]<?php
echo 'Top 5 Pages:<br>';
$query = mysql_query("SELECT page, SUM(hits) as hitcount FROM stats__pv WHERE date BETWEEN LAST_DAY(CURDATE())-INTERVAL 1 MONTH AND LAST_DAY(CURDATE()) GROUP BY page ORDER BY hitcount DESC LIMIT 5");
while($r=mysql_fetch_array($query)) {
    echo $r['page'] . ' - ' . $r['hitcount'] . ' hits<br>';
}
?>[/code]