Query needed to determine whom is the 1,000,000th post?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Xyabber
    Senior Member
    • Dec 2004
    • 219

    Query needed to determine whom is the 1,000,000th post?

    Hi all -

    Anyone have a query to find out what post on my forum is the 1,000,000th post?

    Thanks in advance,

    xyabber

    (I tried searching for the answer to no avail.)
  • Zachery
    Former vBulletin Support
    • Jul 2002
    • 59097

    #2
    Well, easiest way is showthread.php?p=1000000

    That is the true one millionth post, this won't take into account deleted, or moderated posts.

    Comment

    • Xyabber
      Senior Member
      • Dec 2004
      • 219

      #3
      Easy is good!
      However, when I try this it is showing me a thread from over a year ago? Stats panel says I am not quite there yet (999,231)

      Thanks for the quick reply and help by the way.

      Comment

      • Zachery
        Former vBulletin Support
        • Jul 2002
        • 59097

        #4
        If you get lucky, lats might showup and give a query, I would suggest trying to google vB.com (so site:vbulletin.com query millionth post or some such) and you might get lucky. My quick search didnt turn up any results.

        Comment

        • Arkham
          Senior Member
          • Mar 2001
          • 2233
          • 4.1.x

          #5
          Originally posted by Zachery
          Well, easiest way is showthread.php?p=1000000

          That is the true one millionth post, this won't take into account deleted, or moderated posts.
          ...Aaand on that note:

          Belated Congratulations to Manager Josh!

          Comment

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

            #6
            You can try a query like:

            select username from post limit 999999,1;

            Should pull the 1 Millionth record from the table. The offset of the first row is 0 so 999999 would pull 999999+1.

            If you use a table prefix, you need to adjust the table name to account for it.
            Translations provided by Google.

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

            Comment

            • Xyabber
              Senior Member
              • Dec 2004
              • 219

              #7
              Thanks Wayne. Does this take into account deletions and such as Zachary mentioned?

              Also, is there a way to get the thread URL? Ideally, I'd love to point to it in my announcement.

              Thanks all. I really appreciate the help.

              Comment

              • Xyabber
                Senior Member
                • Dec 2004
                • 219

                #8
                Originally posted by Wayne Luke
                You can try a query like:

                select username from post limit 999999,1;

                Should pull the 1 Millionth record from the table. The offset of the first row is 0 so 999999 would pull 999999+1.

                If you use a table prefix, you need to adjust the table name to account for it.
                Wayne your query is a bit off I think?
                Results: 1,009,002 (0.0006s), Page 1 of 50451

                Comment

                • Zachery
                  Former vBulletin Support
                  • Jul 2002
                  • 59097

                  #9
                  It pulls the current 1 millionth record, so it'd be pretty accurate.

                  Comment

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

                    #10
                    Originally posted by Xyabber
                    Wayne your query is a bit off I think?
                    Results: 1,009,002 (0.0006s), Page 1 of 50451
                    Should only pull one record as I wrote it.
                    Translations provided by Google.

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

                    Comment

                    • Xyabber
                      Senior Member
                      • Dec 2004
                      • 219

                      #11
                      I got nearly the same result set when I tried that again.

                      In a former life I did mysql at my day job. I know enough to be dangerous.
                      After doing a 'desc' on post table I tried the following:

                      Code:
                      mysql> SELECT username, threadid FROM post WHERE postid = 1000000;
                      +----------+----------+
                      | username | threadid |
                      +----------+----------+
                      | MoJoe    |    97759 |
                      +----------+----------+
                      1 row in set (0.00 sec)
                      Is this accurate? Thing that is confusing is that my stats panel says I have 999,441 posts.

                      Edit - FYI looking at stats panel template it is using:
                      Code:
                      $vbphrase[posts]: $totalposts
                      Looking at stats.php in modules dir for vba I see:
                      Code:
                                      $totalthreads = 0;
                                      $totalposts = 0;
                      
                                      $getforumstats = $db->query_read("
                                              SELECT threadcount, replycount
                                              FROM " . TABLE_PREFIX . "forum
                                              $inforumquery
                                      ");
                                      while ($fstats = $db->fetch_array($getforumstats))
                                      {
                                              $totalthreads += $fstats['threadcount'];
                                              $totalposts += $fstats['replycount'];
                                      }
                      
                                      $db->free_result($getforumstats);
                                      unset($fstats);
                      
                                      $totalthreads = vb_number_format($totalthreads);
                                      $totalposts = vb_number_format($totalposts);
                      Last edited by Xyabber; Thu 26 Jan '12, 7:33pm.

                      Comment

                      • Andy
                        Senior Member
                        • Jan 2002
                        • 5886
                        • 4.1.x

                        #12
                        You can also use phpmyadmin.

                        Show 1 row(s) starting from record # 999999

                        Comment

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

                          #13
                          SELECT username, threadid FROM post WHERE postid = 1000000;

                          The above is not accurate if you have ever deleted a post in the life of the forum. postid is an auto-increment field. Deleting posts just leaves gaps in the postid column. Ids are not reused.
                          Translations provided by Google.

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

                          Comment

                          • Lats
                            Senior Member
                            • Mar 2002
                            • 3671

                            #14
                            Limited testing as I don't have a million posts on my test account, but this adds the number of deleted posts to the postid...
                            Code:
                            SELECT 
                             username, 
                             postid, 
                             threadid 
                            FROM 
                             post 
                            WHERE 
                             postid = (SELECT (1000000 + (max(postid) - count(postid))) FROM post);
                            Lats...

                            Comment

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