Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 02 Jan 2017, 16:47
grey_goose grey_goose is offline
 
Join Date: Jun 2009
MYSQL Query to count words written

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?
Reply With Quote
  #2  
Old 02 Jan 2017, 16:49
Dave Dave is offline
 
Join Date: Jun 2010
Real name: Dave
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?
__________________
https://technidev.com - security, development, exploits, vBulletin
dave[at]technidev[dot]com

Contact me for custom vBulletin 3/4 work & server/website management.
Reply With Quote
  #3  
Old 02 Jan 2017, 17:21
grey_goose grey_goose is offline
 
Join Date: Jun 2009
...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.
Reply With Quote
  #4  
Old 02 Jan 2017, 17:24
Dave Dave is offline
 
Join Date: Jun 2010
Real name: Dave
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.
__________________
https://technidev.com - security, development, exploits, vBulletin
dave[at]technidev[dot]com

Contact me for custom vBulletin 3/4 work & server/website management.
Reply With Quote
  #5  
Old 02 Jan 2017, 17:33
grey_goose grey_goose is offline
 
Join Date: Jun 2009
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.
Reply With Quote
  #6  
Old 03 Jan 2017, 20:07
Dave Dave is offline
 
Join Date: Jun 2010
Real name: Dave
Originally Posted by grey_goose View Post
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.
__________________
https://technidev.com - security, development, exploits, vBulletin
dave[at]technidev[dot]com

Contact me for custom vBulletin 3/4 work & server/website management.
Reply With Quote
Reply


Tags
mysql, query


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 15:06.

Layout Options | Width: Wide Color: