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
Smokey (14h) : Don't look back, I'm gaining beast ! LOL
beast (16h) : Thanks Smokey!
Zero2Cool (18h) : I'm not confident vs Steelers. But they are up/down a lot.
dhazer (19h) : I actually had a Steeler fan ask me why the stadium was still packed at the end of the game on Sunday. I said we back our team no matter wha
dhazer (19h) : Kevin we will beat that this week lol
Zero2Cool (21h) : Cowboys have one touchdown in 2 games. One TD in 22 possessions. One
Smokey (21-Nov) : Nice Week 11 Pick'em beast , salute !
hardrocker950 (21-Nov) : No late game heroics for Seattle tonight
yinzer (21-Nov) : You can do much worse than having the Steelers as your favorite team... that being said, Go Steelers!
uffda udfa (21-Nov) : Gilbert is/was their starting RT.
Zero2Cool (20-Nov) : Steelers OT Marcus Gilbert suspended four games for violating performance-enhancing substance policy.
Zero2Cool (20-Nov) : and my 1st favorite team.
Zero2Cool (20-Nov) : Packers are my 2nd favorite team.
uffda udfa (20-Nov) : Are Steelers your 2nd favorite team? I know you live the Pens and are from there.
Zero2Cool (20-Nov) : Steelers are league-best 4-1 vs. teams who currently have a winning record.
uffda udfa (20-Nov) : Kenny Clark x-rays were negative. Says he's fine.
Zero2Cool (20-Nov) : "I believe in Brett Hundley," McCarthy conclude. Quick, check coach for concussion!
Zero2Cool (20-Nov) : RB Aaron Jones was arrested in October for marijuana, driving without a license, and speeding.
Zero2Cool (20-Nov) : NFL Receiver Terry Glenn Has Died in a Car Accident
Zero2Cool (20-Nov) : @BenFennell_NFL Not to be overlooked...
Zero2Cool (20-Nov) : Packers run defense lately has been outstanding Since Nov 1 (3 games)... 59.0 YPG (3rd in NFL) and 2.33 YPC (2nd in NFL)
beast (20-Nov) : See you are troll, your true colors are showing
uffda udfa (20-Nov) : Charles Manson dead at 83.
uffda udfa (20-Nov) : At least Cleveland was the road team when Baltimore shut them out.
uffda udfa (20-Nov) : We shouldn't feel bad about being shutout by BAL. CLE was shutout by them, too.
uffda udfa (20-Nov) : Davante was a huge surprise. Ravens are tough to throw it on.
beast (20-Nov) : I don't know... Mays and Spriggs did a good job of looking offensive too!
Zero2Cool (20-Nov) : Only offensive player to come out looking good is Davante Adams.
uffda udfa (20-Nov) : He ain't changing from Hundley. Trying way too hard to prove it was right to go with him all along.
greengold (19-Nov) : A HC would say that today even if he planned to start Callahan next week.
Nonstopdrivel (19-Nov) : Yeah, well, there are plenty of people who believe in fairies and leprechauns too.
uffda udfa (19-Nov) : MM sticking with Hundley. Said he believes in him.
TheKanataThrilla (19-Nov) : Hayward with another INT 2nd of the day
Zero2Cool (19-Nov) : Martellus quit on Packers and this week they followed his lead. Offense anyway.
Nonstopdrivel (19-Nov) : Clark left with an ankle injury apparently.
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
2h / Green Bay Packers Talk / buckeyepackfan

6h / Green Bay Packers Talk / bboystyle

8h / Green Bay Packers Talk / beast

10h / Green Bay Packers Talk / uffda udfa

10h / Green Bay Packers Talk / beast

11h / Green Bay Packers Talk / uffda udfa

11h / Green Bay Packers Talk / beast

12h / Green Bay Packers Talk / beast

18h / Green Bay Packers Talk / Zero2Cool

20h / Green Bay Packers Talk / bboystyle

20h / Green Bay Packers Talk / Zero2Cool

21-Nov / Green Bay Packers Talk / MintBaconDrivel

20-Nov / Green Bay Packers Talk / nerdmann

20-Nov / Green Bay Packers Talk / beast

20-Nov / GameDay Threads / nerdmann

Headlines