1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL query for post count in a forum during date range

Discussion in 'General PHP and MySQL Discussions' started by Baron, Oct 21, 2015.

  1. Baron

    Baron Member

    Does anybody have a SQL query handy that would allow you to calculate the number of posts that occurred in a specific forum during a given timeframe (such as last year for example)? Thanks in advance!
     
  2. Jim Boy

    Jim Boy Well-Known Member

    Off the top of my head, it would be something like:

    select count(*) from xf_post p, xf_thread t where p.thread_id=t.thread_id and t.node_id=[id of the forum in question] and p.post_date between unix_timestamp([start date/time]) and unix_timestamp([end date/time])
     
    Baron likes this.
  3. Baron

    Baron Member

    Thanks so much!!
     
  4. TheBigK

    TheBigK Well-Known Member

    Well, how do we go about fetching records from 'Last Month'? So if it's October, I want records from September. If it's January, I want records from December.

    For the sake of simplicity, let's just consider table with two columns:

    apples_sold | Date
    33 | 3 September 2015
    54 | 2 October 2015
    211 | 14 September 2015
    .
    .
    .
     
  5. Mouth

    Mouth Well-Known Member

    Code:
    select count(*)
    from xf_post p, xf_thread t
    where p.thread_id=t.thread_id and
    t.node_id=[id of the forum in question] and
    p.post_date between DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
     
    Baron, TheBigK and borbole like this.
  6. TheBigK

    TheBigK Well-Known Member

    Thanks @Mouth . I tried that in my query that tries to fetch the count of articles and username shared by our 'bloggers' in XenPorta. It's not working. Would really appreciate your inputs -

    Code:
    SELECT user.username, COUNT(articles.thread_id) AS article_countFROM xf_user AS user
    INNER JOIN xf_user_profile AS user_profile
    ON (user.user_id = user_profile.user_id)
    INNER JOIN ewrporta2_catlinks AS catlinks
    ON (catlinks.user_id = user.user_id)
    INNER JOIN ewrporta2_articles AS articles
    ON (articles.thread_id = catlinks.thread_id)
    WHERE MONTH(CURDATE() articles.article_date between DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
    GROUP BY user.user_id
    
     
  7. Mouth

    Mouth Well-Known Member

    Ahh, forgot that XF stores dates as integers and not datetime. Change to;

    Code:
    [...]
    where articles.article_date between UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')) AND UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59'));
    
     
    Last edited: Oct 31, 2015
    TheBigK likes this.
  8. TheBigK

    TheBigK Well-Known Member

    Tried that and XenForo shows following error -

    ErrorException: Fatal Error: syntax error, unexpected '%' - library/Report/Model/Index.php:18
    Generated By: Unknown Account, A moment ago

    My code:
    PHP:
    public function getAuthorWithPostCount()
    {
    return 
    $this->_getDb()->fetchPairs('
    SELECT user.username, COUNT(articles.thread_id) AS article_countFROM xf_user AS user
    INNER JOIN xf_user_profile as user_profile
    ON (user.user_id = user_profile.user_id)
    INNER JOIN EWRporta2_catlinks AS catlinks
    ON (catlinks.user_id = user.user_id)
    INNER JOIN EWRporta2_articles AS articles
    ON (articles.thread_id = catlinks.thread_id)
    WHERE articles.article_date BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, '
    %Y-%m-01 00:00:00')) AND UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59'))
    GROUP BY user.user_id
    '
    );
    }
     
  9. Mouth

    Mouth Well-Known Member

    The single quote mark to define the date format is closing out your SQL inside fetchpairs() before it's fully represented. You'll need to 'escape'(backslash) these single quotes. Try;
    Code:
    WHERE articles.article_date BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(NOW() - INTERVAL 1 MONTH, \'%Y-%m-01 00:00:00\')) AND UNIX_TIMESTAMP(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH), \'%Y-%m-%d 23:59:59\'))
     
  10. LaxmiSathy

    LaxmiSathy Member

    Hello,
    Am looking for a similar sql query such that in where conditional I enter the node id for the category say "Official Forums" - https://xenforo.com/community/#official-forums.1

    it lists all the nodes under this category and the post count in each of the node during the specific date range.

    Thanks.
     

Share This Page