Need help with array

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pburgh
Forum Newbie
Posts: 13
Joined: Mon Feb 14, 2011 3:14 pm
Location: Boston

Need help with array

Post by pburgh »

I'm not the best PHP developer in the world, nor am I the worst... but I can't figure out a good way to handle some data I'm extracting from a db. I am pulling a userID and a group name from the db. Here's what each time through the output loop looks like it, I am using echo $userID." - ".$groupID;

2301230 - Group1
03e4333 - Group1
0933943 - Group1
3093433 - Group2
9349349 - Group2
0e20000 - Group3
23mwe4 - Group4
3023023 - Group4
...

What I need to do is sum up all the userIDs that belong to each group. So, in the end I'd have something like

Group1 has 3 users
Group2 has 2 users
Group3 has 1 user
Group4 has 2 users

For some reason I can't wrap my head around this. Any ideas? Thanks!
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Need help with array

Post by John Cartwright »

Using the group name as the key, it would be trivial to keep a running total, i.e.,

Code: Select all

//query database here

$counter = array();
while ($row = mysql_fetch_assoc($result)) {
  
   if (!array_key_exists($row['group_name'], $counter)) {
      $counter[$row['group_name']] = 0;
   } 
   
   $counter[$row['group_name']]++;
}
Although you should probably just design your query to return counts, if you are not actually required to fetch all the row's information. I.e.,

Code: Select all

SELECT COUNT(*) AS `count`, `group_name`
FROM `your_users_table`
GROUP BY `group_name`

Which would return 1 row per group, with a count column giving you the # of instances of that group.
pburgh
Forum Newbie
Posts: 13
Joined: Mon Feb 14, 2011 3:14 pm
Location: Boston

Re: Need help with array

Post by pburgh »

Thanks, that makes sense. I will try GROUP BY. :)
pburgh
Forum Newbie
Posts: 13
Joined: Mon Feb 14, 2011 3:14 pm
Location: Boston

Re: Need help with array

Post by pburgh »

Ugh, still stuck. I don't fully understand how GROUP BY works in this instance. Here is my code.

Code: Select all

$stid = oci_parse($conn,"SELECT * FROM METRICS.CS_DEPARTMENT, METRICS.CS_VISITOR, METRICS.CS_SESSION, METRICS.CS_SESSION_DEPARTMENT WHERE ( METRICS.CS_SESSION.
VISITOR_ID = METRICS.CS_VISITOR.ID ) AND ( METRICS.CS_SESSION_DEPARTMENT.DEPARTMENT_ID = METRICS.CS_DEPARTMENT.ID ) AND ( METRICS.
CS_SESSION_DEPARTMENT.SESSION_ID = METRICS.CS_SESSION.ID ) GROUP BY METRICS.CS_DEPARTMENT.DEPARTMENT");
oci_execute($stid);

while ($row = oci_fetch_array($stid, OCI_BOTH)) {
	echo $row["VISITOR"]." ".$row["DEPARTMENT"]."<br>";
}
The query is supposed to return a list of METRICS.CS_VISITOR.VISITOR and METRICS.CS_DEPARTMENT.DEPARTMENT. If I change the GROUP BY to ORDER BY, the list outputs just fine. Is my GROUP BY implementation incorrect?

Is there a simpler way to get all the VISITORs from each DEPARTMENT by just using SQL?

Thanks again
pburgh
Forum Newbie
Posts: 13
Joined: Mon Feb 14, 2011 3:14 pm
Location: Boston

SOLVED

Post by pburgh »

I figured it out using GROUP BY. Perhaps someone will find a use for this in the future... so here's the query:

Code: Select all

SELECT METRICS.CS_DEPARTMENT.DEPARTMENT, COUNT(UNIQUE METRICS.CS_VISITOR.VISITOR) AS VISITOR 
FROM METRICS.CS_DEPARTMENT, METRICS.CS_VISITOR, METRICS.CS_SESSION_DEPARTMENT, METRICS.CS_SESSION 
WHERE ( METRICS.CS_SESSION_DEPARTMENT.DEPARTMENT_ID = METRICS.CS_DEPARTMENT.ID ) 
AND ( METRICS.CS_SESSION_DEPARTMENT.SESSION_ID = METRICS.CS_SESSION.ID ) 
AND (METRICS.CS_SESSION.VISITOR_ID = METRICS.CS_VISITOR.ID )
GROUP BY  METRICS.CS_DEPARTMENT.DEPARTMENT
ORDER BY METRICS.CS_DEPARTMENT.DEPARTMENT ASC
Then I just used the values for VISITOR and DEPARTMENT in my output.
Post Reply