Posts per Week

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Wistow
    New Member
    • Feb 2005
    • 13
    • 3.0.7

    Posts per Week

    Hi

    Is there any way I can see which user made the most posts in a given date range? i am planning on giving domain names away every week to the highest posters, and need to know this information.

    I have checked in the admincp under stats but it doesn't seem to show this info, only the number of posts. (not who posted)

    Kind Regards,

    Craig
  • Lats
    Senior Member
    • Mar 2002
    • 3671

    #2
    Check this thread...

    Hi guys, can you help me? To increase number of posts on my forum I'd like to give little gift to the most active users. I would like to run a query which should list all user and report the number of posts of each one in a certain period: for example from July 1st to July 31st Then it should be better to order them by the
    Lats...

    Comment

    • Jake Bunce
      Senior Member
      • Dec 2000
      • 46598
      • 3.6.x

      #3
      This query should work:

      Code:
      SELECT COUNT(postid) AS count, user.username
      FROM post
      LEFT JOIN user ON (user.userid = post.userid)
      WHERE dateline > UNIX_TIMESTAMP('[color=red]2004-01-01[/color]') AND dateline < UNIX_TIMESTAMP('[color=red]2005-01-01[/color]')
      GROUP BY post.username
      ORDER BY count
      DESC
      Change the dates appropriately. This will return a list of usernames and post counts for the time period specified.

      You can run this query in your:

      Admin CP -> Import & Maintenance -> Execute SQL Query

      You must have permission to execute queries in your includes/config.php file:

      // ****** USERS WITH QUERY RUNNING PERMISSIONS ******
      // The users specified here will be allowed to run queries from the control panel.
      // See the above entries for more information on the format.
      // Please note that the ability to run queries is quite powerful. You may wish
      // to remove all user IDs from this list for security reasons.
      $canrunqueries = '';

      Comment

      • my3shadows
        New Member
        • Dec 2004
        • 13
        • 3.8.x

        #4
        What would I need to add to this sql statement to exclude certain forum ids? I want to do what the original poster is doing, except posts in certain forums don't count toward the prizes.

        Thanks!

        Rachael
        Rachael

        Expressive Parents.com

        Comment

        • Jake Bunce
          Senior Member
          • Dec 2000
          • 46598
          • 3.6.x

          #5
          Code:
          SELECT COUNT(postid) AS count, user.username
          FROM post
          LEFT JOIN user ON (user.userid = post.userid)
          [color=blue]LEFT JOIN thread ON (thread.threadid = post.threadid)[/color]
          WHERE [color=blue]post.[/color]dateline > UNIX_TIMESTAMP('[color=red]2004-01-01[/color]') AND [color=blue]post.[/color]dateline < UNIX_TIMESTAMP('[color=red]2005-01-01[/color]')
          [color=blue]AND forumid NOT IN (X,Y,Z)[/color]
          GROUP BY post.username
          ORDER BY count
          DESC
          I added the blue code. X,Y,Z is a comma-separated list of excluded forumids.

          Comment

          • my3shadows
            New Member
            • Dec 2004
            • 13
            • 3.8.x

            #6
            Thank you! You have no idea how much easier you just made my day!
            Rachael

            Expressive Parents.com

            Comment

            • JD45
              New Member
              • May 2006
              • 29
              • 3.5.x

              #7
              Could this be moved to suggestions? A more full featured admin search would be nice.

              Comment

              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
              Working...