Green Bay Packers Forum

Welcome to your Green Bay Packers Online Community!

Since 2006, PackersHome has been providing a unique experience for fans.
Your participation is greatly anticipated!
Login or Register.
3 Pages123>
Options
Go to last post Go to first unread
Offline Zero2Cool  
#1 Posted : Friday, October 22, 2010 2:59:22 PM(UTC)
Rank: Premier Member

Yahoo! NCAA March Madness - Gold: 2015Yahoo! Fantasy Football - Gold: 2009FleaFlicker Fantasy Football - Silver: 2010Yahoo! NCAA March Madness - Silver: 2011ESPN NCAA March Madness - Bronze: 2010Yahoo! NCAA March Madness - Bronze: 2013

United States
Joined: 10/13/2006(UTC)
Location: Green Bay, WI
Applause Given: 2,384
Applause Received: 3,733
I'm trying to clean up the database.

My goal is to remove the posts from members that have been deleted.

I want a select statement that shows me how many of them there are before deleting.


I think I'll be using three tables with this.

table (field)
[ul]users (user_id)
posts (poster_id)
posts_text (post_id)[/ul]

Every post is given and id number. Then that id number is assigned to the post text.

The relationship between users and posts is the user_id and poster_id.
The relationship between posts and posts_text is the post_id.

I tried this, but it didn't work so well.

[php]SELECT *
FROM posts LEFT JOIN users
ON posts.poster_id != users.user_id[/php]


I think I caused some site load errors with this query too, lol.
Offline dhpackr  
#2 Posted : Friday, October 22, 2010 3:09:19 PM(UTC)
Rank: Registered

Joined: 12/12/2007(UTC)
Applause Given: 36
Applause Received: 36
bummer you can't delete posts
Offline dhpackr  
#3 Posted : Friday, October 22, 2010 3:17:12 PM(UTC)
Rank: Registered

Joined: 12/12/2007(UTC)
Applause Given: 36
Applause Received: 36
don't you want to use count

SELECT posts, COUNT(*) FROM posts WHERE Posterid=poster_id GROUP BY poster_id;

you have to play around with the table names, and you add quite a bit to a query with 'where' & 'group by' as well as 'orderby'

just running a select or count query should not break the site
Offline Zero2Cool  
#4 Posted : Friday, October 22, 2010 3:22:57 PM(UTC)
Rank: Premier Member

Yahoo! NCAA March Madness - Gold: 2015Yahoo! Fantasy Football - Gold: 2009FleaFlicker Fantasy Football - Silver: 2010Yahoo! NCAA March Madness - Silver: 2011ESPN NCAA March Madness - Bronze: 2010Yahoo! NCAA March Madness - Bronze: 2013

United States
Joined: 10/13/2006(UTC)
Location: Green Bay, WI
Applause Given: 2,384
Applause Received: 3,733
I dunno, do I?
Offline dhpackr  
#5 Posted : Friday, October 22, 2010 3:25:30 PM(UTC)
Rank: Registered

Joined: 12/12/2007(UTC)
Applause Given: 36
Applause Received: 36
you know the answer, just play around till you get the query.

wanna let me in?

i'll try
otherwise this will help
Offline Nonstopdrivel  
#6 Posted : Friday, October 22, 2010 3:31:11 PM(UTC)
Rank: Select Member

United States
Joined: 9/14/2008(UTC)
Location: Germany
Applause Given: 389
Applause Received: 263
I'm confused. Are you wanting to clean up deleted posts or posts by deleted members? The owner of the other board I used to administrate did the latter routinely; unfortunately, some of the most prolific members (e.g., instructors) were getting deleted. This resulted in some of our best threads being rendered nearly indecipherable, with members responding to phantom statements and questions. Threads came to resemble Swiss cheese, with much of the best content missing, rendering them all but useless. If this is what you want to do, I'd urge caution in implementing it.
Offline Zero2Cool  
#7 Posted : Friday, October 22, 2010 3:36:19 PM(UTC)
Rank: Premier Member

Yahoo! NCAA March Madness - Gold: 2015Yahoo! Fantasy Football - Gold: 2009FleaFlicker Fantasy Football - Silver: 2010Yahoo! NCAA March Madness - Silver: 2011ESPN NCAA March Madness - Bronze: 2010Yahoo! NCAA March Madness - Bronze: 2013

United States
Joined: 10/13/2006(UTC)
Location: Green Bay, WI
Applause Given: 2,384
Applause Received: 3,733
627 posts returned.


Every post should have a valid relationship to a member.
Every topic should have a valid relationship to a post.
Every post should have a valid relationship to a topic.

I don't want rogue data out there.
Offline Pack93z  
#8 Posted : Friday, October 22, 2010 3:44:48 PM(UTC)
Rank: Select Member

PackersHome NFL Pick'em - Bronze: 2012

United States
Joined: 3/16/2007(UTC)
Location: North Central Wisconsin
Applause Given: 436
Applause Received: 1,205
Yes use the count, cleaner approach for your request in the first post.. as a review.
Offline Pack93z  
#9 Posted : Friday, October 22, 2010 3:45:50 PM(UTC)
Rank: Select Member

PackersHome NFL Pick'em - Bronze: 2012

