470,826 Members | 2,038 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,826 developers. It's quick & easy.

PHP mysql query from 2 tables

I have two tables I am trying to pull data from. The first table holds "groups" and the other table "group_members" holds the members of that group with a respective id that points to id in "groups".

I need to count the number of members (rows.. each row represents one membership) from group members, and print the 10 most populus groups with their total members.

this is what i have for a query, but then i do not know how to fetch the array... I can't think right now either because i have been staring at this for hours!!

here is my query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. $sql="SELECT group_id, count(*) as a FROM group_friends GROUP BY group_id ORDER BY COUNT(*) DESC";
  3.  
  4. $res=mysql_query($sql)
  5. while($data=mysql_fetch_array($res)) {
  6. print data...
  7. }
  8.  
thanks for the help!!
Mar 3 '06 #1
2 5696
Banfa
9,065 Expert Mod 8TB
I think you have made a typo in your query, you have FROM group_friends but you have stated that you tables are called groups and group_members. I assume that you meant group_members. Also "AS a", a is not very descriptive how about "AS count" or "AS num".

You have done most of the hard work retrieving the data, personally I would use mysql_fetch_assoc rather than mysql_fetch_array but it makes little difference.

you can easily access the result in your variable data like this

Expand|Select|Wrap|Line Numbers
  1. echo( "<p>${data['group_id']} - ${data['a']}</p>\n" );
  2.  
should do it inside your while loop.
Mar 3 '06 #2
Ok thanks ... i appreciate you taking the time to help!!
Mar 4 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Lenz Grimmer | last post: by
reply views Thread by Philip Stoev | last post: by
reply views Thread by Mike Chirico | last post: by
11 posts views Thread by DJJ | last post: by
39 posts views Thread by Mairhtin O'Feannag | last post: by
Atli
6 posts views Thread by Atli | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.