Announcement

Collapse
No announcement yet.

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

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

  • 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.)

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


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


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


        • #5
          Originally posted by Zachery View Post
          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


          • #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 - Full / Mobile
            Vote for your favorite feature requests and the bugs you want to see fixed.

            Comment


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


              • #8
                Originally posted by Wayne Luke View Post
                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


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

                  Comment


                  • #10
                    Originally posted by Xyabber View Post
                    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 - Full / Mobile
                    Vote for your favorite feature requests and the bugs you want to see fixed.

                    Comment


                    • #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 26th Jan '12, 8:33pm.

                      Comment


                      • #12
                        You can also use phpmyadmin.

                        Show 1 row(s) starting from record # 999999

                        Comment


                        • #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 - Full / Mobile
                          Vote for your favorite feature requests and the bugs you want to see fixed.

                          Comment


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