Zero2Cool
14 years ago
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.


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]
UserPostedImage
Zero2Cool
14 years ago
[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.
UserPostedImage
Zero2Cool
14 years ago
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.
UserPostedImage
Zero2Cool
14 years ago
[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.
UserPostedImage
Zero2Cool
14 years ago
+1
UserPostedImage
Fan Shout
wpr (31m) : I thought KC already won
Zero2Cool (10h) : If you wanna post about the Super Bowl, please, by all means, open a topic. They are free this month! 😁
Zero2Cool (10h) : There doesn't need to be a topic. There's a playoff prediction thread.
packerfanoutwest (10h) : and there no SB contest over in the other Packer forum, either
buckeyepackfan (10h) : #2Officially Retires!
beast (11h) : Probably no SB topic as people are wore out talking about the Chiefs, Refs and Eagles
Zero2Cool (11h) : Packers reportedly have their new LB coach, promoting Sean Duggan to that role
Zero2Cool (15h) : WR Cooper Kupp is being traded.
packerfanoutwest (4-Feb) : why is there no SB Prediction topic?
Zero2Cool (3-Feb) : Anthony Perkins spent 2024 as a defensive quality-control coach with the Packers.
Zero2Cool (3-Feb) : Packers lose another assistant.
Zero2Cool (3-Feb) : Defensive Player of the Year and Browns star Myles Garrett has requested a trade.
Zero2Cool (3-Feb) : deleted all my browser history and autofill and passwords. gonna be fun!
packerfanoutwest (3-Feb) : too funny
packerfanoutwest (3-Feb) : Lions QB Jared Goff was the offensive MVP
packerfanoutwest (3-Feb) : for the Pro Bowl, which is flag football
Zero2Cool (2-Feb) : Rather, the murder WAS covered up to prevent ...
Zero2Cool (2-Feb) : JFK murder was a cover-up to prevent war with Cuba/Russia.
Martha Careful (1-Feb) : I have always admired the pluck of the man
Zero2Cool (1-Feb) : I remember thinking he was going to be something good.
Mucky Tundra (1-Feb) : The Dualing Banjo!
Zero2Cool (31-Jan) : Jets have named Chris Banjo as their special teams coordinator, Former Packers player
Zero2Cool (31-Jan) : Jaguars have hired Anthony Campanile as their DC. We lose coach
Zero2Cool (30-Jan) : QB coach Sean Mannion
Zero2Cool (30-Jan) : DL Coach DeMarcus Covington
dfosterf (30-Jan) : from ft Belvoir, Quantico and points south. Somber reminder of this tragedy at Reagan Nat Airport
dfosterf (30-Jan) : So eerily quiet here in Alexandria. I live in the flight path of commercial craft coming from the south and west, plus the military craft
dfosterf (30-Jan) : So eeri
Mucky Tundra (30-Jan) : Now that's a thought, maybe they're looking at the college ranks? Maybe not head coaches but DC/assistant DCs with league experience?
beast (30-Jan) : College Coaches wouldn't want that publicly, as it would hurt recruiting and they might not get the job.
beast (30-Jan) : I thought they were supposed to publicly announce them, at least the NFL ones. Hafley was from college, so I believe different rules.
Mucky Tundra (30-Jan) : Who knows who they're interviewing? I mean, nobody knew about Hafley and then out of nowhere he was hired
beast (30-Jan) : I wonder what's taking so long with hiring a DL coach, 2 of the 3 known to interview have already been hired elsewhere.
Zero2Cool (27-Jan) : Packers coach Matt LaFleur hires Luke Getsy as senior assistant, extends Rich Bisaccia's deal
Zero2Cool (27-Jan) : Chiefs again huh? I guess another Super Bowl I'll be finding something else to do.
Mucky Tundra (27-Jan) : Chiefs Eagles...again...sigh
dfosterf (27-Jan) : Happy Birthday Dave!
Mucky Tundra (27-Jan) : happy birthday dhazer
TheKanataThrilla (26-Jan) : Exactly buck...Washington came up with the ball. It is just a shitty coincidence one week later
buckeyepackfan (26-Jan) : I forgot, they corrected the call a week later. Lol btw HAPPY BIRTHDAY dhazer!
buckeyepackfan (26-Jan) : That brings up the question, why wasn't Nixon down by contact? I think that was the point Kanata was making.
buckeyepackfan (26-Jan) : Turnovers rule, win the turnover battle, win the game.
packerfanoutwest (26-Jan) : well, he was
TheKanataThrilla (26-Jan) : Eagles down by contact on the fumble....fuck you NFL
Mucky Tundra (26-Jan) : I think this games over
beast (26-Jan) : Eagles sure get a lot of fumbles on kickoffs
Mucky Tundra (26-Jan) : This game looks too big for Washington
packerfanoutwest (26-Jan) : that being said, The Ravens are the Browns
packerfanoutwest (26-Jan) : Browns, Dolphins have longest AFC Championship droughts
packerfanoutwest (26-Jan) : As of today, Cowboys have longest NFC Championship drought,
Please sign in to use Fan Shout
2024 Packers Schedule
Friday, Sep 6 @ 7:15 PM
Eagles
Sunday, Sep 15 @ 12:00 PM
COLTS
Sunday, Sep 22 @ 12:00 PM
Titans
Sunday, Sep 29 @ 12:00 PM
VIKINGS
Sunday, Oct 6 @ 3:25 PM
Rams
Sunday, Oct 13 @ 12:00 PM
CARDINALS
Sunday, Oct 20 @ 12:00 PM
TEXANS
Sunday, Oct 27 @ 12:00 PM
Jaguars
Sunday, Nov 3 @ 3:25 PM
LIONS
Sunday, Nov 17 @ 12:00 PM
Bears
Sunday, Nov 24 @ 3:25 PM
49ERS
Thursday, Nov 28 @ 7:20 PM
DOLPHINS
Thursday, Dec 5 @ 7:15 PM
Lions
Sunday, Dec 15 @ 7:20 PM
Seahawks
Monday, Dec 23 @ 7:15 PM
SAINTS
Sunday, Dec 29 @ 3:25 PM
Vikings
Sunday, Jan 5 @ 12:00 PM
BEARS
Sunday, Jan 12 @ 3:30 PM
Eagles
Recent Topics
4h / Green Bay Packers Talk / wpr

11h / Green Bay Packers Talk / Martha Careful

13h / Green Bay Packers Talk / beast

14h / Green Bay Packers Talk / Zero2Cool

4-Feb / Green Bay Packers Talk / earthquake

4-Feb / Green Bay Packers Talk / earthquake

1-Feb / Green Bay Packers Talk / Martha Careful

1-Feb / Green Bay Packers Talk / wpr

29-Jan / Green Bay Packers Talk / Mucky Tundra

27-Jan / Green Bay Packers Talk / beast

25-Jan / Green Bay Packers Talk / beast

25-Jan / Green Bay Packers Talk / Martha Careful

25-Jan / Random Babble / Martha Careful

20-Jan / Green Bay Packers Talk / Martha Careful

20-Jan / Green Bay Packers Talk / bboystyle

Headlines
Copyright © 2006 - 2025 PackersHome.com™. All Rights Reserved.