I am trying to get a list of the members (and email address) that posted in a forum within the past 30 days. Does anyone know what query would be used to get that information. TIA
Query for users that post in a forum
Collapse
X
-
You can try the following:
Code:select distinct user.userid, user.username, user.email from user join post on (user.userid = post.userid) join thread on (post.threadid = thread.threadid) where thread.forumid = XX AND post.dateline >= DATE_SUB(NOW(), INTERVAL 30 DAY);
Translations provided by Google.
Wayne Luke
The Rabid Badger - a vBulletin Cloud demonstration site.
vBulletin 5 API -
Should be able to if you have permissions set.Translations provided by Google.
Wayne Luke
The Rabid Badger - a vBulletin Cloud demonstration site.
vBulletin 5 APIComment
-
Also make sure to change the XX in the query to the forumid you want to check.Translations provided by Google.
Wayne Luke
The Rabid Badger - a vBulletin Cloud demonstration site.
vBulletin 5 APIComment
-
Hi Wayne, i ran this:
select distinct vb_user.userid, vb_user.username, vb_user.email from vb_user
join vb_post on (vb_user.userid = vb_post.userid)
join vb_thread on (vb_post.threadid = vb_thread.threadid)
where vb_thread.forumid = 65 AND vb_post.dateline >= DATE_SUB(NOW(), INTERVAL 30 DAY)
And had no returns, thats my busiest forum, maybe i've missed something like the OP, i'm using vb3.8.6Comment
-
Hmm.. Works on my test database or I wouldn't have posted it. What versions of MySQL are you using?Translations provided by Google.
Wayne Luke
The Rabid Badger - a vBulletin Cloud demonstration site.
vBulletin 5 APIComment
-
Here is what I ended up using
Code:[FONT=Courier New][URL="https://www.diecastwings.net:2087/3rdparty/phpMyAdmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.1%2Fen%2Fselect.html&token=f8b10d0cc27fc6c2024241aedc72db16"]SELECT[/URL] [/FONT][FONT=Courier New]DISTINCT [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]userid[/FONT][FONT=Courier New], [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]username[/FONT][FONT=Courier New], [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]email[/FONT][FONT=Courier New] [/FONT][FONT=Courier New]FROM [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New] [/FONT][FONT=Courier New]JOIN [/FONT][FONT=Courier New]post [/FONT][FONT=Courier New]ON [/FONT][FONT=Courier New]( [/FONT][FONT=Courier New]user[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]userid [/FONT][FONT=Courier New]= [/FONT][FONT=Courier New]post[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]userid [/FONT][FONT=Courier New])[/FONT][FONT=Courier New] [/FONT][FONT=Courier New]JOIN [/FONT][FONT=Courier New]thread [/FONT][FONT=Courier New]ON [/FONT][FONT=Courier New]( [/FONT][FONT=Courier New]post[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]threadid [/FONT][FONT=Courier New]= [/FONT][FONT=Courier New]thread[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]threadid [/FONT][FONT=Courier New])[/FONT][FONT=Courier New] [/FONT][FONT=Courier New]WHERE [/FONT][FONT=Courier New]thread[/FONT][FONT=Courier New].[/FONT][FONT=Courier New]forumid [/FONT][FONT=Courier New]=[/FONT][FONT=Courier New]17[/FONT][FONT=Courier New] [/FONT][B][COLOR=red][FONT=Courier New]AND [/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New]post[/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New].[/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New]dateline [/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New]>=[/FONT][/COLOR][/B][B][COLOR=red][FONT=Courier New]1315526400[/FONT][/COLOR][/B][FONT=Courier New] [/FONT][FONT=Courier New]LIMIT [/FONT][FONT=Courier New]0 [/FONT][FONT=Courier New], [/FONT][FONT=Courier New]30 [/FONT]
1315526400 is unix time for 9/9/2011 at 00:00:00
Use this site http://www.onlineconversion.com/unix_time.htm to convert for other dates…
Comment
-
Hmm.. wonder if date_sub is broken on your version of MySQL then.Translations provided by Google.
Wayne Luke
The Rabid Badger - a vBulletin Cloud demonstration site.
vBulletin 5 APIComment
-
-
Hmmm. I am running 5.5.14 Community. Sorry about the query. I am glad you got it working though.Translations provided by Google.
Wayne Luke
The Rabid Badger - a vBulletin Cloud demonstration site.
vBulletin 5 APIComment
widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
Comment