Announcement

Collapse
No announcement yet.

Query for users that post in a forum

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #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 customization and demonstration site.
    vBulletin 5 Documentation - Updated every Friday. Report issues here.
    vBulletin 5 API - Full / Mobile
    I am not currently available for vB Messenger Chats.

    Comment


    • #3
      Thanks Wayne,

      Can I use that through the vB QSL Query admin?

      Comment


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

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud customization and demonstration site.
        vBulletin 5 Documentation - Updated every Friday. Report issues here.
        vBulletin 5 API - Full / Mobile
        I am not currently available for vB Messenger Chats.

        Comment


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

          Comment


          • #6
            dont forget to add your table prefixes.

            Comment


            • #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 customization and demonstration site.
              vBulletin 5 Documentation - Updated every Friday. Report issues here.
              vBulletin 5 API - Full / Mobile
              I am not currently available for vB Messenger Chats.

              Comment


              • #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


                • #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 customization and demonstration site.
                  vBulletin 5 Documentation - Updated every Friday. Report issues here.
                  vBulletin 5 API - Full / Mobile
                  I am not currently available for vB Messenger Chats.

                  Comment


                  • #10
                    Here is what I ended up using

                    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 =17
                    AND post.dateline >=1315526400
                    LIMIT 0 , 30
                    


                    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


                    • #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 customization and demonstration site.
                      vBulletin 5 Documentation - Updated every Friday. Report issues here.
                      vBulletin 5 API - Full / Mobile
                      I am not currently available for vB Messenger Chats.

                      Comment


                      • #12
                        Running 5.1.56

                        Comment


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

                          Comment


                          • #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 customization and demonstration site.
                            vBulletin 5 Documentation - Updated every Friday. Report issues here.
                            vBulletin 5 API - Full / Mobile
                            I am not currently available for vB Messenger Chats.

                            Comment


                            • #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...
                              X