Discussion Board
Welcome Guest! You can login or register. Login or Register.

Options
Go to last post Go to first unread
Zero2Cool  
#1 Posted : Thursday, January 6, 2011 3:30:33 AM(UTC)
I'd like a SQL statement that finds the amount of posts for each user, then updates the specific field with that number.



On table_fpro_posts I want the count of posts that equal the poster_id and then update the field field_user_posts on table_users.

The relation between the tables would be user_id from the table_users table and poster_id from the table_fpro_posts table.


Code:
table_users		table_fpro_posts
user_id		=	poster_id



I've noticed several members with a post count that is not accurate and I'd like to fix that, but not sure where to start with the SQL command.


Getting the count isn't too hard, but I don't want to go through each member and manually update them. That's not fun.
[php]

SELECT COUNT(post_id)
FROM `cvs_fpro_posts`
WHERE poster_id = 2[/php]
Zero2Cool  
#2 Posted : Thursday, January 6, 2011 3:49:58 AM(UTC)
[php]UPDATE table_users
SET user_posts = COUNT(*)
FROM table_fpro_posts
WHERE table_fpro_posts.poster_id = table_users.user_id[/php]

That was my first guess, but I haven't ran it ... scared I'll break shit.
Zero2Cool  
#3 Posted : Thursday, January 6, 2011 4:34:42 AM(UTC)
Code:
SELECT table_fpro_posts.poster_id, count(table_fpro_posts.poster_id), table_users.user_id, table_users.user_posts
FROM table_users LEFT JOIN table_fpro_posts
ON table_users.user_id = table_fpro_posts.poster_id
GROUP BY table_fpro_posts.poster_id


This displays the members post count on their profile, next to the actual post count.
Zero2Cool  
#4 Posted : Thursday, January 6, 2011 4:47:16 AM(UTC)
[php]UPDATE table_users
SET table_users.user_posts = (SELECT COUNT(table_fpro_posts.poster_id)
FROM table_fpro_posts
WHERE table_users.user_id = table_fpro_posts.poster_id)[/php]

This did the trick.
Zero2Cool  
#5 Posted : Saturday, January 8, 2011 4:16:05 PM(UTC)
+1
Rss Feed
Users browsing this topic
Guest
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error

Fan Shout
Zero2Cool (28m) : McCarthy wants all of his free agents back. Perry has delivered when healthy.
Zero2Cool (28m) : McCarthy says #Packers would love to have Eddie Lacy back.
Zero2Cool (28m) : McCarthy on LB Nick Perry: “Nick’s a hell of a football player”
Zero2Cool (1h) : Exactly
Zero2Cool (1h) : I don't think names matter to the Packers in free-agency, film does. - @jasonjwilde
Zero2Cool (2h) : NFL sets 2017 salary cap at $167 million
Zero2Cool (4h) : Who?
FLORIDA PACKER88 (16h) : Davon house on the way out with Jacksonville
Zero2Cool (16h) : Revis is gonna be available
Zero2Cool (23h) : Chiefs cut Jamaal Charles after 9 years in KC
Zero2Cool (28-Feb) : The Vikings will not exercise the 2017 option on RB Adrian Peterson’s contract. He'll be an unrestricted free agent.
Zero2Cool (27-Feb) : Mike Mayock says this is one of the best defensive drafts he's ever seen, especially at DE, CB & S.
Smokey (27-Feb) : Yellow as the background color for such blocks as Lambeau Field & Lombardi Ave & Recent Topics & Fan Shout .
Zero2Cool (27-Feb) : and thanks FP88 ... glad you enjoy it. :-)
Zero2Cool (27-Feb) : Finally, got my avatar back! Weird!
Zero2Cool (27-Feb) : FP88, :-) its okay. Smokey, yellow where?
FLORIDA PACKER88 (27-Feb) : Didnt realize how much I needed this site in my life until it wasn't available haha! Looks great Zero!
Zero2Cool (26-Feb) : Click the Clear link to force new files to load for style.
Smokey (26-Feb) : A little yellow for contrast maybe ?
Zero2Cool (26-Feb) : Thanks. Took about 16-20 hours.
wpr (26-Feb) : looks great.
Zero2Cool (25-Feb) : avatars aren't displaying
Zero2Cool (25-Feb) : plain look
Please sign in to use Fan Shout
2016 Packers Schedule
Sunday, Sep 11 @ 12:00 PM
at Jaguars
Sunday, Sep 18 @ 7:30 PM
at Vikings
Sunday, Sep 25 @ 12:00 PM
LIONS
Sunday, Oct 2 @ 12:00 AM
BYE
Sunday, Oct 9 @ 7:30 PM
GIANTS
Sunday, Oct 16 @ 3:25 PM
COWBOYS
Thursday, Oct 20 @ 7:25 PM
BEARS
Sunday, Oct 30 @ 3:25 PM
at Falcons
Sunday, Nov 6 @ 3:25 PM
COLTS
Sunday, Nov 13 @ 12:00 PM
at Titans
Sunday, Nov 20 @ 7:30 PM
at Redskins
Monday, Nov 28 @ 7:30 PM
at Eagles
Sunday, Dec 4 @ 12:00 PM
TEXANS
Sunday, Dec 11 @ 3:25 PM
SEAHAWKS
Sunday, Dec 18 @ 12:00 PM
at Bears
Saturday, Dec 24 @ 12:00 PM
VIKINGS
Sunday, Jan 1 @ 7:30 PM
at Lions
Think About It
Think About It
Recent Topics
16m / Green Bay Packers Talk / nerdmann

1h / Green Bay Packers Talk / nerdmann

1h / Green Bay Packers Talk / nerdmann

2h / Green Bay Packers Talk / jlyons043

2h / Green Bay Packers Talk / FLORIDA PACKER88

4h / Green Bay Packers Talk / PackFanWithTwins

4h / Green Bay Packers Talk / PackFanWithTwins

5h / Green Bay Packers Talk / Zero2Cool

7h / Fantasy Sports Talk / Smokey

13h / Green Bay Packers Talk / Cheesey

13h / Around The NFL / Smokey

14h / Green Bay Packers Talk / wpr

14h / Green Bay Packers Talk / nerdmann

28-Feb / Green Bay Packers Talk / sschind

28-Feb / Green Bay Packers Talk / Zero2Cool

Headlines