SitePoint Sponsor

User Tag List

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

    MultiDimensional Array Count

    This is how my db structure looks, i have around 350 rows like this one below each with a different number of comment_list and likes from friends.


    All i want is a query that counts how many times each variable comes up in the db.

    For example if we ran the query in this script we would get:

    1.) 65653171 (3 Times)
    2.) 67418530 (1 Time )

    etc...

    Any Ideas???


    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
                                    )
                                )
                            )

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    sorry, a database table does ~not~ look like a php array

    please show actual sample rows of data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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];
           
       }       
    }



    Code:
    That query would return: ( Each of these integers represent a unique user id )
    
    65653171
    67418530
    108832181
    58579401
    55125266
    60906602
    Now when i run this query on my DB i'll get around 500 different users id's, but lots will be duplicates.
    Some will appear 10 times, some 34 times, some 5 times.
    All i want is to calculate the number of times they each come up??
    Know what i'm saying??
    Last edited by claudfs; Nov 3, 2009 at 07:25. Reason: forgot to include sample row.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by claudfs View Post
    Know what i'm saying??
    no i don't!!!

    i understand the SQL -- and remember, this is the MySQL forum, not the php forum -- but the stuff that comes after it might as well be in sanskrit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lol sorry i just edited it to make more sense i'm sure you will understand now..

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by claudfs View Post
    All i want is to calculate the number of times they each come up??
    now i do
    Code:
    SELECT actor_id
         , COUNT(*) AS number_of_times
      FROM stream 
     WHERE source_id = ".$user."
    GROUP
        BY actor_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is for a Facebook Application, and when doing a FQL Query on the DB facebook does not allow GROUP BY and COUNT in the query it self..

    Is there another way to do it??

    Thanks

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by claudfs View Post
    facebook does not allow GROUP BY and COUNT in the query it self..
    well, ~that's~ pretty lame
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •