MySql PHP, count and group by question pretty tricky

[claudfs]

Hey Guys,

Ok here is my problem:

There is around 350 rows in the db.

All the variables $actor_id, $comment_id and $likes_id are all uid's for facebook users

Now i want to group and sum how many entries there are of each unique uid

Say for example the number:

'65653171' came up
10 times as a $actor_id
5 times as a $comment_id
3 times as a $likes_id

and

'67418530' came up
5 times as a $actor_id
21 times as a $comment_id
3 times as a $likes_id

Then i want a query that would display in order of most entries:
1.) 67418530 (29)
2.) 65653171 (18)


Thanks for the help, real brain teazer for me.


This is the code i use to fetch the Data from the FQL Table, a preview of the FQL Table can been seen on the bottom of this thread.

 $query = "SELECT post_id, actor_id, comments, likes FROM stream WHERE source_id = ".$user." LIMIT 500";
$result = mysql_query($query);
$count = count($result);
$total=$count;
 
 
 for ($i = 0; $i < $count; $i++)
 {
 
 $actor_id = $result[$i][actor_id];
$count_c  = $result[$i][comments][count];
 
if($count_c > 0){
    for ($a = 0; $a < $count_c; $a++){
          $comment_id = $result[$i][comments][comment_list][$a][fromid];
 
    }
}
 
 
$count_l  = $result[$i][likes][count];
 
if($count_l > 0){        
        for ($b = 0; $b < $count_l; $b++){
            $likes_id = $result[$i][likes][friends][$b];
 
   }        
}
 
 
 
 
}

This is how the information is stored in the DB

[posts] => Array
 
        (
 
            [0] => Array
 
                (
 
                    [post_id] => 65653171_199696556137
 
                    [viewer_id] => 65653171
 
                    [source_id] => 65653171
 
                    [type] => 46
 
                    [app_id] => 291512034
 
                    [attribution] => via <a href="/mobile/?v=web">Mobile Web</a>
 
                    [actor_id] => 65653171
 
                    [target_id] => 
 
                    [message] => is just trying to be a nice guy :P stop hating on me.
 
                    [attachment] => 
 
                    [app_data] => 
 
                    [action_links] => 
 
                    [comments] => Array
 
                        (
 
                            [can_remove] => 1
 
                            [can_post] => 1
 
                            [count] => 2
 
                            [comment_list] => Array
 
                                (
 
                                    [0] => Array
 
                                        (
 
                                            [fromid] => 67418530
 
                                            [time] => 125705944
 
                                            [text] => Y these ppl hating on yu bebe :) miss yu sooo much!!
 
                                            [id] => 65653171_199696556137_6751049
 
                                        )
 
 
 
                                    [1] => Array
 
                                        (
 
                                            [fromid] => 108832181
 
                                            [time] => 1257186632
 
                                            [text] => yo
 
                                            [id] => 65653171_199696556137_6802381
 
                                        )
 
 
 
                                )
 
 
 
                        )
 
 
 
                    [likes] => Array
 
                        (
 
                            [href] => http://www.facebook.com/social_graph.php?node_id=19966556137&class=LikeManager
 
                            [count] => 4
 
                            [sample] => 
 
                            [friends] => Array
 
                                (
 
                                    [0] => 58579401
 
                                    [1] => 55125266
 
                                    [2] => 57449386
 
                                    [3] => 60906602
 
                                )
 
 
 
                            [user_likes] => 0
 
                            [can_like] => 1
 
                        )
 
 
 
                    [privacy] => Array
 
                        (
 
                            [value] => NOT_EVERYONE
 
                        )
 
 
 
                    [updated_time] => 1257186632
 
                    [created_time] => 1257057706
 
                    [tagged_ids] => 
 
                    [is_hidden] => 0
 
                    [filter_key] => 
 
                    [permalink] => http://www.facebook.com/profile.php?id=65653171&v=feed&story_fbid=19969655137
 
                )
 
 
 
            [1] => Array
 
                ( .........Next entry same format as top........

[TheServant]

I am assuming that it will grow beyond 350 rows.

Besides making a rediculous query with lots of filtering you can't do this in one step. It would be way too resource intensive even if you could if it greew to say 10,000 rows.
I suggest this:
Have another column called entry_totalizer or something and then you can ORDER BY that and display that instead of doing a whole lot of COUNTing. Every time a comment_id or actor_id etc is entered, a filter can add one to the entry_totalizer .

With the ones that you already have, you might need to write a simple script to update those already in the table by searching for the empty ones and then doing the math and updating the value for each. If you are worried about resources, LIMIT it to 50 at a time, but I suspect that only having 350 will not be a problem.