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

Answer 1

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.