mySQL Select DISTINCT ips based on dates and return dates

Question

I am doing this query to know the count of unique IPs by date. The fact is that I need to return in PHP not only the number of unique IPs by date, but also the dates itself. How can I do it?

function getTotUniqueVisitsDay($id) {
    $query="SELECT COUNT(DISTINCT user_ip) FROM campaigns_visitors WHERE campaign_id = ".$id." group by date";
    $result = mysql_query($query) or die("Getting tot unique visits by day failed: " . mysql_error());   
    $visits_by_day = mysql_num_rows($result);
    return $visits_by_day;   
}

score 1 · Accepted Answer · edited Jun 07 '13 at 10:18

Your query is

SELECT COUNT(DISTINCT user_ip)
FROM campaigns_visitors
WHERE campaign_id = ?
GROUP BY date

This doesn't return date as you have found.

You should have more luck with

SELECT COUNT(DISTINCT user_ip), date
FROM campaigns_visitors
WHERE campaign_id = ?
GROUP BY date

Others are likely to recomment that you use prepared statements and mysqli routines; I have helpfully translated your query to a format that can be prepared.

I trust you can construct the PHP to manipulate the changed statement.

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

The best practise is to store the date as last_updated and date_created column. This post suggest how to get update date for any table How can I tell when a MySQL table was last updated?

and according to following post you can't get update date for specific row in a table How to get the date of record updating in MySQL

edited May 23 '17 at 12:18

Community

1
1

answered Jun 07 '13 at 10:14

Muhammad Raihan Muhaimin

5,317
7
44
64

score 0 · Answer 3 · answered Jun 07 '13 at 10:15

function getTotUniqueVisitsDay($id) {
    $query="SELECT date_field FROM campaigns_visitors WHERE campaign_id = ".$id." group by date_field";
    $result = mysql_query($query) or die("Getting tot unique visits by day failed: " . mysql_error());   
    $visits_by_day = mysql_num_rows($result);
    $visits_by_day['count'] = count($visits_by_day);
    return $visits_by_day;   
}

mySQL Select DISTINCT ips based on dates and return dates

3 Answers3