United States
Joined: 3/16/2007(UTC)
Location: North Central Wisconsin
Applause Given: 436
Applause Received: 1,205
Okay.. with the timeout error, more than likely due to query gen, my comment now seems out of place. lol.
Offline Zero2Cool  
#10 Posted : Friday, October 22, 2010 3:52:22 PM(UTC)
Rank: Premier Member

Yahoo! NCAA March Madness - Gold: 2015Yahoo! Fantasy Football - Gold: 2009FleaFlicker Fantasy Football - Silver: 2010Yahoo! NCAA March Madness - Silver: 2011ESPN NCAA March Madness - Bronze: 2010Yahoo! NCAA March Madness - Bronze: 2013

United States
Joined: 10/13/2006(UTC)
Location: Green Bay, WI
Applause Given: 2,384
Applause Received: 3,733
627 is what I got back, which is just a list of members who have 1 or more posts.

The member list shows 618 with a post.

That to me means we have posts for 9 members that have been deleted.
Rss Feed 
Users browsing this topic
Guest
3 Pages123>
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
dyeah_gb (32m) : Dyeah! Go Pack!
buckeyepackfan (2h) : GOOD MORNING PACKER FANS! GAMEDAY! GO! PACK! GO!
flep (6h) : Gameday!!! Will see you all in chat later
The_Green_Ninja (10h) : Also, I'm not sure if you guys know. I'm a Memphis Tigers fan. Start looking a Riley Ferguson, QB. He is the real deal.
The_Green_Ninja (13h) : Big whoop, we have Aaron. We are going to win. H8ers.
Zero2Cool (15h) : Matthews, Burnett out; Guion, Jones doubtful
Zero2Cool (20h) : thank ya
macbob (23-Sep) : HAPPY BIRTHDAY, KEVIN (yesterday...sigh...)
Zero2Cool (23-Sep) : Sam Shields has already been ruled out for Sunday's game.
Zero2Cool (23-Sep) : For Lions, Ansah is out. LBs Levy and Williams doubtful and Van Noy questionable. TE Ebron and DE Taylor questionable, too.
Zero2Cool (23-Sep) : Jimmy G sprained his AC joint
Mucky Tundra (23-Sep) : Brady is suspended and 2nd stringer Jimmy Garoppolo suffered a ACL sprain in week 2
Mucky Tundra (23-Sep) : Patriots 3rd stringer QB Jacoby Brissett injuried and needs surgery
Zero2Cool (23-Sep) : Quinten Rollins is the only NFL cornerback to play 20 or more snaps in the slot this year without allowing a catch
Porforis (23-Sep) : Cutler has better luck completing to Packers players 10+ yards downfield than Rodgers does. I say only mostly facetiously.
Smokey (23-Sep) : HELLO DAVE
The_Green_Ninja (23-Sep) : You people would think we have a QB worse than Cutler.
dyeah_gb (22-Sep) : Bench him!!!!
Porforis (22-Sep) : WE'RE TEARING EACHOTHER APART! CAN'T YOU SEE WHAT THEY'RE DOING? THIS IS JUST WHAT THE BEARS WANT!
Zero2Cool (22-Sep) : you
The_Green_Ninja (22-Sep) : **** you ****s who are losing faith in our MVP qb. Go Aaron and Go Pack Go
Zero2Cool (22-Sep) : “I know I’ve got to play better, and I will,” Rodgers said, via ESPN.com.
Mucky Tundra (21-Sep) : Nearing the end of the line for Adrian?
Mucky Tundra (21-Sep) : Adrian Peterson to miss time with meniscus injury
Mucky Tundra (21-Sep) : is that a good thign?
Zero2Cool (20-Sep) : Wheel routes galore!
Zero2Cool (20-Sep) : 40 yard dash time: 4.37 seconds
Mucky Tundra (20-Sep) : that's interesting?
Zero2Cool (20-Sep) : Packers are having veteran RB C.J. Spiller in for a visit today, source said.
Zero2Cool (20-Sep) : Sure as hell hope Aaron Rodgers lights things up now.
Mucky Tundra (20-Sep) : damned if i know
Zero2Cool (20-Sep) : I bet if I cache'd it, we'd have no issues at all.
Zero2Cool (20-Sep) : I wonder how load intensive it would be if I added an auto refresh to this fan shout? Maybe refresh every 30 seconds...
Mucky Tundra (20-Sep) : *sad panda*
Zero2Cool (20-Sep) : Sucky Tundra
Please sign in to use Fan Shout

Road To Super Bowl LI
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 @ 12:00 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 @ 12:00 PM
at Lions

Think About It
Think About It

Recent Topics
8h / Green Bay Packers Talk / Cheesey

12h / Green Bay Packers Talk / The_Green_Ninja

13h / Green Bay Packers Talk / yooperfan

24-Sep / Green Bay Packers Talk / nerdmann

23-Sep / Green Bay Packers Talk / PackFanWithTwins

23-Sep / Green Bay Packers Talk / yooperfan

23-Sep / Green Bay Packers Talk / yooperfan

23-Sep / Green Bay Packers Talk / nyrpack

22-Sep / Green Bay Packers Talk / sschind

22-Sep / Green Bay Packers Talk / DarkaneRules

22-Sep / Green Bay Packers Talk / DarkaneRules

22-Sep / Green Bay Packers Talk / The_Green_Ninja

21-Sep / Around The NFL / Smokey

21-Sep / Green Bay Packers Talk / steveishere

21-Sep / Fantasy Sports Talk / Smokey


Packers Headlines