Zero2Cool
13 years ago
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.
UserPostedImage
dhpackr
13 years ago
bummer you can't delete posts
So if you meet me Have some courtesy, Have some sympathy, and some taste
Use all your well-learned politesse, Or I'll lay your soul to waste
dhpackr
13 years ago
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
So if you meet me Have some courtesy, Have some sympathy, and some taste
Use all your well-learned politesse, Or I'll lay your soul to waste
Zero2Cool
13 years ago
I dunno, do I?
UserPostedImage
dhpackr
13 years ago
you know the answer, just play around till you get the query.

wanna let me in?

i'll try
otherwise this will help 
So if you meet me Have some courtesy, Have some sympathy, and some taste
Use all your well-learned politesse, Or I'll lay your soul to waste
Nonstopdrivel
13 years ago
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.
UserPostedImage
Zero2Cool
13 years ago
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.
UserPostedImage
Pack93z
13 years ago
Yes use the count, cleaner approach for your request in the first post.. as a review.
"The oranges are dry; the apples are mealy; and the papayas... I don't know what's going on with the papayas!"
Pack93z
13 years ago
Okay.. with the timeout error, more than likely due to query gen, my comment now seems out of place. lol.
"The oranges are dry; the apples are mealy; and the papayas... I don't know what's going on with the papayas!"
Zero2Cool
13 years ago
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.
UserPostedImage
Fan Shout
Zero2Cool (18m) : They do not do it for "content sake".
dfosterf (10h) : For the record, I enjoy Beast and Mucky drafts
Zero2Cool (17h) : Haha
Mucky Tundra (18h) : No time for talking! Back to work beast!
beast (18h) : You saw only 4,201 of my mocks? 🥺 I think that means you missed more than half of them 😢
dfosterf (22h) : Does anyone know what Lambeau field improvements got put on hold? My guess would be for the 2025 draft
Zero2Cool (27-Mar) : It's like listen, you made 4,201 mocks, no shit.
Zero2Cool (27-Mar) : Cuz during the draft "I had them mocked there!" as if it's amazing.
Zero2Cool (27-Mar) : They're fun to do once in awhile. It's people who think they are "content" that annoy me.
dfosterf (27-Mar) : Against tbd
dfosterf (27-Mar) : Answer to your question is yes, it's a Thursday, will be the Chiefs aga
dfosterf (27-Mar) : Luckily for all concerned, I don't post them. I did one, but that was like 25 mocks ago
Zero2Cool (27-Mar) : NFL 2024 gonna start Sept 5th isn't it???
Zero2Cool (27-Mar) : Ugh... kids these days!
dfosterf (27-Mar) : I'm gonna go do some more mock draft hell instead 🤪
Zero2Cool (27-Mar) : Did we do one of those prediction threads yet for 2024 season?
dfosterf (27-Mar) : In my city, they are playing the nimby game, in order to keep some railroad tracks vs. 2 professional sports teams and a concert venue.
dfosterf (27-Mar) : And/Or a city council, of which I haven't seen a good one in a very long time
dfosterf (27-Mar) : That sounds like a Mayor, not a city.
buckeyepackfan (26-Mar) : Packers halt scheduled 80mil upgrade of stadium until lease agreement talks are restarted
Zero2Cool (26-Mar) : City of Green Bay puts Packers' Lambeau Field lease talks on hold
buckeyepackfan (26-Mar) : Packers 1 of 3 teams to vote no on new kickoff rule.
Zero2Cool (26-Mar) : Packers sign another Kicker
dfosterf (26-Mar) : Lengthy explanation at PFF if you click the link
dfosterf (26-Mar) : Kickoff rules officially changed.ngthy explan
Zero2Cool (26-Mar) : lol
Cheesey (26-Mar) : 2009? No thanks! One open heart surgery is enough!
dfosterf (26-Mar) : Good for you!
Zero2Cool (26-Mar) : Yes. That's the one.
dfosterf (26-Mar) : Is that "Lady Dugan" per chance?
dfosterf (26-Mar) : Crystal?
dfosterf (26-Mar) : Please refresh my memory
Zero2Cool (26-Mar) : Alan posts. Crystal back in my life. It's 2009 all over again! Lol
Mucky Tundra (26-Mar) : BAH GAWD! THAT'S CHEESEYS MUSIC!
Zero2Cool (25-Mar) : Gutekunst said early stages of Jordan Love contract being discussed.
Zero2Cool (25-Mar) : Shouldn't be penalized cuz official screwed up
Zero2Cool (25-Mar) : Yeah, challenge until you are incorrect twice.
Zero2Cool (25-Mar) : Fining them is the goal, per the people who made the rule anyway.
dfosterf (25-Mar) : Still waiting on the kickoff rule changes. Did hear yesterday that the touchback proposal will now be the 30 yard line, not the 35
dfosterf (25-Mar) : Probably speed of game issues with your proposal
dfosterf (25-Mar) : Hopefully the refs don't get in the habit of throwing flags on this
beast (25-Mar) : I think when it comes to Challenges should get two strikes, so unlimited challenges as long as they keep winning them, but 2 wrong then done
dfosterf (25-Mar) : Still subject to the fines etc
dfosterf (25-Mar) : Yes, I should have been more specific. Also, they are now saying it would be a 15 yard penalty. That makes more sense .
beast (25-Mar) : Simply fined in the week to follow
beast (25-Mar) : I agree with one NFL official, it'll probably be like some of the helmets hits, not really called by the refs on the field but simply fined
Zero2Cool (25-Mar) : Hip drop is not. Super confusing. Referees job is harder
Zero2Cool (25-Mar) : Swivel hip drop is banned
dfosterf (25-Mar) : The hip drop enforcement will be in the form of fines, etc. Not flags
dfosterf (25-Mar) : A major foul will be enforced on the offense if there are offsetting penalties in a change of possession situation
Please sign in to use Fan Shout
2023 Packers Schedule
Sunday, Sep 10 @ 3:25 PM
Bears
Sunday, Sep 17 @ 12:00 PM
Falcons
Sunday, Sep 24 @ 12:00 PM
SAINTS
Thursday, Sep 28 @ 7:15 PM
LIONS
Monday, Oct 9 @ 7:15 PM
Raiders
Sunday, Oct 22 @ 3:25 PM
Broncos
Sunday, Oct 29 @ 12:00 PM
VIKINGS
Sunday, Nov 5 @ 12:00 PM
RAMS
Sunday, Nov 12 @ 12:00 PM
Steelers
Sunday, Nov 19 @ 12:00 PM
CHARGERS
Thursday, Nov 23 @ 11:30 AM
Lions
Sunday, Dec 3 @ 7:20 PM
CHIEFS
Monday, Dec 11 @ 7:15 PM
Giants
Sunday, Dec 17 @ 12:00 PM
BUCCANEERS
Sunday, Dec 24 @ 12:00 PM
Panthers
Sunday, Dec 31 @ 7:20 PM
Vikings
Sunday, Jan 7 @ 3:25 PM
BEARS
Sunday, Jan 14 @ 3:30 PM
Cowboys
Saturday, Jan 20 @ 7:15 PM
49ers
Recent Topics
10h / Green Bay Packers Talk / dfosterf

10h / Green Bay Packers Talk / bboystyle

11h / Green Bay Packers Talk / bboystyle

17h / Green Bay Packers Talk / Mucky Tundra

22h / Random Babble / Zero2Cool

27-Mar / Green Bay Packers Talk / beast

26-Mar / Green Bay Packers Talk / beast

26-Mar / Green Bay Packers Talk / beast

26-Mar / Green Bay Packers Talk / bboystyle

25-Mar / Green Bay Packers Talk / Zero2Cool

25-Mar / Green Bay Packers Talk / Zero2Cool

24-Mar / Around The NFL / dhazer

24-Mar / Green Bay Packers Talk / beast

24-Mar / Green Bay Packers Talk / beast

22-Mar / Green Bay Packers Talk / beast

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