Filtering log file using COUNT, GROUP BY, ORDER BY MAX

Hi I have a machine that logs where guests and users navigate to and I开发者_StackOverflow社区'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.

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

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

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.

LIMIT should appear after the rest of your query, and you're not specifying where to get the records from with a FROM clause, like so:

SELECT uri, COUNT(date) 
FROM <tablename>
GROUP BY uri 
ORDER BY MAX(COUNT(date)), uri DESC 
LIMIT 5

1. You're missing a FROM clause.

2. Move the LIMIT after the ORDER BY

   SELECT uri, COUNT(date) FROM YourTable GROUP BY uri ORDER BY MAX(COUNT(date)), uri DESC LIMIT 5

You do not have to use MAX(). Just use the query below.

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

And you echo like this:

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