Mass selective thread move

[Bozza]

Sorry, this is probably more SQLy than anything else, but I couldn't see a specific SQL forum, so here goes...

I'm looking for some practical help with a problem that hit me today...

My site is a reasonable size - 195,000 threads (95% in the one main forum) and 4.1m posts. We are heavily reliant on Google Adsense revenue to pay our hosting bills.

Today, Adsense disabled ads from us because of some of the content on the site. The example they gave was a thread that included a picture of a topless woman. Nothing that can't be seen in many UK 'family' newspapers on any given day, but there you have it.

I want to retain Adsense if I can, as the site could struggle to exist without it.

As there could be similar content anywhere over the site I've done a mass move - moving everything older than a month to a new, currently hidden, forum. The idea is to get a trusted group of users to work through threads in this forum, clean them if required and then move them back into view. However this is c180,000 threads - in all likelihood this will not all be reviewed.

To try and keep the manual work as small as possible I thought it would be good if there were a way to programmatically move all threads that didn't contain either an attachment or an IMG tag as these won't contain anything dodgy. The remainder could then be reviewed manually.

My SQL skills are OK and I reckon I could write a script to identify all such threads, but is this practical? It strikes me that the SQL could be massively intensive given the need to effectively do a LIKE on each of 4m posts.

Is this the only way I could do something 'clever' or is there another option for me? All help appreciated!

[Zachery]

You can just prune/move threads that are older than a specific date.

[Bozza]

Hi, yes I've done that. Maybe I need to explain a little bit better!

So, I had Forum 1 with c4m posts in across 195,000 threads.

I've now moved 180,000 of those threads to Forum 2, using the Admin Panel mass move thread.

I now want to be able to select all those from Forum 2 that have no attachments and no IMG tags and move them back to Forum 1, leaving behind in Forum 2 only those with attachment(s) or IMG tag(s).

So something like (excuse the syntax)...

update thread
set forumid = 1
where threadid in
(
select threadid from thread
join post on post.threadid = thread.threadid
where thread.forumid = 2
and post.pagetext like '%[IMG]%'
or post.attach = 1
)

[punchbowl]

I feel your pain Bozza. I had the same from adsense recently but got back after a week or so.

Instead of looking through all your old threads add a replacement variable that rewrites img src="http to a href="http that'll change all embedded images to links

[Bozza]

I'm not sure that will wash punchbowl since Adsense say you can't link to content they frown upon so I'd assume if they take exception to an image on the site, they'll also frown upon a link to that same image.

[punchbowl]

you can't link to gambling...they don't mind you linking to images. We were reviewed and reaccepted despite having a link to our nsfw section at the top of all forums. You can't have an image of a naked lady on the same page as adsense. That's what you got done for. Not because you had nsfw somewhere on your forum.

Our situation was different but we basically moved all the nsfw threads (we had previously created a nsfw prefix so it was quite easy) to a nsfw forum where adsense wasn't shown. We probably still have some nsfw images in our forum that we haven't spotted. But we have a policy now and imo that's what they want to see.

Anyway good luck. If you need any advice from someone who's been through it gimme a shout. Our site relied on adsense too and I thought all was lost.

[Bozza]

Thanks - appreciate the support. I thought it would be good to show a policy re-vamp too, so am expecting to have very visible...

i) New instructions on acceptable content.
ii) Reinforcement to users to 'report post' when bad content is posted.
iii) A visible bolstering of the moderating team.
iv) Demonstrate efforts have been made to remove questionable content.

I like the nsfw adsense-free idea and might pursue that too. I might be back for more tips!

[punchbowl]

good luck man, all is not lost!