Page 1 of 1

[Solved] Counting SQL Group Members

Posted: Sat Mar 12, 2005 4:19 pm
by jwalsh
Hi,

I'm working on building a membership system, and tracking how registered members navigate through the site. I'm having some problems creating a report. I am querying my table "visitors" by group like this...

"Select * From visitors Group By Browser"

This returns a list of unique browsers, but how can I return a count of how many visitors are using each browser?

I could parse the group results in an array, then loop through using "WHERE" to count... but is this the only way? or is there a better solution?

Josh

Posted: Sat Mar 12, 2005 5:13 pm
by infolock
here is a sniplet of a reporting tool i made that does just what you want. however, i'm not sure what type of data you are gonna be looking at to determine what browser the user is using as the types i use come out of a log file... but maybe this will help out a little on the query and the formatting...
(note: the switch statement could be rewritten to be a multi-dimensional array in the $brow array, but this code was written in like novem 2003 when i didn't exactly understand the concept of them... but if you can live with it this way, it works just as well. just rewritting to be a multidimensional array would save in processing time is all...)

Code: Select all

$brow=array('Net%','Moz%','NSPlayer%','WMFSDK/9%','WMFSDK/8%','WMFSDK/7%','MSIE/6%','MSIE/5%');
$count=array();
$Browsers=array();

for($i=0; $i<9; $i++)
{
	$sql = mysql_query("SELECT count(csUserAgent) FROM table WHERE (csUserAgent LIKE '".$brow[$i]."') and csUserAgent <> '-'") or die(MySQL_Error());
	while($row=mysql_fetch_assoc($sql))
	{
		if (($row['count(csUserAgent)'] != 0) and ($row['csUserAgent'] <> '-'))
		{
			$count[]=$row['count(csUserAgent)'];
			switch($brow[$i])
			{
				case 'Net%' :
					$Browsers[] = 'Netscape';
					break;
				case 'Moz%' :
					$Browsers[] = 'Mozilla';
					break;
				case 'NSPlayer%' :
					$Browsers[] = 'NSPlayer';
					break;
				case 'WMFSDK/9%' :
					$Browsers[] = 'Media Player 9';
					break;
				case 'WMFSDK/8%' :
					$Browsers[] = 'Media Player 8';
					break;
				case 'WMFSDK/7%' :
					$Browsers[] = 'Media Player 7';
					break;
				case 'MSIE/6%' :
					$Browsers[] = 'IE 6';
					break;
				case 'MSIE/5%' :
					$Browsers[] = 'IE 5';
					break;
				case 'MSIE/4%' :
					$Browsers[] = 'IE 4';
					break;
			}
		}
	}
}
echo'<table border=1><tr><td>Browers</td><td>Found</td></tr>';
for($i=0; $i<=count($Browsers); $i++) 
{
	if($Browsers[$i] <> '')
	{
		echo'
		<tr>
			<td>'.$Browsers[$i].'</td>
			<td>'.$count[$i].'</td>
		</tr>';
	}
}
echo '</table>';

Posted: Sat Mar 12, 2005 5:47 pm
by jwalsh
I'm pulling all my data straight out of $_SERVER['HTTP_USER_AGENT'] so it looks like your code should work, I'll take a look at it and let you know.

Thanks,

Josh

RESOLVED

Posted: Sat Mar 12, 2005 7:01 pm
by jwalsh
Worked Like A Charm! Thanks!