SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Count and Group pretty tricky

    My PHP CODE:

    Code:
    $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];
           
       }       
    }
    Data is Shaped Like this:

    Code:
     [0] => Array
    
                    (
                        [viewer_id] => 65653171
                        [source_id] => 65653171
                        [actor_id] => 65653171
                        [comments] => Array
    
                            (
    
                                [comment_list] => Array
                                    (
                                        [0] => Array
                                            (
                                                [fromid] => 67418530
                                            )
                                        [1] => Array
                                            (
                                                [fromid] => 108832181
                                            )
                                    )
                            )
                            
                        [likes] => Array
                           
                           (
                                [friends] => Array
                                (
                                    [0] => 58579401
                                    [1] => 55125266
                                    [2] => 57449386
                                    [3] => 60906602
                                    )
                                )
                            )
     [1] => Array .... Next Row....
    When i Execute that php code on 1 row ie. The Array above. i get these results:

    Code:
    65653171
    67418530
    108832181
    58579401
    55125266
    60906602

    If i execute it on all my rows i get:

    Code:
    656531711
    1516783097
    674185730
    609066002
    656531711
    674185730
    1088321881
    585794601
    551252676
    574493836
    609066002
    674185730
    
    656531711
    656531711
    674185730
    656531711
    704318752
    609066002
    
    
    
    674185730
    656531711
    674185730
    532183012
    696220476
    674185730
    656531711
    532273835
    626576675
    626576675
    642160876
    609066002
    696220476
    656531711
    1117985931
    609066002
    609066002
    656531711
    626900263
    532273835
    626576675
    555457370
    656531711
    515868989
    674185730
    656531711
    1117985931
    670474941
    866155561
    656531711
    656531711
    674185730
    674185730
    704318752
    656531711
    625090469
    609066002
    656531711
    630999466
    585794601
    823541561
    630999466
    656531711
    656531711
    1254722891
    656531711
    607086243
    870955113
    656531711
    607086243
    609066002
    574493836
    656531711
    656531711
    656531711
    1254722891
    607086243
    1254722891
    1254722891
    609066002
    1254722891
    656531711
    574493836
    656531711
    610776430
    656531711
    656531711
    1254722891
    656531711
    630999466
    674185730
    1254722891
    656531711
    656531711
    674500606
    794502841
    625090469
    615311059
    626576675
    656531711
    656531711
    630999466
    609066002
    1083630614
    609066002
    674185730
    656531711
    630999466
    625090469
    656531711
    656531711
    656531711
    607086243
    630999466
    625090469
    727581485
    656531711
    696220476
    674185730
    674185730
    609066002
    656531711
    609066002
    656531711
    656531711
    656531711
    794502841
    626576675
    656531711
    607086243
    656531711
    1083630614
    609066002
    609066002
    100000192788370
    656531711
    607086243
    674185730
    674185730
    656531711
    607086243
    1798532390
    1083630614
    656531711
    1117985931
    577218458
    609066002
    656531711
    870955113
    656531711
    656531711
    656531711
    1083630614
    674185730
    609066002
    656531711
    656531711
    656531711
    643885079
    642160876
    656531711
    656531711
    1254722891
    577218458
    551252676
    607086243
    1254722891
    674185730
    870955113
    642160876
    656531711
    727581485
    727581485
    
    
    
    546980094
    560048573
    1254722891
    1516783097
    
    
    
    656531711
    577218458
    577218458
    656531711
    605612222
    1254722891
    656531711
    656531711
    674185730
    674185730
    674185730
    699890727
    656531711
    674185730
    656531711
    656531711
    674185730
    674185730
    532183012
    1792634671
    609066002
    605612222
    674185730
    674185730
    609066002
    564306576
    609066002
    
    609066002
    609066002
    630999466
    696220476
    674185730
    609066002
    609066002
    609066002
    609066002
    609066002
    609066002
    564306576
    674185730
    609066002
    674185730
    674185730
    609066002
    630999466
    605612222
    674185730
    609066002
    609066002
    638315931
    609066002
    609066002
    674185730
    656531711
    574493836
    609066002
    532273835
    674185730
    630999466
    609066002
    609066002
    674185730
    577218458
    1166813003
    562271102
    609066002
    794502841
    1259829408
    538357861
    704318752
    597991014
    566073511
    726971495
    532183012
    870955113
    616550716
    670472168
    664636476
    870740326
    727581485
    517942396
    1083630614
    598622230
    1254722891
    660941442
    699890727
    748556354
    674500606
    878905455
    585794601
    647590657
    823541561
    1144432391
    555457370
    661632754
    663243579
    617346399
    642160876
    642160876
    561991971
    674185730
    1226041688
    577290311
    573374896
    630863016
    696220476
    674185730
    609066002
    664944194
    609066002
    1083630614
    574493836
    801405141
    617346399
    574493836
    674185730
    870955113
    823541561
    664944194
    574493836
    674185730
    537328196
    609066002
    609066002
    574493836
    1254722891
    1254722891
    704318752
    674185730
    574493836
    574493836
    674185730
    574493836
    574493836
    586614836
    1254722891
    870955113
    710801556
    674185730
    660941442
    674185730
    664944194
    702018082
    1254722891
    674185730
    674185730
    870955113
    656531711
    609066002
    609066002
    866155561
    609066002
    724721156
    1083630614
    574493836
    660941442
    674185730
    605612222
    615311059
    660941442
    664944194
    609066002
    702018082
    878905455
    870955113
    609066002
    1169014029
    1083630614
    609066002
    1643126177
    609066002
    870955113
    870955113
    617454387
    682037670
    1516783097
    647590657
    866155561
    523797078
    585794601
    866155561
    551252676
    664636476
    870955113
    609066002
    586614836
    609066002
    585794601
    609066002
    560048573
    609066002
    870955113
    717511299
    664944194
    664944194
    720251764
    753679615
    753679615
    560048573
    674185730
    870955113
    870955113
    577290311
    609066002
    544352078
    609066002
    586614836
    1144432391
    878905455
    870955113
    577290311
    586614836
    866155561
    592660813
    592660813
    866155561
    536884770
    1254722891
    598622230
    626900263
    609066002
    609066002
    609066002
    866155561
    609066002
    605612222
    701683592
    866155561
    866155561
    670769071
    866155561
    866155561
    866155561
    866155561
    866155561
    866155561
    1371472943
    866155561
    866155561
    608643692
    608643692
    605612222
    626900263
    605612222
    674185730
    562271102
    605612222
    682661576
    682661576
    609066002
    609066002
    598622230
    866155561
    674185730
    608643692
    605612222
    654691620
    1135160691
    654691620
    870955113
    608643692
    753679615
    753679615
    605612222
    702018082
    870955113
    866155561
    608643692
    866155561
    870955113
    605612222
    605612222
    605612222

    Ok, what i would like is a query or a function that will work out how many times the same number appears.

    For example the number
    1.)609066002 appears 61 Times
    2.)674185730 appears 48 Times

    I'm really stuck on this one..

  2. #2
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why don't you let database do the job?
    For example, this query will give you info on actor_id
    SELECT actor_id, count(actor_id) FROM stream GROUP BY actor_id

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's a for a social application so 'count' and 'GROUP BY' are not supported in the SQL query.
    Is there another way around??

  4. #4
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Whats the connection between "social application" and SQL dialect?
    I can't believe there is somewhere SQL with no group by.
    Did you ever try it?

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup just tried it now its for a facebook app and you query there databases using FQL and they don't support GROUP BY and COUNT is it possible to do with php??

  6. #6
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you can write php code to arrange any data in any way. Sorting, grouping, filtering, counting, etc
    But it looks like weird to me. I hope amount of data to count is not too big...

  7. #7
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its only around 350 entries so its not to much data.
    What looks weird??

  8. #8
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Facebook politics looks weird to me.

    Okay, you don't have access to db tools and amount of data is reliable. And we'll hope it won't grow to 35000.

    First of all, do not make a giant structure which you don't even understand. Make a simple, plain array of your data.
    With this simple code
    PHP Code:
    $result mysql_query($query);
    while(
    $row=mysql_fetch_array$data[]=$row
    Now you can iterate array $data with foreach statement and do any evaluations you need.
    For example if you need to count actor_id appearances, do as simple thing as
    PHP Code:
    foreach ($data as $item) {
      
    $actors[$item['actor_id']]++;

    and then with
    PHP Code:
    foreach ($actors as $key => $value) {
      echo 
    "$key appears $value Times";

    you'll have what you need.

  9. #9
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks alot for your help it worked.. But 1 More Question.
    First of all i couldn't use this.
    Code:
    while($row=mysql_fetch_array)
    It doesn't work on Facebook, So i used my own code. This is my current code and its works:

    Code:
    $result = $facebook->api_client->stream_get('', $user, '', '', '500', '', '');
    
    for ($i = 0; $i < $count; $i++){
    
    $data[] = $result[posts][$i];
     
     }
    
    foreach ($data as $item) {
    $actors[$item['actor_id']]++;
    
    }  
    
    
    foreach ($actors as $key => $value) {
      
     echo "$key appears $value Times";
     echo '<br />';
    }
    Is it possible to include my other 2 arrays in the search to see how many times the UID appears overall through out all 3 Arrays??

    Hers is the code with the other 2 arrays

    Code:
    for ($i = 0; $i < $count; $i++){
    // ACTOR ID - FIRST ARRAY 
    $actor = $result[posts][$i][actor_id];
    // BUT we set it too
    $data[] = $result[posts][$i]
    
    
    // COMMENTS - SECOND ARRAY
    $count_c  = $result[posts][$i][comments][count];
    if($count_c > 2){
        $count_cc = '2';} else {
            $count_cc = $count_c;}
    if($count_cc > 0){
        for ($a = 0; $a < $count_cc; $a++){
    
    
              $comment_id = $result[posts][$i][comments][comment_list[$a][fromid];
    
        }
    }
     
      
     // GET LIKES - THIRD ARRAY
    $friends = $result[posts][$i][likes][friends];
    
     if ($friends != NULL){  
    $friends_count = count($friends);
     for ($b = 0; $b < $friends_count; $b++){
    
    
                $likes_id = $result[posts][$i][likes][friends][$b];
    
       }        
      }    
    }

    I know its a big piece of code, but its the only way i can filter it out on fb with out getting errors.
    So like i said Integers from the first array can appear in the second and third array as well.

    So is there away to include them in the foreach loops?

  10. #10
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do not understand data structure fb provides you.
    But I believe that code must be the same.
    also, foreach is not the only solution. for can be used as well.

  11. #11
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, Nevermind i figured it out Thanks alot.
    This is the last piece then my project is complete and its probably the simplest part.

    Ok here is my current code:

    Code:
    // Just cut out the main parts to make it easier to see
    $data[] .= $result[posts][$i][actor_id];
    $data[] .= $result[posts][$i][comments][comment_list][$a][fromid];          
    $data[] .= $result[posts][$i][likes][friends][$b];
    
    // Then i use your code
    foreach ($data as $item) {
    $actors[$item]++;
    
    }  
    
    
    foreach ($actors as $key => $value) {
      
     echo "$key appears $value Times";
     echo '<br />';
    }
    The code works but i was wondering how can i add a LIMIT and ORDER from most appearances to least. Then store those say for example 5 results into a variable or array?? So i can use it later in my script.

  12. #12
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't understand this code.
    May be it should be
    PHP Code:
    $data1[] = $result[posts][$i][actor_id];
    $data2[] = $result[posts][$i][comments][comment_list][$a][fromid];          
    $data3[] = $result[posts][$i][likes][friends][$b]; 
    ?
    And I must say, I did one unnecessary loop.
    it can be just
    PHP Code:
    $result $facebook->api_client->stream_get(''$user'''''500''''');
    for (
    $i 0$i $count$i++){
      
    $actors[$item['actor_id']]++;

    without additional foreach.

    To make top 5 is very easy too.
    you can sort arrays in PHP using sort() function and it's relatives
    asort() will be ok in this case.
    PHP Code:
    $actors=asort($actors); 
    after that you can display only 5 items using any loop or counter you wish.

  13. #13
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there is array_slice() function too
    which works much like LIMIT statement
    http://php.net/manual/en/ref.array.php is great place to take a brief look on what can be done to arrays in PHP


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •