Jump to content


Photo

Query Help


  • Please log in to reply
1 reply to this topic

#1 underparnv

underparnv
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationReno, Nevada

Posted 02 March 2006 - 06:23 PM

Hey everyone. I have a query question. First the table:
id | page | hits | date |
This would be sample data:
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
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:

<?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;
    }
?>
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?
"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning."

The Sporting Edge - Free NFL Football Pool


#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 02 March 2006 - 09:10 PM

MySQL can do all of that.

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users