Jump to content

Query Help


underparnv

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:

[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?
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.