Cody Taylor

WordPress is by far the most popular content management system for blog hosting. The wordpress content management system uses the mysql database. If you have a big site with a large number of posts then it can be handy to search the content of every post to find certain text. Sometimes you may even need to replace certain keywords with other keywords. As with most content management setups there is probably a plugin that will do just that, but it is far easier to just use basic sql if you know the structure of the wordpress database.
Within either phpmyadmin or mysqlyog (depending on what you are using) you can use this sql query to find the text that you are looking for:

select * from wp_posts where post_content 
like '%content management system%';

The ID that you get back is basically the page id. For example, if I query my database and get back an id of 13449 then that content will reside at http://codytaylor.org/?p=13449. Other useful columns are the post_content which is the content text of the post, post_name which is the title of the post, and the guid which is the full url (before mod_rewrite changes it) so you don’t have to copy and paste the id and append it to your url.

If you need to search and replace some text in more than one post then you can use this sql :

UPDATE wp_posts SET post_content = REPLACE (
post_content, 'content management system', 'CMS');

That SQL query will replace the ‘content management system’ with ‘CMS’.

My webhost recently moved all my sites to a ‘stabilization’ server because my sites were using far to much CPU time and Memory. After reviewing the logs it looked like some bot from India decided to repeatedly scrape one of my sites in it’s entirety without any delays between requests. So the support team over there either requires me to correct the problem or upgrade to a dedicated server plan at ridiculous costs.

Since I didn’t really think that there was a problem I emailed back about the single IP address that was causing all the issues and took steps to prevent requests from that IP address from accessing the site. The support team replied saying that my usage was still high and that I still needed to correct the problem. A little frustrated, I did some research on how to improve my site’s load time and hopefully reduce CPU and memory usage.

Most of my sites use wordpress so I found a large number of articles geared specifically to optimizing wordpress blogs. Before I tried anything I backed up my entire public_html directory and did a dump of all my mySQL databases (took almost 20 minutes for the dump).

Dealing with Plugins
The first thing I did was upgrade all my plugins. Most wordpress plugins allow you to upgrade automatically so all you really have to do is click a button and all the work is done for you. I also deactivated and deleted a surprising number of plugins that I haven’t really had any use for recently. Apparently a lot of free plugins can cause large amounts of unneccesary load on your server due to the authors not really knowing or caring how well their software performs.

Dealing with spam bots
I have been using the Akismet plugin for awhile and it has been reporting large amounts of spam comments and pingbacks. It’s not really something that most people worry about because the spam is automatically deleted after a period of time. It does however increase server load, especially if it’s in the thousands of messages a day. I found this little mod_rewrite snippet to deny any blatent spammers that don’t have a proper referer :

RewriteEngine On
RewriteCond %{REQUEST_METHOD} POST
RewriteCond %{REQUEST_URI} .wp-comments-post\.php*
RewriteCond %{HTTP_REFERER} !.*codytaylor.org.* [OR]
RewriteCond %{HTTP_USER_AGENT} ^$
RewriteRule ^(.*)$ ^http://lemonparty.org//$ [R=301,L]

Cache and Compress
Since most of my pages rarely change it’s silly to generate every page for every request dynamically. After some reading I decided to use WP Super Cache to help optimize my WordPress sites. Of course just enabling Super Cache in the WP Super Cache plugin didn’t really improve load times for the end user but it should reduce server load immensely. What did improve load times drastically was the Super Cache Compression. This was a little more involved to get going but if you’re comfortable with copying and pasting code into a .htaccess file then it shouldn’t be difficult as long as your host supports mod_mime, mod_rewrite, and mod_deflate.

After going through all that, my sites now average at about half the load time they used to. Hopefully my web host feels that I’ve done enough to get off the ‘stabilization’ server so I don’t have to transfer all my stuff to another company.

TinyURLs can be very useful when you have a long url to type into something like an iphone or some other mobile device where the keyboard is rather tedious. On a few of my sites I was looking for a way to automatically generate a tinyURL with the least amount of effort. Since I use wordpress for most of them I decided to go with a shortcode.

Shortcodes seem to be gaining a fair amount of attention in the wordpress community and with good reason. The Shortcode API, which was new in wordpress 2.5, is a simple set of functions for creating macro codes for use in your posts. Shortcodes are written by providing a handler function and they accept parameters too. Here is a wordpress shortcode to generate a tinyURL for a post.

//Generate Tiny URLS For A Post 
function get_tiny_url($arguments)
{
  if(empty($arguments))
    $url = get_permalink($post->ID);
  else
    $url = urlencode($arguments['url']);
  if($url)
    {
      $tiny_url = 'http://tinyurl.com/api-create.php?url='.$url;
      $new_url = file_get_contents($tiny_url);
    }
    else
      $new_url = "Error";
    return $new_url;
}

add_shortcode('small_url', 'get_tiny_url');

If the url parameter is not defined then the function will attempt to use the wordpress function get_permalink($post->ID) which will return the current posts url. Also you can pass in a parameter if you want to show a tinyURL to another location. Using curl instead of file_get_contents would probably be faster but I didn’t want to make the example to long. The add_shortcode function is what tells the API to use the get_tiny_url function when it encounters the small_url shortcode. Note that this will call on the tinyurl api every page view so it would probably be prudent to set the tiny urls in the database so you only have to check once on a production site although I’m not sure if they expire or not.
This shortcode can be called in the post by typing :

[small_url]  // or
[small_url url='http://codytaylor.org']