[Solved] Counting SQL Group Members

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

[Solved] Counting SQL Group Members

Post 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
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post 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>';
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post 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
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

RESOLVED

Post by jwalsh »

Worked Like A Charm! Thanks!
Post Reply