[Logo] JForum - Powering Communities
  [Search] Search   [Recent Topics] Recent Topics   [Hottest Topics] Hottest Topics   [Top Downloads] Top Downloads   [Groups] Back to home page 
[Register] Register /  [Login] Login 


The final version of JForum 2.8.0 has been released with lots of improvements. Read all about it here

Extract Forum data in CSV or other content type - Questions per date, etc. RSS feed
Forum Index » User Forum
Author Message
Kevin


Joined: 2021/7/2
Messages: 13
Offline
Hi everyone!

I wanted to ask about extracting statistical forum data. JForum has boards and rankings with useful insights, but I was wondering if it would be possible to extract that data, let's say in CSV or XLS for Reports. I'm particularly interested in extracting information about topic answers raised per date.

Thanks!
udittmer


Joined: 2013/2/21
Messages: 366
Offline
No, JForum has nothing built in for extracting data, so the main option is to get down and dirty with SQL. If you can describe in a bit more detail what data you'd be interested in, I could whip up a few SQL queries to get you started.

Ping & Net - my free Android app for TCP/IP network diagnostics
[Email] [WWW]
Kevin


Joined: 2021/7/2
Messages: 13
Offline
Hi!

I'm interested in extracting information in CSV or XLS format about the following:
  • New topics raised by date and user

  • New answers raised by date and user

  • Views by date


  • An SQL script would be much appreciated, thanks!
    udittmer


    Joined: 2013/2/21
    Messages: 366
    Offline
    This gives you all topics created after a specific time, along with number of views, number of replies, the user who started the topic, and the text of the initial post:

    select t.topic_id, t.forum_id, t.topic_title, t.topic_time, t.topic_views, t.topic_replies, t.user_id, u.username, pt.post_text
    from jforum_topics t, jforum_users u, jforum_posts p, jforum_posts_text pt
    where t.user_id = u.user_id
    and t.topic_id = p.topic_id
    and p.post_id = pt.post_id
    and t.topic_time > '2021-01-01'
    and p.post_id = t.topic_first_post_id


    This gives you all replies after a specific time, along with the user name who posted the reply and the text of each reply:

    select t.topic_id, t.forum_id, t.topic_title, p.post_time, p.user_id, u.username, pt.post_text
    from jforum_topics t, jforum_posts p, jforum_posts_text pt, jforum_users u
    where t.user_id = u.user_id
    and t.topic_id = p.topic_id
    and p.post_id = pt.post_id
    and p.post_time > '2021-01-01'
    and p.post_id <> t.topic_first_post_id


    Views are only tracked per topic overall, not on a daily basis.

    Ping & Net - my free Android app for TCP/IP network diagnostics
    [Email] [WWW]
     
    Forum Index » User Forum
    Go to:   
    Mobile view
    Powered by JForum 2.8.0-b1 © 2021 JForum Team • Maintained by Andowson Chang and Ulf Dittmer