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
buckeyepackfan (2h) : Just like last year at 4-6. The guy is just too funny!
buckeyepackfan (2h) : GOOD news Uffda is already all but writing The Packers off for 2017!
uffda udfa (6h) : Masturbation talk from Barfan? Nothing could make me COME back, quicker.
wpr (7h) : Maybe the shout box needs to take a timeout. People keep using it. ;)
Zero2Cool (8h) : Why do this is in shout? So frustrating. Post in forums. Thanks
Barfarn (8h) : Masterbation will relieve some of that nervous tension!
uffda udfa (10h) : Now, a guy they really liked and have groomed for 3 years is the guy. Tons of toys on O. Let's see how it runs with a great coach, now.
uffda udfa (10h) : The Flynn Patriots game has been used to say that he is. Seneca and Scott showed otherwise.
uffda udfa (10h) : Hundley is going to settle the debate once and for all in whether Mike McCarthy is a great coach, or not.
uffda udfa (10h) : Doesn't speak well to the talent acquired by the org, does it? Easy to say talent is great until Rodgers is gone and you have to see it wit
Porforis (10h) : Could Sam Bradford come into the Packers and post a winning record from here on out? Are there any non-starters in the NFL that could?
Zero2Cool (10h) : Teddy is replaceable. Aaron is not. Vikings have a really good defense. We do not. Understand??
Zero2Cool (10h) : You are wise enough to know the difference. Right?
uffda udfa (11h) : Here's one for you, Z. Vikes lose Teddy B. and go out and aggressively get Bradford. Packers lose 12 and go out and get a UDFA.
uffda udfa (12h) : :) Evans had a pre-draft visit with Packers.
Zero2Cool (12h) : Put it in a topic. My lord why so difficult lol
uffda udfa (12h) : http://www.nfl.com/draft/2017/profiles/jerod-evans?id=2558099
uffda udfa (12h) : http://www.foxsports.com/nfl/story/former-virginia-tech-qb-jerod-evans-issues-warning-after-going-undrafted-043017
uffda udfa (12h) : Oh, Jerod Evans formerly of Va Tech is our Hokie QB plan
uffda udfa (12h) : Was previously on Eagles PS
uffda udfa (12h) : Tweeted the below due to huge chip on his shoulder for going undrafted after leaving early.
uffda udfa (12h) : Packers add Jerod Evans to PS. He went undrafted: https://twitter.com/rodfor6_/status/858382534274682884
Smokey (13h) : NO a 6 point favorite to beat the Packers in GB .
Zero2Cool (17-Oct) : Yes!!!!!
Porforis (17-Oct) : "I said that too. Why don't you quote me?? I'm famous!" - Zero2Cool
Zero2Cool (16-Oct) : I said that too. Why don't you quote me?? I'm famous!
uffda udfa (16-Oct) : @ProFootballDoc says Rodgers could return Week 14 at earliest, and surgery speeds up return timeline.
Zero2Cool (16-Oct) : McCarthy dropped a "bullshit" on packers.com stream. No delay apparently lol
Zero2Cool (16-Oct) : @AaronNagler One thing is crystal clear: McCarthy is pissed at the way his team played yesterday.
Smokey (16-Oct) : 2017 Packers are done , time to focus on the 2018 NFL Draft .
Zero2Cool (16-Oct) : Mike McCarthy says Aaron Rodgers "suffered a significant injury". Will require surgery. Potentially season could be over.
Zero2Cool (16-Oct) : McCarthy --Rodgers will have surgery. Potentially season is over.
Zero2Cool (16-Oct) : NaVaorro Bowman will get his full-year salary from the 49ers AND get another $3 million from the Raiders.
Smokey (16-Oct) : COOL !
Zero2Cool (16-Oct) : added a links dropdown with some misc web links
Please sign in to use Fan Shout
2017 Packers Schedule
Sunday, Sep 10 @ 3:25 PM
SEAHAWKS
Sunday, Sep 17 @ 7:30 PM
at Falcons
Sunday, Sep 24 @ 3:25 PM
BENGALS
Thursday, Sep 28 @ 7:25 PM
BEARS
Sunday, Oct 8 @ 3:25 PM
at Cowboys
Sunday, Oct 15 @ 12:00 PM
at Vikings
Sunday, Oct 22 @ 12:00 PM
SAINTS
Sunday, Oct 29 @ 12:00 AM
- BYE -
Monday, Nov 6 @ 7:30 PM
LIONS
Sunday, Nov 12 @ 12:00 PM
at Bears
Sunday, Nov 19 @ 12:00 PM
RAVENS
Sunday, Nov 26 @ 7:30 PM
at Steelers
Sunday, Dec 3 @ 12:00 PM
BUCCANEERS
Sunday, Dec 10 @ 12:00 PM
at Browns
Sunday, Dec 17 @ 12:00 PM
at Panthers
Saturday, Dec 23 @ 7:30 PM
VIKINGS
Sunday, Dec 31 @ 12:00 PM
at Lions
Think About It
Think About It
Recent Topics
1h / Green Bay Packers Talk / buckeyepackfan

2h / Green Bay Packers Talk / Barfarn

7h / Green Bay Packers Talk / wpr

12h / Around The NFL / Zero2Cool

17h / Green Bay Packers Talk / Smokey

18h / Green Bay Packers Talk / gotarace

19h / Green Bay Packers Talk / nerdmann

17-Oct / Green Bay Packers Talk / musccy

17-Oct / Green Bay Packers Talk / nerdmann

17-Oct / Green Bay Packers Talk / nerdmann

17-Oct / Green Bay Packers Talk / PackFanWithTwins

17-Oct / Green Bay Packers Talk / Porforis

17-Oct / Green Bay Packers Talk / nerdmann

16-Oct / Green Bay Packers Talk / DoddPower

16-Oct / Green Bay Packers Talk / Smokey

Headlines