Portal Forums Calendar Active Threads
Previous Thread
Next Thread
Print Thread
Hop To
7.2.2 Forum Stats Sql question
#223786 03/08/2009 3:35 PM
Joined: Mar 2009
Posts: 4
Z
ZombieSheep
Offline
stranger
ZombieSheep
stranger
Z Offline
Joined: Mar 2009
Posts: 4
Hi there,

I have been asked to do a favour for a friend who runs a forum on UBBT 7.2.2. and I was hoping you guys may be able to help me out.

His forum has been going a while now, and is approaching the 250,000 post milestone. He wants to do some advertising around the fact, as well as being able to officially recognise the 1/4 millionth poster. Looking at the Forum stats island, I can see the number of posts is currently at 249,500 or so, but for the life of me I can't formulate a sql query that yields his number (closest I got was about 3-400 out.

Unfortunately I don't have access to the files of the site, just the back-end mod access to the database. I was hoping someone would be able to point me in the right direction to be able to pull out the detail of the post that clicks over to 250,000.

Thanks in advance, and apologies that my first post here is such a techie question. smile

(I probably don't need t add this info, but I will just in case. I can't simply search fpr the post id, since there are close to 253,000 ids in the database frown )

Marc.

Last edited by ZombieSheep; 03/08/2009 3:49 PM.
Re: 7.2.2 Forum Stats Sql question
ZombieSheep #223787 03/08/2009 4:02 PM
Joined: Jun 2006
Posts: 16,299
Likes: 116
Portland, OR; USA
Gizmo Offline
UBB.threads Developer
Gizmo
UBB.threads Developer
Offline
Joined: Jun 2006
Posts: 16,299
Likes: 116
Portland, OR; USA
I'd load the posts table in phpmyadmin (or using a limit) and snag the last id... it should allow you to load it without grabbing every post id in the db...

I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Re: 7.2.2 Forum Stats Sql question
Gizmo #223789 03/08/2009 4:24 PM
Joined: Mar 2009
Posts: 4
Z
ZombieSheep
Offline
stranger
ZombieSheep
stranger
Z Offline
Joined: Mar 2009
Posts: 4
Thanks Gizmo, but unfortunately, the ids don't match up with the count of posts or the data in the forum stats island. I assume the forum stats island is doing some funky exclusion stuff, but I don't know what.
Re: 7.2.2 Forum Stats Sql question
ZombieSheep #223790 03/08/2009 4:40 PM
Joined: Apr 2007
Posts: 3,940
Likes: 1
SoCal, USA
SD Offline
Former Developer
SD
Former Developer
Offline
Joined: Apr 2007
Posts: 3,940
Likes: 1
SoCal, USA
SQL Query
SELECT
SUM(FORUM_TOPICS) AS TOPICS, 
SUM(FORUM_POSTS) AS POSTS
FROM	ubbt_FORUMS
WHERE	FORUM_IS_ACTIVE = '1'

is what is used for total topics and posts wink
Re: 7.2.2 Forum Stats Sql question
SD #223805 03/09/2009 7:53 AM
Joined: Mar 2009
Posts: 4
Z
ZombieSheep
Offline
stranger
ZombieSheep
stranger
Z Offline
Joined: Mar 2009
Posts: 4
Thanks SirDude. I'll figure out how to find the 250,000th post from there. Thanks. smile
Re: 7.2.2 Forum Stats Sql question
ZombieSheep #223841 03/09/2009 4:00 PM
Joined: Mar 2009
Posts: 4
Z
ZombieSheep
Offline
stranger
ZombieSheep
stranger
Z Offline
Joined: Mar 2009
Posts: 4
I think I've narrowed it down to the following query...

USE THIS QUERY TO GET THE POST ID...

SQL Query
select POST_ID from ubbt_POSTS where TOPIC_ID in
(
	select TOPIC_ID from ubbt_TOPICS where FORUM_ID in (
		select FORUM_ID from 
		ubbt_FORUMS where FORUM_IS_ACTIVE = 1
		and FORUM_ID not in (56)
	)
)
LIMIT 250000,1

Can anyone pick any holes in this, or does it look like you think it should?

Note - FORUM_ID 56 is a moderators area, and I don't want to count these posts.

Note 2 - Yeah, it could do with some optimisation - its not a performant query, but it's only going to be run once or twice. smile

Last edited by ZombieSheep; 03/09/2009 4:01 PM.

Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Bots
by Outdoorking - 04/13/2024 5:08 PM
Can you add html to language files?
by Baldeagle - 04/07/2024 2:41 PM
Do I need to rebuild my database?
by Baldeagle - 04/07/2024 2:58 AM
This is not a bug, but a suggestion
by Baldeagle - 04/05/2024 11:25 PM
Is UBB.threads still going?
by Aaron101 - 04/01/2022 8:18 AM
Who's Online Now
1 members (Nightcrawler), 1,165 guests, and 234 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Forum Rules · Mark All Read Contact Us · Forum Help · UBBCentral
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)