Announcement

Collapse
No announcement yet.

Who has posted the most?

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

  • Who has posted the most?

    I am going to be running a competition on my forum.

    I need a way of telling who has posted the most between now and this time next month.

    Is there any way to run a query of some sort or anything to determine who has made the most posts from 2 seperate dates?

    Many thanks,

    Andy
    http://www.eteknix.com/images/misc/vbulletin4_logo.png
    eTeknix.com - http://www.eteknix.com
    eTeknix Forums - http://forums.eteknix.com

  • #2
    There's a few variations about, but try starting here...

    http://www.vbulletin.com/forum/showt...Specifc-Period
    Lats...

    Comment


    • #3
      I would just run a SQL query in the database control panel of my web host since VB is a PITA to run one within the ACP. Since I write SQL for a living, I could write it for you in no time at all. What kind of a database are you using?

      Comment


      • #4
        A simple mysql database with the db name of:

        forums

        i want to run the query from 10th jan 2010 till 13th jan 2010.

        Thanks,

        Andy
        http://www.eteknix.com/images/misc/vbulletin4_logo.png
        eTeknix.com - http://www.eteknix.com
        eTeknix Forums - http://forums.eteknix.com

        Comment


        • #5
          Here's the query, but I am not sure about the part in bold since it is a Unix timestamp, which is something that I never use in my work. If the query works, you will bet a ranking.

          SELECT `username` ,count(`postid`) As "Post Count"
          FROM `vbpost`
          Where `dateline` Between '1263103200' And '1263448740'
          Group by `username`
          Order by 2 Desc,1

          Comment


          • #6
            Hi,

            It worked fantastically.

            Can i just ask what the dateline bit means so i can use this code again in future competitions at further dates.

            Many thanks,

            Andy
            http://www.eteknix.com/images/misc/vbulletin4_logo.png
            eTeknix.com - http://www.eteknix.com
            eTeknix Forums - http://forums.eteknix.com

            Comment


            • #7
              Originally posted by Blade-uk View Post
              Hi,

              It worked fantastically.

              Can i just ask what the dateline bit means so i can use this code again in future competitions at further dates.

              Many thanks,

              Andy
              It's the Unix date/time. I actually had to use an online converter for the values.

              Comment


              • #8
                Another relevant thread...

                http://www.vbulletin.com/forum/showt...1463-SQL-Query

                which shows the unix_timestamp function in use.
                Lats...

                Comment


                • #9
                  Thanks dspenc1, works a treat, really appreciated.

                  Thanks too Lats.

                  Andy
                  http://www.eteknix.com/images/misc/vbulletin4_logo.png
                  eTeknix.com - http://www.eteknix.com
                  eTeknix Forums - http://forums.eteknix.com

                  Comment


                  • #10
                    Originally posted by Blade-uk View Post
                    Thanks dspenc1, works a treat, really appreciated.

                    Thanks too Lats.

                    Andy
                    I wasn't sure how to convert the unixtimestamp into a real date since I don't use Unix date/times in my job. Thanks to Lats, mystery solved. The query below is more user friendly and it does work.

                    SELECT `username` ,count(`postid`) As "Post Count"
                    FROM `vbpost`
                    Where `dateline` Between unix_timestamp('2010/01/10') And unix_timestamp('2010/01/13')
                    Group by `username`
                    Order by 2 Desc,1

                    Incidentally, my forum is new and I am getting members, but the vast majority aren't posting. I have a contest where I am going to pick one post randomly for a prize. I will be using a SQL query to pick the winner.

                    Comment


                    • #11
                      Originally posted by dspenc1 View Post
                      I wasn't sure how to convert the unixtimestamp into a real date since I don't use Unix date/times in my job. Thanks fot Lats, mystery solved. The query below is more user friendly and it does work.

                      SELECT `username` ,count(`postid`) As "Post Count"
                      FROM `vbpost`
                      Where `dateline` Between unix_timestamp('2010/01/10') And unix_timestamp('2010/01/13')
                      Group by `username`
                      Order by 2 Desc,1

                      Incidentally, my forum is new and I am getting members, but the vast majority aren't posting. I have a contest where I am going to pick one post randomly for a prize. I will be using a SQL query to pick the winner.
                      Thanks matey, works a treat

                      Let me know when you mock that SQL query up as i could use that one too

                      Thanks again.

                      Andy
                      http://www.eteknix.com/images/misc/vbulletin4_logo.png
                      eTeknix.com - http://www.eteknix.com
                      eTeknix Forums - http://forums.eteknix.com

                      Comment


                      • #12
                        Originally posted by Blade-uk View Post
                        Thanks matey, works a treat

                        Let me know when you mock that SQL query up as i could use that one too

                        Thanks again.

                        Andy
                        SELECT `username` , `postid` ,`title`,`pagetext`
                        FROM `vbpost`
                        WHERE `dateline`
                        BETWEEN unix_timestamp( '2010/01/01' )
                        AND unix_timestamp( '2010/01/31' )
                        ORDER BY RAND( )
                        LIMIT 1


                        Comment

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