使用COUNT，GROUP BY，ORDER BY MAX过滤日志文件 [英] Filtering log file using COUNT, GROUP BY, ORDER BY MAX

问题描述

您好，我有一台机器记录客人和用户导航到哪里，我想写一个php脚本，将排序日志，并总结为我最喜欢的五个目的地人们往往，即访问哪些网页最多。

Hi I have a machine that logs where guests and users navigate to and I'm trying to write a php script that will sort the logs and summarize for me the top five destinations people tend to go to i.e. what pages are visited the most.

到目前为止，这是我有的：

So far this is what I have:

<?php

#### mysql connection #####
$db = mysql_connect("_","guest","_");
mysql_select_db(networkr) or die("Couldn't select the database");
echo "<table>";
$query = "SELECT uri LIMIT 5, COUNT(date) GROUP BY uri ORDER BY MAX(COUNT(date)), uri DESC";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
    echo "Top 5 Destinations:". $row['COUNT(date)'] ." ". $row['uri'] .".";
    echo "<br />";
}
echo "</table>";
?>

所以我试图让它计数所有的时间一个特定的页面使用日期作为唯一标识符。然后，我希望它从最高到最低以降序列出计数。

So I'm trying to get it to count all the times a specific page (uri) is visited using date as the unique identifier. Then I want it to list the counts in descending order from highest to lowest. Then only show the top five most visited pages.

我在网络浏览器中打开php脚本时出现的错误是：

The error I am getting when I open the php script in a web browser is:

您的SQL语法有错误;
检查对应于
的手册您的MySQL服务器版本
右语法在'COUNT（日期）附近使用
GROUP BY uri ORDER BY
MAX（COUNT （date）），uri DESC'在第1行

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COUNT(date) GROUP BY uri ORDER BY MAX(COUNT(date)), uri DESC' at line 1

我认为它的某种语法错误，因为我不是php的专家。

I think its some kind of syntax error since I am not an expert at php. If anyone can help me run through the script for errors that would be most appreciated.

推荐答案

您不必使用 MAX（）。

SELECT uri, COUNT(date) AS visits
FROM table
GROUP BY uri
ORDER BY visits DESC
LIMIT 5

$ c> echo 像这样：

And you echo like this:

while($row = mysql_fetch_assoc($result)){
    echo "Top 5 Destinations:". $row['visits'] ." ". $row['uri'];
    echo "<br />";
}

这篇关于使用COUNT，GROUP BY，ORDER BY MAX过滤日志文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！