36 members from hitting 65,000

[krashkart]

Originally Posted by STEED

Are now I see why jim so far ahead, double poster, what cheek.

learn a thing or two

[krashkart]

Originally Posted by STEED

Are now I see why jim so far ahead, double poster, what cheek.

from the master.

[Anthony W.]

But I wanna use the squidopods...

[Onkel Neal]

Originally Posted by Penguin

Maybe I can help a little.
Is your second command meant like it looks? You would select everyone who had his last log-in in in the first 24 hours after joining, if a member logged in after this period, he would not be selected. I think you know from experience if spammers log in another time, or just create an account and never check back.

To check if someone was inactive for more than a year, it should look like User.Lastactivity - User.joindate > 31536000

Yes, sir, my intention is to look back at members who joined since Dec 1, 2010, have 0 posts, have a signature, and their last activity was 24 hours from their join date. We find many of the sig spammers simply make an account and never post or log in again.

I want to minimize deleting actual people, so if a guy joined Dec 20 and his last activity was Dec 23, then he stays. I don't mind a guy being inactive for 6 months, as long as he is not a spammer. Does that make sense?

Originally Posted by Penguin

I think the <>"" can do the job to check if there is a sig (I guess that signaturparesed stores it in plain text), nicer would be Sigparsed.signatureparsed NOT LIKE ' '

As you obviously check two tables, User and Sigparsed, don't forget the JOIN command.

Cheers,
Penguin

Yes, I am studying up on the JOIN command, internal and external. I'm a real noob with SQL so I have to build this query from scratch. Anything you have to say on the subject is welcome, thanks, Penguin.

[Penguin]

Originally Posted by Neal Stevens

Yes, sir, my intention is to look back at members who joined since Dec 1, 2010, have 0 posts, have a signature, and their last activity was 24 hours from their join date. We find many of the sig spammers simply make an account and never post or log in again.

I want to minimize deleting actual people, so if a guy joined Dec 20 and his last activity was Dec 23, then he stays. I don't mind a guy being inactive for 6 months, as long as he is not a spammer. Does that make sense?

Yes, I am studying up on the JOIN command, internal and external. I'm a real noob with SQL so I have to build this query from scratch. Anything you have to say on the subject is welcome, thanks, Penguin.

Ok, this makes sense, if the spammer doesn't log in a second time.(didn't check out the spam hunt thread yet to look at actual spammer accounts )
EDIT: Found this one while randomly looking for spammers: http://www.subsim.com/radioroom/member.php?u=276634 He logged in 3 days after he registered , however most found had their last activity on the date of registration.

So the command to get all the infos about this account would look like this, this is the explicit statement, you can make it shorter, but this is better for the comprehension

SELECT *(all infos, or whatever you need)
FROM User AS U1 INNER JOIN Sigparsed AS S1 (just abbreviations to type less)
ON U1.UserID = S1.UserId (UserId, or something simular would be your unique key that you can find in both tables)
WHERE U1.Joindate >1291165261
AND (U1.Lastactivity - U1.joindate ) < 86400
AND U1.Totalposts < 2
AND S1.Signatureparsed NOT LIKE ' '
AND S1.signatureparsed IS NOT NULL
ODER BY U1.Username ;

Here is some material to learn SQL: A lecture by David Malan, who teaches at a little, unknown school on the East Coast. I think he is very articulate and intelligible: http://cs75.tv/2010/fall/#l=lectures...res/4/lecture4 At the lower left corner you'll find also the second part (lecture5)

If you are more of a beginner, there is also a SQL lecture in the introduction to Computer Science class found here: http://cs50.tv/2010/fall/ If a lecture is broken you may want to play with the course dates in the upper drop-down menu to access courses from previous years.

[Onkel Neal]

Thanks so much for the help. I am going to have a look at those course, I find SQL very interesting, I just never got on track with a tutorial that I could follow.

Question: in your query, you stated
SELECT *(all infos, or whatever you need)
The info I would want to select would be userID, signature, homepage, right?


FROM User AS U1 INNER JOIN Sigparsed AS S1 (just abbreviations to type less)
Abbreviations for what? Or did you simply give the join names really short names, like U1 ? That makes sense.

AND U1.Totalposts < 2
That means any member with 1 post will not be selected, right? I can change that to < 1 and select all the 0 post users, right?

Listen, I really appreciate the help.
Neal

[Penguin]

Originally Posted by Neal Stevens

Thanks so much for the help. I am going to have a look at those course, I find SQL very interesting, I just never got on track with a tutorial that I could follow.

Question: in your query, you stated
SELECT *(all infos, or whatever you need)
The info I would want to select would be userID, signature, homepage, right?

np!
With the star it would show you all infos available (all columns) from both tables. If you want to use this statement to find the homepages it would just be something like this: SELECT U1.userpage - this would be usefull for a list of the userpages. To select more, just put a comma between, select U1.userpage, S1.signature, U1.shoesize, etc

Originally Posted by Neal Stevens

FROM User AS U1 INNER JOIN Sigparsed AS S1 (just abbreviations to type less)
Abbreviations for what? Or did you simply give the join names really short names, like U1 ? That makes sense.

yes, it just gives short names for the tables in this search query, helps to avoid typing long names for the lazy ones This is helpfull if you want to join several tables. If you want to use the real table names, just delete the "AS..."

Originally Posted by Neal Stevens

AND U1.Totalposts < 2
That means any member with 1 post will not be selected, right? I can change that to < 1 and select all the 0 post users, right?

In the example, it would select all users with no or one posts. I thought this would make sense, as we saw today, many spammers make one post, however I think those accounts get vaporized eventually .
If you want to select the users with no posts, you use either "= 0", "<1", or "IS NULL". It depends on how the totalposts column looks if a new user is created. If the db puts a 0 (numeric zero) into that column in the moment a new user registers, you can use one of the first two statements. If the column is empty untill the first postcount is written into it, you have to use the IS NULL statement.

[razark]

Subsim:
Come for the spam hunts, stay for the SQL tutorials.

[Onkel Neal]

Hahaha Nice!

[Paco]

Hi,

I'm coming from the SPAM-Hunt thread to this one.

Originally Posted by Penguin

AND (U1.Lastactivity - U1.joindate ) < 86400

perhaps a little bit easier to handle:

AND (DATEDIFF(U1.Lastactivity, U1.joindat) < "amount of days you want")

BTW, the SQL looks very good for hunting spammers

[Penguin]

Thanks for your input Paco, that's the reason why I put up the code here: always better if some more pair of eyes take look over it - no offence if you are a pirate

I am not sure though if Datediff works if the dates are stored as a timestamp and not in date format, without having to convert it - otherwise Datediff is certainly more elegant!

[Onkel Neal]

Chinese Prisoners Allegedly Forced to Play 'World of Warcraft'


Don't get any ideas!


Yeah, we got the query working, weeded out almost 1000 spam accounts, probably another 3000 to go.

[geetrue]

You SQL guys are all a like ... for all we know you could be the same men in black that show up after UFO sightings

[flatsixes]

All this talk about squirrels makes me hungry.

[frau kaleun]