Jump to content


This topic is now archived and is closed to further replies.


Query Help

Recommended Posts

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:

$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;
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?

Share this post

Link to post
Share on other sites
MySQL can do all of that.

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>';

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.