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

SQL Query Help - How Many Topics / Threads Per Category

Discussion in 'General PHP and MySQL Discussions' started by ProCom, May 18, 2016.

  1. ProCom

    ProCom Active Member

    Hey code masters!

    I have an advertiser that wants to know how many threads / topics there are in each forum "Category" and "Section".

    Sure, I could just go through and count on my homepage, but I like learning new things and thought running a SQL query via phpMyAdmin that would bring up the data would be better... especially since I could also put time boundaries on the data, for example:

    • Topics Per Category - Past 180 days
    • Topics Per Forum - Past 500 days

    ... problem is that I'm FAR from a SQL expert... which is why I come to you!

    So, is anyone willing to write the basic SQL query for the first or both of the queries above?

    Thanks in advance for your help!
     
  2. ProCom

    ProCom Active Member

    Ok, I paid to have this coded since I was in a hurry. Here are the basic queries:

    ---------THREADS PER NODE--------
    SELECT n.title, t.threads, n.parent_node_id
    FROM (
    SELECT node_id, COUNT( node_id ) AS 'threads'
    FROM xf_thread
    GROUP BY node_id
    )t, xf_node n
    WHERE n.node_id = t.node_id



    ---------THREADS PER PARENT CATEGORY NODE DATE RANGE----------

    SELECT n2.title, t3.total
    FROM (
    SELECT t2.parent_node_id, sum( t2.threads ) AS 'total'
    FROM (
    SELECT n.title, t.threads, n.parent_node_id
    FROM (
    SELECT node_id, COUNT( node_id ) AS 'threads'
    FROM xf_thread
    WHERE post_date < UNIX_TIMESTAMP( '2016-05-18' )
    AND post_date > UNIX_TIMESTAMP( '2016-04-01' )
    GROUP BY node_id
    )t, xf_node n
    WHERE n.node_id = t.node_id
    )t2
    GROUP BY t2.parent_node_id
    )t3, xf_node n2
    WHERE t3.parent_node_id = n2.node_id
     
    Vitor Dos Reis likes this.

Share This Page