MYSQL user statistics query help?

Question

I have a table (statistics) with the columns User, Date (datetime), Page and IP. I have this query:

SELECT * FROM statistics WHERE page LIKE '%page_name' ORDER BY date DESC LIMIT 30.

That's ok to display all the user that visited that page. But I would like to display unique ips per day, something like DISTINCT ip (per day) I don't know how to add the 'per day' part...

any ideas? :)

Answer 1

Something like that should do the job

SELECT COUNT(ip), DATE_FORMAT(date, '%Y-%m-%d') AS day
FROM statistics WHERE page LIKE '%page_name'
GROUP BY day ORDER BY day DESC LIMIT 30

Answer 2

If you want to show the amount of unique IPs you can use MySQL's GROUP BY:

SELECT COUNT(DISTINCT(ip)), DATE(date)
FROM statistics
GROUP BY date
ORDER BY date DESC

If you want a list of all the unique IPs per date, you need to group them after you've retrieved them all, e.g.

$sql = "SELECT DISTINCT(ip) AS ip, DATE(date)
        FROM statistics
        ORDER BY date DESC";
$res = mysql_query($sql);
$ips = array();
while ($r = mysql_fetch_object($res)) {
  $ips[$r->date][] = $r->ip;
}

foreach ($ips as $date => $list) {
  // echo the $date
  foreach ($list as $ip) {
    // go through all the ips
  }
}