XF 1.1 Help with database queries

[Antonio Coelho]

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.

 

[Jake Bunce]

1)

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

2)

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

 

[Antonio Coelho]

Thanks Jake

 

[Antonio Coelho]

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.

 

[Jake Bunce]

2)

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;

 

[Antonio Coelho]

Thanks you so much Jake (again!)