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

XF 1.1 Help with database queries

Discussion in 'XenForo Questions and Support' started by Antonio Coelho, Oct 24, 2013.

  1. Hi all,

    I run a private forum where users are supposed to write at least their presentation in two weeks time after registration and any user with 0 (zero) posts within 3 months of registration are deactivated.

    Is there any way to query the database for:

    1. Users registered within the last 3 months with 0 (zero) posts ?

    2. Users registered within the last two weeks to have started a topic on xf_thread.thread_id = 9 ?

    Thanks in advance for your help.
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    1)

    Code:
    SELECT *
    FROM xf_user
    WHERE register_date > UNIX_TIMESTAMP('yyyy-mm-dd 00:00:00')
    AND message_count = 0
    
    2)

    Code:
    SELECT u.*
    FROM xf_post AS p
    LEFT JOIN xf_user AS u ON (u.user_id = p.user_id)
    WHERE u.register_date > UNIX_TIMESTAMP('yyyy-mm-dd 00:00:00')
    AND p.thread_id = 9
    
     
    Liam W likes this.
  3. Thanks Jake :)
     
  4. Hi Jake,

    For some reason query 2 is not working as expected, but I notice that was my mistake.

    Should be:

    2. Users registered within the last two weeks that have NOT started a topic on xf_thread.thread_id = 9 ?

    Can you please help again?

    Thanks mate.
     
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    2)

    Code:
    SELECT u.*
    FROM xf_user AS u
    LEFT JOIN xf_post AS p ON (p.user_id = u.user_id AND p.thread_id = 9)
    WHERE u.register_date > UNIX_TIMESTAMP('yyyy-mm-dd 00:00:00')
    AND p.post_id IS NULL;
    
     
    Liam W likes this.
  6. Thanks you so much Jake (again!) :)
     

Share This Page