SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict tina88's Avatar
    Join Date
    Jan 2007
    Location
    UK
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    choose most active member based on 4 tables

    Hiya, not really sure how to explain this so if it doesn't make sense tell me What I want to do is display the member of the month on my homepage. This will be based on the member who has been most active throughout the previous month. I am aiming to measure activity by creating a select query that counts the number of times a member ID occurs in 4 different tables. The member with the highest count will have their details displayed in a section on the homepage.

    What I need help with is how would I write a query to count mID over 4 tables for each member and select the member with the highest number so I can query the user table to obtain their details.

    I don't have access to test this at work, but here is something I wrote about 5 minutes ago I realise this is incorrect but it may contain some of the required elements?

    PHP Code:
    $sql mysql_query("SELECT COUNT(tblgallery.uID,tbllogs.uID,tblfthread.uID,tblfcomment.uID) AS uID 
        WHERE tblgallery.uID = tbllogs.uID AND tbllogs.uID = tblfthread.uID AND tblfthread.uID = tblfcomment.uID
        ORDER BY uID DESC LIMIT 1"
    );
    $row mysql_fetch_array($sql);
    $uID $row['uID'];

    $sql2 mysql_query("SELECT uusername, uavatar FROM tbluser WHERE uID = $uID");
    $row2 mysql_fetch_array($sql2);
    $uusername $row2['uusername'];
    $uavatar $row2['uavatar'];

    echo 
    '<a href="profile.php?uID='.$uID.'"><img src="images/members/'.$uID.'/'.$uavatar.'" alt="'.$uusername.'" />
    <br />'
    .$uusername.'</a>'
    Please can someone offer some advice for this.

    Thanx, Tina

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    function MostActive() {
      
    $member['id'] = "";
      
    $member['count'] = 0;

      
    $tables = array( "table1""table2""table3""table4" );

      
    $query "select userid from members";
      
    $roster mysql_query$query );

      foreach ( 
    $roster as $id ) {
          
    $ttl 0;
          foreach ( 
    $tables as $t ) {
              
    $query "select count( $id ) from $t where timestamp >= DATE_SUB(CURDATE(),INTERVAL -30 DAY) and userid = $id";
              
    $rslt mysql_query$query );
              if ( 
    $rslt ) { 
                  
    $row mysql_fetch_array$rslt );
                  
    $ttl += $row[0] ;
              }
          }
          if ( 
    $ttl $member['count'] ) {
              
    $member['id'] = $id;
              
    $member['count'] = $ttl;
          }
      }
      return 
    $member;

    I am not sure the date part of this function will work, but I think you get the general ideal here. You said member of the month so you need to use some type of time constraint. This is untested but should work. The return will be an array with a member ID and the count, you should be able to get the avatar with the user ID. Please note also for brevity sake I left out the code to open your databases, close them and release the recordset's.
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  3. #3
    SitePoint Addict tina88's Avatar
    Join Date
    Jan 2007
    Location
    UK
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hiya, I have just been testing this code but I can't get it to work. I have modified it to fit my table structure:
    PHP Code:
    function MostActive() { 
      
    $member['uID'] = ""
      
    $member['count'] = 0

      
    $tables = array("tblgallery""tbllogs""tblfthread""tblfcomment"); 

      
    $sql "SELECT uID FROM tbluser"
      
    $roster mysql_query($sql); 

      foreach(
    $roster AS $uID) { 
          
    $ttl 0
          foreach(
    $tables AS $t) { 
              
    $sql2 "SELECT COUNT($uID) FROM $t WHERE timestamp >= DATE_SUB(CURDATE(),INTERVAL -30 DAY) AND uID = $uID"
              
    $rslt mysql_query($sql2); 
              if (
    $rslt) { 
                  
    $row mysql_fetch_array($rslt); 
                  
    $ttl += $row[0] ; 
              } 
          } 
          if (
    $ttl $member['count']) { 
              
    $member['uID'] = $uID
              
    $member['count'] = $ttl
          } 
      } 
      return 
    $member
    }

    MostActive(); 
    It is giving me an error though
    Warning: Invalid argument supplied for foreach() in C:\wamp\www\site\index.php on line 26
    Line 26 is
    PHP Code:
    foreach($roster AS $uID) { 
    I don't see the problem with this though?

    Thanx, Tina

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    how about just one query...
    Code:
    SELECT tbluser.uID 
         , tbluser.uName
         , tbluser.otherstuff
         , D2.total
      FROM tbluser
    INNER
      JOIN (
           SELECT uID
                , sum(subtotal) as total
             FROM (
                  SELECT uID,count(*) as subtotal
                    FROM tblgallery GROUP BY uID
                  UNION ALL
                  SELECT uID,count(*) as subtotal
                    FROM tbllogs GROUP BY uID
                  UNION ALL
                  SELECT uID,count(*) as subtotal
                    FROM tblfthread GROUP BY uID
                  UNION ALL
                  SELECT uID,count(*) as subtotal
                    FROM tblfcomment GROUP BY uID
                  ) as D
           GROUP BY uID
           ) as D2
        ON D2.uID = tbluser.uID
    ORDER BY total DESC LIMIT 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict tina88's Avatar
    Join Date
    Jan 2007
    Location
    UK
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanx for that query code but I can't get it to work. I have never used joins before and am not sure where the D2.total comes from? This is what I ended up with
    PHP Code:
    $sql mysql_query("SELECT tbluser.uID 
         , tbluser.uusername
         , D2.total
      FROM tbluser
    INNER
      JOIN (
           SELECT uID
                , sum(subtotal) as total
             FROM (
                  SELECT uID,count(*) as subtotal
                    FROM tblgallery GROUP BY uID
                  UNION ALL
                  SELECT uID,count(*) as subtotal
                    FROM tbllogs GROUP BY uID
                  UNION ALL
                  SELECT uID,count(*) as subtotal
                    FROM tblfthread GROUP BY uID
                  UNION ALL
                  SELECT uID,count(*) as subtotal
                    FROM tblfcomment GROUP BY uID
                  ) as D
           GROUP BY uID
           ) as D2
        ON D2.uID = tbluser.uID
    ORDER BY total DESC LIMIT 1"
    );
    $row mysql_fetch_array($sql);
    $uusername $row['uusername'];

    echo 
    'username = '.$uusername
    When I use this I get an error
    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\site\index.php on line 75
    Line 75 is
    PHP Code:
    $row mysql_fetch_array($sql); 
    Thanx, Tina

  6. #6
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My apologies, I see the problem. $roster is a recordset not an array. We need to go one step further.

    PHP Code:
      $sql "SELECT uID FROM tbluser"
      
    $roster mysql_query($sql); 

      while (
    $row nysql_fetch_array$roster ) ) {
         
    $id $row[0]; 
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    please run the query outside of php, i.e. directly in mysql, and let's see the real error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict tina88's Avatar
    Join Date
    Jan 2007
    Location
    UK
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    EDITI made a typo on one of my table names which was causing the problem. The code now works perfectly. Thanks



    Hiya, sorry I have not been back to this thread sooner, I have been working on other projects. I ran the code in MySQL and this is the error I got:

    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = mysql_query("SELECT tbluser.uID
    , tbluser.uusername
    , D2.tot' at line 1
    As far as I can tell, the code looks fine to me. Please can you have a look at my code below.

    PHP Code:
    $sql mysql_query("SELECT tbluser.uID 
         , tbluser.uusername
         , D2.total
      FROM tbluser
    INNER
      JOIN (
           SELECT uID
                , sum(subtotal) as total
             FROM (
                  SELECT uID,count(*) as subtotal
                    FROM tblgallery GROUP BY uID
                  UNION ALL
                  SELECT uID,count(*) as subtotal
                    FROM tbllogs GROUP BY uID
                  UNION ALL
                  SELECT uID,count(*) as subtotal
                    FROM tblfthread GROUP BY uID
                  UNION ALL
                  SELECT uID,count(*) as subtotal
                    FROM tblfcomments GROUP BY uID
                  ) as D2
           GROUP BY uID
           ) as D2
        ON D2.uID = tbluser.uID
    ORDER BY total DESC LIMIT 1"
    );
    $row mysql_fetch_array($sql);
    $uusername $row['uusername'];

    echo 
    'username = '.$uusername
    Thanks, Tina
    Last edited by tina88; Aug 26, 2007 at 09:31.


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
  •