I think if you look at the Statistics in someone's profile it shows # of downloads in addition to # of posts.

So the criteria could include whether or not someone's downloaded something in addition to whether or not they've posted. That would allow lurkers who created a valid account in order to download something to be retained as members. Seems unfair to delete an account if someone made it for a valid reason. I seem to remember I initially made my account to access the downloads, not to post. I don't know that I even had the intention of ever posting anything.* :hmmm:






*You can all stop laughing now.

WE CAN'T
http://meowcheese.com/files/lolpics/...ughing-cat.jpg

Hardly. :doh:

Or we could just make it so you don't have to register to dowload, but have one of those letter things.

And a speed cap - and registered users get their speed caps taken off after 100 posts or something

Trew. I do not want to disturb legit members even if they only joined to d/l. And that's one of the reasons that registration is necessary for downloading, they may become active members and enhance the site.


What I really need is more SQL background. Anyone here know SQL?


User.Joindate >1291165261
User.Lastactivity - User.joindate < 86400
User.Totalposts <2
Sigparsed.Signatureparsed <> ""
Sigparsed.signatureparsed IS NOT NULL

Something like that should help me isolate spammers and still allow dormant accounts to remain intact.

I see... yes, I could see myself testing beer. I still get the free Texas BBQ, right? :DL

LOL...nice one :DL

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

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

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

Perhaps we can

learn a thing or two

from the master. :D

But I wanna use the squidopods...

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. :salute:

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.

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

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

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..."

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 :DL.
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.

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

Hahaha :har: Nice!

Hi,

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


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 :salute: