Counting where month is the same

[timmah1]

I'm trying to get a total number from the db where the month is the same and the ip is different.

This is a stats script that someone else wrote, and I'm just trying to get a month-to-month total.

 

Right now, it pulls the information out and displays the month with how many different ips came that month, the problem is, it lists like this:

 

June - 41 - 41

 

June - 1 - 1

 

June - 1 - 1

 

June - 1 - 1

 

June - 1 - 1

 

June - 1 - 1

 

June - 9 - 9

 

June - 2 - 2

 

June - 2 - 2

 

June - 2 - 2

 

June - 1 - 1

 

June - 3 - 3

 

June - 2 - 2

 

June - 4 - 4

 

June - 1 - 1

 

June - 13 - 13

 

June - 154 - 154

 

what I need to do is grab all those numbers and give a total for that month, in this case, June.

 

This is my query

$query = "SELECT date, COUNT(referrer)'referrer' FROM app_analytics WHERE appid = $userid AND date BETWEEN '" .$y_month . "' AND '" . $t_month . "' GROUP BY referrer ORDER BY date";

 

Can anybody help out?

 

Thanks in advance

[cyberRobot]

A quick and dirty way is to do something like:

 

<?php
$monthTotals = array('January'=>0, 'February'=>0, 'March'=>0, 'April'=>0, 'May'=>0, 'June'=>0, 'July'=>0, 'August'=>0, 'September'=>0, 'October'=>0, 'November'=>0, 'December'=>0);
$testData    = array('June', 'June', 'July', 'August', 'June', 'July', 'May');

foreach($testData as $currMonth) {
$monthTotals[$currMonth]++;
}

foreach($monthTotals as $currKey => $currTotal) {
print "<div>$currKey: $currTotal</div>";
}
?>

[DavidAM]

Your query is grouping on referrer rather than the date, so I think you are getting the number of times for each unique referrer and NOT the number of different referrer's. If your date column is a DATE or DATETIME column I would think the query you want is:

 

SELECT MONTH(date), COUNT(DISTINCT referrer) 'referrer' 
FROM app_analytics 
WHERE appid = $userid 
AND date BETWEEN $y_month  AND  $t_month 
GROUP BY MONTH(date)

[Zane]

Have you tried grouping by referrer AND month?

 

GROUP BY referrer, month

[timmah1]

ok

 

This is working except for 2 things

 

It shows every month as December, and it don't give the total count?

 

I'm not sure what I'm doing wrong

 

Here is the code

$userid = '49';
$t_month = date("Y-m-d h:i:s");
$y_month = date("Y-m-d h:i:s", strtotime("-1 Year"));

//Grabs Unique Visitors to App Page
$query = "SELECT MONTH(date), COUNT(DISTINCT referrer) 'referrer' FROM app_analytics WHERE appid = $userid AND date BETWEEN '" .$y_month."'  AND  '" .$t_month."' GROUP BY MONTH(date)";

    $result = mysql_query($query) or die(mysql_error());

        $hits = mysql_num_rows($result);
        
        while($row = mysql_fetch_array( $result )){
            
            $months = date("m", strtotime($row['date'])); 
            $ip = $row['referrer'];
			echo $hits."<br />";
			echo $months."-".$ip."<br />";

      }

[DavidAM]

$months = date("m", strtotime($row['date']));

 

$row['date'] does not exist.

 

$query = "SELECT MONTH(date), COUNT(DISTINCT referrer) 'referrer' 
FROM app_analytics 
WHERE appid = $userid 
AND date BETWEEN '" .$y_month."'  AND  '" .$t_month."' 
GROUP BY MONTH(date)";

 

Since you did not give the column an alias, it would be referenced as $row['MONTH(date)'] (I think). Even then, it is not a date-time value. It is an integer between 1 and 12 indicating the month. You could use the MONTHNAME() function instead of MONTH().  But the sort would then be alphabetical instead of chronological. Also note that if your date criteria crosses a year, the order will be confusing, since you don't show the year either.

 

You might try something like this (untested code):

$query = "SELECT CONCAT_WS(' ', YEAR(date), MONTHNAME(date)) AS RptDate, COUNT(DISTINCT referrer) 'referrer' 
FROM app_analytics 
WHERE appid = $userid 
AND date BETWEEN '" .$y_month."'  AND  '" .$t_month."' 
GROUP BY CONCAT_WS(' ', YEAR(date), MONTHNAME(date))
ORDER BY YEAR(date), MONTH(date)";

// Then refer to the column as 
$months = $row['RptDate'];

[timmah1]

That's perfect DavidAM.

Thank you so much for your help