vBulletin Mods

The Official vBulletin Modifications Site
https://www.vbulletin.org/forum/showthread.php?t=324109

MYSQL Query to count words written
by grey_goose
02 Jan 2017 17:47

I've got a query written that more or less counts words, that I can execute on a per-user basis because it needs the userid in WHEREs.

What I'd like to do is be able to update the entire table and run it as a cron job -- but I can't figure out how to get the userid into the subquery.
X = userid
@thisuser = where I need the userids


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

I'm not sure if I'm going about this the wrong way, or just can't figure out how to fetch the userid of the current row that the query is looped on.

Suggestions?

Dave 02 Jan 2017 17:49

So if I understand you correctly, you want to convert the SQL you posted to work without a hardcoded userid and run it against all users in the database?

grey_goose 02 Jan 2017 18:21

...Yes. Although the way you asked that makes me wonder if I'm doing something dumb *lol*

I know it's pretty spendy from running it by hand, but it's a small forum.

Dave 02 Jan 2017 18:24

Yes this is not something you would want to do as a cronjob, as your database gets bigger, the cronjob will take more time and more load to complete.

It would be better if you run this one time and then create a hook in vBulletin that updates the `words` column every time a post is made/modified.

grey_goose 02 Jan 2017 18:33

Good point. I knew my spider sense was tingling!

What about executing/updating it per user on MEMBERINFO pageview, since that's where the info is displayed? Probably add a quarter sec to page load, but I'll be touching it far less.

Dave 03 Jan 2017 21:07

Quote:

Originally Posted by grey_goose (Post 2580196)
Good point. I knew my spider sense was tingling!

What about executing/updating it per user on MEMBERINFO pageview, since that's where the info is displayed? Probably add a quarter sec to page load, but I'll be touching it far less.

You could approach it in 2 different ways:
1. Always update it every time the profile is visited.
2. Add it once to the login of the user, then mark the user so it does not get executed again when the user is logging in. Then update it every time the user makes a post.


All times are GMT. The time now is 19:48.

Powered by vBulletin® Version 3.8.13
Copyright © 2019, MH Sub I, LLC dba vBulletin. All Rights Reserved. vBulletin® is a registered trademark of MH Sub I, LLC
Copyright ©2001 - , vbulletin.org. All rights reserved.