Query for users that post in a forum

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • hankster
    Senior Member
    • Feb 2002
    • 890

    Query for users that post in a forum

    I am trying to get a list of the members (and email address) that posted in a forum within the past 30 days. Does anyone know what query would be used to get that information. TIA
  • Wayne Luke
    vBulletin Technical Support Lead
    • Aug 2000
    • 73976

    #2
    You can try the following:

    Code:
    select distinct user.userid, user.username, user.email from user
      join post on (user.userid = post.userid)
      join thread on (post.threadid = thread.threadid)
      where thread.forumid = XX AND post.dateline >= DATE_SUB(NOW(), INTERVAL 30 DAY);
    Replace XX with the forum ID. Also if you're using a table prefix, you would need to update all the table names to reflect this. user, post and thread are table names. You would need to update every occurrence.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment

    • hankster
      Senior Member
      • Feb 2002
      • 890

      #3
      Thanks Wayne,

      Can I use that through the vB QSL Query admin?

      Comment

      • Wayne Luke
        vBulletin Technical Support Lead
        • Aug 2000
        • 73976

        #4
        Should be able to if you have permissions set.
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API

        Comment

        • hankster
          Senior Member
          • Feb 2002
          • 890

          #5
          That is what I thought but I'm not getting any results.

          Comment

          • Riasat
            Senior Member
            • Aug 2006
            • 4013

            #6
            dont forget to add your table prefixes.

            Comment

            • Wayne Luke
              vBulletin Technical Support Lead
              • Aug 2000
              • 73976

              #7
              Also make sure to change the XX in the query to the forumid you want to check.
              Translations provided by Google.

              Wayne Luke
              The Rabid Badger - a vBulletin Cloud demonstration site.
              vBulletin 5 API

              Comment

              • Simon Lloyd
                Senior Member
                • Apr 2008
                • 610
                • 3.7.x

                #8
                Hi Wayne, i ran this:
                select distinct vb_user.userid, vb_user.username, vb_user.email from vb_user
                join vb_post on (vb_user.userid = vb_post.userid)
                join vb_thread on (vb_post.threadid = vb_thread.threadid)
                where vb_thread.forumid = 65 AND vb_post.dateline >= DATE_SUB(NOW(), INTERVAL 30 DAY)


                And had no returns, thats my busiest forum, maybe i've missed something like the OP, i'm using vb3.8.6
                Kind regards,
                Simon
                Microsoft Office Discussion

                Comment

                • Wayne Luke
                  vBulletin Technical Support Lead
                  • Aug 2000
                  • 73976

                  #9
                  Hmm.. Works on my test database or I wouldn't have posted it. What versions of MySQL are you using?
                  Translations provided by Google.

                  Wayne Luke
                  The Rabid Badger - a vBulletin Cloud demonstration site.
                  vBulletin 5 API

                  Comment

                  • hankster
                    Senior Member
                    • Feb 2002
                    • 890

                    #10
                    Here is what I ended up using

                    Code:
                    [FONT=Courier New][URL="https://www.diecastwings.net:2087/3rdparty/phpMyAdmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.1%2Fen%2Fselect.html&token=f8b10d0cc27fc6c2024241aedc72db16"]SELECT[/URL] [/FONT][FONT=Courier New]DISTINCT [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]userid[/FONT][FONT=Courier New], [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]username[/FONT][FONT=Courier New], [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]email[/FONT][FONT=Courier New]
                    [/FONT][FONT=Courier New]FROM [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New]
                    [/FONT][FONT=Courier New]JOIN [/FONT][FONT=Courier New]post [/FONT][FONT=Courier New]ON [/FONT][FONT=Courier New]( [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]userid [/FONT][FONT=Courier New]= [/FONT][FONT=Courier New]post[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]userid [/FONT][FONT=Courier New])[/FONT][FONT=Courier New]
                    [/FONT][FONT=Courier New]JOIN [/FONT][FONT=Courier New]thread [/FONT][FONT=Courier New]ON [/FONT][FONT=Courier New]( [/FONT][FONT=Courier New]post[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]threadid [/FONT][FONT=Courier New]= [/FONT][FONT=Courier New]thread[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]threadid [/FONT][FONT=Courier New])[/FONT][FONT=Courier New]
                    [/FONT][FONT=Courier New]WHERE [/FONT][FONT=Courier New]thread[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]forumid [/FONT][FONT=Courier New]=[/FONT][FONT=Courier New]17[/FONT][FONT=Courier New]
                    [/FONT][B][COLOR=red][FONT=Courier New]AND [/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New]post[/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New].[/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New]dateline [/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New]>=[/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New]1315526400[/FONT][/COLOR][/B][FONT=Courier New]
                    [/FONT][FONT=Courier New]LIMIT [/FONT][FONT=Courier New]0 [/FONT][FONT=Courier New], [/FONT][FONT=Courier New]30
                    [/FONT]


                    1315526400 is unix time for 9/9/2011 at 00:00:00

                    Use this site http://www.onlineconversion.com/unix_time.htm to convert for other dates…

                    Comment

                    • Wayne Luke
                      vBulletin Technical Support Lead
                      • Aug 2000
                      • 73976

                      #11
                      Hmm.. wonder if date_sub is broken on your version of MySQL then.
                      Translations provided by Google.

                      Wayne Luke
                      The Rabid Badger - a vBulletin Cloud demonstration site.
                      vBulletin 5 API

                      Comment

                      • hankster
                        Senior Member
                        • Feb 2002
                        • 890

                        #12
                        Running 5.1.56

                        Comment

                        • Simon Lloyd
                          Senior Member
                          • Apr 2008
                          • 610
                          • 3.7.x

                          #13
                          Mysql 5 community
                          Kind regards,
                          Simon
                          Microsoft Office Discussion

                          Comment

                          • Wayne Luke
                            vBulletin Technical Support Lead
                            • Aug 2000
                            • 73976

                            #14
                            Hmmm. I am running 5.5.14 Community. Sorry about the query. I am glad you got it working though.
                            Translations provided by Google.

                            Wayne Luke
                            The Rabid Badger - a vBulletin Cloud demonstration site.
                            vBulletin 5 API

                            Comment

                            • hankster
                              Senior Member
                              • Feb 2002
                              • 890

                              #15
                              Thanks for the help. Got me on the right track.

                              Comment

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