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
[Solved] Counting SQL Group Members
Moderator: General Moderators
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...)
(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>';