Finding the member who made the nth post

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Skinny
    Member
    • Sep 2003
    • 81
    • 3.5.x

    Finding the member who made the nth post

    Is it possible to find which member made the nth post on my forum? We've just passed the 200,000 post mark and would like to know who made the 200,000th post.
  • WhSox21
    Member
    • May 2004
    • 70
    • 3.0.3

    #2
    SELECT username FROM post WHERE postid = 200000

    That will give you the username of that user I believe.
    PHP Tutorials - Mozilla Firefox :rolleyes:

    Comment

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

      #3
      Run this query on your database. This will return the username of the 200,000th poster:

      Code:
      SELECT username
      FROM post
      ORDER BY postid
      ASC
      LIMIT 199999, 1
      For some reason this query doesn't work in the vBulletin Admin CP, but it works fine in phpmyadmin if you have that installed on your server.

      Comment

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

        #4
        Originally posted by WhSox21
        SELECT username FROM post WHERE postid = 200000

        That will give you the username of that user I believe.
        That works too. It depends on the logic you want to use. Your query selects the post with postid 200,000. Mine selects the 200,000th post.

        For example, if you wanted to select the 10th post, but postids 5 and 6 were missing, then your query would return postid 10 while mine would return postid 12.

        Comment

        • Skinny
          Member
          • Sep 2003
          • 81
          • 3.5.x

          #5
          Thanks guys, I used both pieces of code. All depending on what way members regarded as being the true 200,000th post.

          Comment

          • zyuray
            Member
            • Jun 2002
            • 49

            #6
            Would it be possible to find out who made Xth post in a particular forum?

            Comment

            • Lats
              Senior Member
              • Mar 2002
              • 3671

              #7
              Originally posted by zyuray
              Would it be possible to find out who made Xth post in a particular forum?
              Limited testing on this however, it seems okay...
              Code:
              SELECT 
                  post.postid, 
                  post.username, 
                  post.pagetext, 
                  post.title, 
                  FROM_UNIXTIME(post.dateline,'%D %M %Y %h:%i') as post_made
              FROM 
                  post
              LEFT JOIN 
                  thread 
              ON 
                  thread.threadid = post.threadid
              LEFT JOIN 
                  forum 
              ON 
                  thread.forumid = forum.forumid
              WHERE 
                  forum.forumid = 8
              AND 
                  post.visible = 1
              ORDER BY 
                  postid
              LIMIT 50, 1
              ...finds the 50th post in forum 8.
              Lats...

              Comment

              • pgowder
                Senior Member
                • Mar 2001
                • 832

                #8
                What if we deleted lots of threads so the postid is off?

                How could I have it count the current posts to determined who posted x one?
                PowWows.com

                Comment

                • pgowder
                  Senior Member
                  • Mar 2001
                  • 832

                  #9
                  Originally posted by pgowder
                  What if we deleted lots of threads so the postid is off?

                  How could I have it count the current posts to determined who posted x one?
                  Anyone know?
                  PowWows.com

                  Comment

                  • peterska2
                    Senior Member
                    • Oct 2003
                    • 8869
                    • 3.7.x

                    #10
                    See Jakes post in post #3

                    Comment

                    • pgowder
                      Senior Member
                      • Mar 2001
                      • 832

                      #11
                      Originally posted by Jake Bunce
                      That works too. It depends on the logic you want to use. Your query selects the post with postid 200,000. Mine selects the 200,000th post.

                      For example, if you wanted to select the 10th post, but postids 5 and 6 were missing, then your query would return postid 10 while mine would return postid 12.
                      We finally hit the number where I needed to run this.

                      So I ran this. It spit out a user and post. But that post is several days earlier than when the stats at the bottom of vBulletin showed we hit 1,000,000 posts.

                      So what gives? Could that total be off?
                      PowWows.com

                      Comment

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