Not a Coder

I am not a coder by design. I can read code, and modify code, but writing original code is not really something that I enjoy or look forward to doing. There are however, occasions where working with code is about the only thing that you can do to accomplish what you want to do. I’ve had a nagging issue on my list that I’ve been wanting to deal with that I finally got to this morning. In our long absence from blogging our blog site managed to accumulate about 9,000 spam comments. Deleting these via the wordpress web pages is kind of a bummer because you are limited to seeing only deleting 20 comments at a time in the mass edit mode. I deleted about 2,000 comments 20 at a time one day while eating lunch at my computer. This got very boring very quickly, and I dropped the idea of finishing the project 20 at a time. This blog post should be titled “How to Deal with a Massive Number of Comments in WordPress” but that did not tickle my funny bone that much.

After writing out my task list yesterday, I decided that I was going to get some the little things out of the way and gain some sense of accomplishment. This project seemed simple to me, so I worked it out this morning over breakfast. Looking at the web address for the mass edit page you can tell which PHP page is being used, and where it’s at on your server. I opened that file in WordPad and did a search for 20. I looked for 20 thinking that there had to be a hard setting for the number of records displayed on the page. Low, and behold there was a 20 in the text, in a SQL statement. Oh, goodie I speak SQL from a past life, and know that this is the place to change the number. First, I set the offset to 500 to display a bunch of comments so I could knock them all out quickly. That was a nice idea but MySQL did not like that. It crashed during the comment deletion operation telling me that it had made too many changes in too little time. I then restarted MySQL to reset the counter. I had to restart MySQL after every other deletion but I was done deleting in about 5 minutes. I could have uped the write allowed value but I was feeling lazy and not in the mood to deal with MySQL config files. Below is the line of code that I changed and an image of what I now see in the WordPress mass delete page.

    $comments = $wpdb->get_results(“SELECT * FROM $wpdb->comments WHERE comment_approved = ‘0’ OR comment_approved = ‘1’ ORDER BY comment_date DESC LIMIT $offset,500“);

 


Sorry that this is not the best screen capture that I have ever done.

Related Posts with Thumbnails

About Kevinm