Moving multiple WordPress blogs to one Bluehost account

This post documents the steps of moving multiple subdomain blogs from my old host to Bluehost. I had about 8 blogs to move from Lunarpages to Bluehost, and by the time I was done I had this process down to a science.

These steps are almost certainly applicable to moving from just about any shared host to Bluehost, but they are written specifically to WordPress and to the process of moving WordPress blogs that are using add on domains / subdomains and exist in folders contained within the root (public_html) directory.

If you have a bunch of WordPress blogs on one host, you probably have them set up as subdomains like this:

  • techblog.mydomain.com
  • koreanbbqblog.mydomain.com
  • catpicturesblog.mydomain.com

Most “how to move your WordPress blog” steps assume you’re moving one blog, but this post will help you move lots of blogs. This guide also assumes you were technically savvy enough to get yourself into the situation of having multiple blogs on subdomains and isn’t written for first timers.

Also, this process will take the specific site you are moving down for the some part of the move. My steps minimize the downtime, but be aware that there will be some downtime while you copy your database over to your new host. I was okay with that (it was about 25 minutes of downtime per blog), but if you’re not, you may want to look into alternative methods of moving your blog(s).

If you aren’t already a Bluehost customer and are still shopping for an excellent shared host for your WordPress blogs, personal portfolio site, etc, take a look at their current deals below:

Before you begin

Have all of these things handy:

  • an ftp program (such as Filezilla)
  • old host website login credentials (oldhost.com)
  • old host ftp login credentials (ftp into your site)
  • new host website login credentials (bluehost.com)
  • new host login credentials (ftp into your site)
  • domain registrar login credentials (if separate)
  • about 1-2 hours to step through this process

Prep Steps

These steps can be done any time. They won’t take your blog down, and the wp-content copy step can take a while, so feel free to start them and continue the “Moving Day” steps later.

Step 0: Log into your existing WordPress installation.

While you’re in here, make sure it’s up to date. Disable any plugins you can live without. Disable caching plugins. (You can keep all plugins enabled, but I find that they move more easily when disabled.)

Step 1: Copy your WordPress blog content to your local hard drive.

Open Filezilla (or whatever) and FTP into your old host. I use Filezilla 3.8, but you should beware of this bug with Filezilla affecting Bluehost users if you’re on 3.10 and reading this in early 2015.

Navigate to your WordPress install folder. It’s probably a sub folder of public_html.

Copy wp-content to your local hard drive. This is where all of your blog’s content is kept, and it’s really the only WordPress thing you need to copy over. Everything else will be handled by a fresh install of WordPress on your new host.

Fun fact about Filezilla: by default, there are speed limits on upload and download. Remove them by going to Transfer > Speed Limit.

filezilla_speed_limits

Step 2: Copy anything that’s in your subdomain blog’s root folder over to your local hard drive, too.

Did you upload a favicon, a banner, an .html file for identifying your site to Google webmaster tools, etc? You might have things in your site’s root folder, so copy those things over, too.

Step 3: Copy your blog’s sql database to your local hard drive.

  1. Log into your old host’s cPanel.
  2. Find phpMyAdmin and log in.
  3. Find the database associated with your blog (and click it)
  4. Click Export in the toolbar at the top
  5. The default settings are fine
  6. Click OK
  7. You’ll download a .sql file – hang onto this for later

export_db

If you have lots of blogs on one host, you might have lots of databases and they may have cryptic names. If this is you, look in the table with the _options suffix to verify which db goes with which blog.

check_options

Wait, there’s one more thing!

How large is your .sql file? If your .sql file is under 50mb, skip ahead to “Moving Day”. If it’s larger than 50mb, I have some bad news: Bluehost won’t let you upload it via phpMyAdmin. You’ll have to use .ssh and the command line to upload your database. Those steps are further down in this guide, but they’ll add 1/2 to 2 hours to this process depending on how experienced you are with .ssh and how quickly you can get set up and logged in.

If your database is under 50mb, you can keep following along in the next section.

Moving Day

These steps will take your blog offline while you complete them. Expected downtime is less than an hour, depending on your db size.

Step 4: Change your nameservers to Bluehost’s (or whoever’s). 

Log into your registrar (I use and love Dynadot) and find the domain of the blog you are moving. Change its nameservers to the nameservers Bluehost tells you to use for your account.

For me, that’s:

ns1.bluehost.com
ns2.bluehost.com

Step 5: Add the “add on domain” to your Bluehost account

Log into Bluehost, go to cPanel, and look for Add on Domains. Enter your domain into the field and wait for Bluehost to validate it.

assign_domainAfter Bluehost validates your domain, scroll down. Keep the “add on domain” radio button checked. Create a new directory for this add on domain. I like to name my add-on directory after the WordPress site it’ll soon hold.

Step 6: Return to cPanel, install WordPress.

Bluehost has (or at least had) a quick “Mojo Marketplace” WordPress installer. I like to reconfigure the defaults and name my site SiteNameBLUEHOST to help me identify its database later on, since Bluehost gives the WordPress databases cryptic names.

Choose your subdomain out of the list (I always pick the one without the .www, might just be personal preference).

Wait for the install to complete.

Step 7: In your FTP program, log into Bluehost and upload wp-content into the new install’s directory.

You just installed WordPress, so navigate to its folder via your FTP program and when you find wp-content, copy your site’s version of wp-content over it. This step may take a while.

Step 8 (small database): Replace that new Wordpress installation’s database with your site’s exported database. (This only works if your database is under 50 mb).

While wp-content copies, you can log into Bluehost’s cPanel again and go to phpMyAdmin.

Find the new database and click it. If you have a lot of databases, look in the table with the _options suffix to identify the correct one. (This is why I like to name my new blog installation something identifiable, especially when dealing with multiple sites. That blog title you entered at installation time will show in _options.)

With the correct database open, click “Check All” and choose With Selected: “Drop”. (Drop is database speak for “delete”).

phpmyadmin_drop_tables

Now use Import to import your existing .sql file into this database. Note the prefix used. (In my screenshot above, the prefix is wp_ but not all of the databases I imported came with wp_.)

Step 8 (large databases over 50mb): Log into your server via ssh and import your gigantic database using the command line.

If your database is over 50mb, congratulations – you get to use. ssh to upload your database instead because Bluehost’s implementation of php doesn’t let you modify the maximum upload size (BOOO).

Bluehost offers some guides to this process, which (when put in order) are basically:

  1. Enable SSH on your Bluehost account
  2. Generate a set of public/private keys
  3. Set up Putty if you’re on Windows and log into Bluehost via Putty
  4. Import your MySQL database via command line

The import command is really the only tricky part. It needs all of the following:

  • Your database’s username. This is not your Bluehost account name. The database user is defined when you set up the database and is probably prefaced with your db name. You can see a list of users associated with your databases by clicking on MySQL Databases in Bluehost’s cPanel and scrolling all the way down to where the users are kept.
  • Target database’s name. This is the database you’re going to overwrite. By default, Bluehost WordPress databases have cryptic names like youraccountname1_wo1234. If you have a lot, make sure you know which one is the one you want to overwrite.
  • .sql file name. You have this on your hard drive, and you’ll need to upload it to your account into a place you can find easily while you’re in the terminal (you can just dump it into public_html via your ftp program, just remove it when you’re done).

Go back to your FTP program (hopefully wp-content is done copying over by now) and upload your .sql file somewhere on your Bluehost account. I just dumped mine into public_html (you can remove it later). (What was that about .ssh being more secure?)

Now go log in via Terminal (Mac) /Putty (Windows).  Remember, you are on your account’s part of Bluehost’s server, not your local hard drive. Use pwd and ls to get your bearings. Navigate (cd foldername) to the folder you uploaded your .sql database file and run the command that imports it.

That command will look something like this:

mysql -p -u username_wo1234 username_wo1234 > yourdb_filename.sql

Uploading through .ssh got the job done, but using it for the first time came with a lot of kinks to work out and added nearly 2 hours to my site’s downtime. Hopefully, the steps detailed above will help you do it faster than I did.

Step 9: Hook up the database by ensuring $table_prefix matches.

If you’re here, congrats – you’re through the hardest parts. There’s a small chance your site is already working.

There’s a larger chance, however, that your site is just a white page or a database connection error message. This step fixes the database connection problem. If you don’t have that problem, skip ahead to the next section.

In Bluehost cPanel, go to File Manager.

file_manager

Navigate to public_html and click on the directory where you’ve set up your blog. Inside, you should find wp_config.php. Right click the file and choose Code Edit.

wp-config-right-click-edit

Inside, look for $table_prefix on or around line 65. Make sure whatever’s here matches what your db actually uses as a prefix.

wp_prefix_table_wordpress_bluehost

 

If you had a database connection problem, there’s a good chance this solved it.

Step 10: Disable plugins to fix blank white WordPress page

If you’re getting a blank white page, try logging in directly via yourblogurl.com/wp-admin. If you can get in, try disabling plugins until the site loads.

If you can’t get in, go into File Manger and find the plugins folder. Right click it, choose rename, and rename it something else – like pluginsX. Doing this will disable all of your WordPress plugins. Now try yourblogurl.com/wp-admin. If you can get in, reactivate plugins from within your admin panel.

If none of that works, try these steps from Bluehost to try to fix the “white screen of death”.

I found that firewall and security plugins were the most likely to get messed up in the move process and, at worst, had to be reinstalled from scratch.

Step 11: Help, all my blog links are DEAD! 404s everywhere!!

In your new WordPress installation, in the dashboard/admin column down the left side, go to Settings > Permalinks. Note the structure they are currently using.

Select Default (so your links look like http://yourdomain.com/?p=123). Save changes.

Try your links now – do they work? If so, go back to Permalinks and change them back to the way they were. If they don’t work, try editing a post and saving it.

Step 12: Set up email addresses, forwarders for your site. 

Chances are, you had some nice emailaddress@yourdomain.com for each of your WordPress sites, probably with forwarders to the main address you use. This step is just a reminder to go to Bluehost’s cPanel and recreate those, along with the forwarders.

Step 13: Move banners, favicons, Google tracking codes, .htaccess, etc back into root folder.

If your sites are like mine, you have a few special things in the root folder of your site. This is a reminder to re-upload those things to your new host.

All done!

At this point, your subdomain blog should now be fully moved over to Bluehost! Well done!

I hope you enjoyed this guide, and if you spotted any errors or outdated information, please let me know in the comments.

And, in case you’re curious, yes, I love Bluehost. I was a Lunarpages customer for 9 years but Bluehost outclasses it in every way – bandwidth, CPU allocation, ability to handle the traffic my blogs collectively pull in, uptime, ease of use, and customer service.

If you’re not already a customer, check ’em out – Bluehost is my favorite shared hosting service for my blogs. (Read more about my blogging-for-profit hobby here).


Note to readers: Tilcode is a participant in Bluehost’s affiliate program.

Blogging for bucks: Year 1 report, mistakes made, lessons learned

Today I’m going to talk about my blogs and how they did this year. Since this is the first post in what I hope will become an annual (or even more regular) series, I’m going to share a long-winded history of my blogs, too, so that you can learn what I did and what my first year of blogging was like.

Just to set expectations, I didn’t make a mint in 2014. I made around $3500 this year off Amazon Affiliate and Google Adsense combined. Nearly half of that amount was made in the last three months of the year. To put the amount in perspective, we spent about that same amount on food in 2014 (for two adults). Or, it’s 10 payments on a car leased for $350 a month. Hey, I’ll take it! I know there are SEO wizards making enough to buy a new Lamborghini every day with their blogs, and that’s awesome, but that didn’t happen to me (yet :D).

However, everything I did is 100% doable by you if you’d like to try out blogging for a little side income!

Backstory

I’ve been putting content online since about 1997. My early efforts were what you’d expect from a 13 year old: a Tamagotchi fan site on Tripod, an AOL site dedicated to my dog, my Sailor Moon fan art. It was so rewarding to share stuff I cared about and find (small) audiences for it! I even made some really good friends through my sites and artwork.

Monetizing the content I made, however, never really occurred to me. I suppose I might have said, “Who would pay me for this? The Internet is full of free stuff!”

Sharing stuff online became nearly everyone’s hobby as the Internet’s popularity exploded and sharing stuff became easier and easier (and this is awesome – no matter how obscure a thing is, 99.9% of the time I can Google it and find someone talking about it).

Nonetheless, in 2013 I found inspiration in the works of bloggers like Young House Love (now retired?) and Smart Passive Income (written by a smart guy who makes a lot of money online by telling others how to make money online).

The formula couldn’t be simpler: build a site on a profitable topic, get traffic, earn money through affiliate sales. Sounds good to me!

First Blogs

April 2011: House Blog

A few years ago I bought a house that needed a lot of work done. I thought it would be fun to write about it and document the projects. (It was.) I bought a domain and put a WordPress blog on it.

House Blog was all over the place in terms of content, and it certainly wasn’t set up to make money. No ads or affiliate links. I was giving my posts “clever” titles, not SEO-friendly titles. And I certainly wasn’t writing with revenue in mind.

House Blog just sat there, getting a new post whenever I felt like it (maybe once a month) but it had a nice little trickle of 20-30 visitors a day.

The blog was 2 years old when I got interested in “passive income” from blogging. I discovered the Amazon Affiliate program through a couple other blogs I followed. I wanted to see if it would work for me, so I re-wrote a couple of my older House Blog articles (and wrote a few new ones) to include Amazon Affiliate links to appropriate products.

Within a week, I had my first sale! :O

I made about 30 cents off it (lol). But that was enough to convince me the formula worked and I could scale it from here.

I decided to start a new blog (and keep House Blog, of course). This one would be more focused. Rather than try to be an all-over-the-place home renovation blog (which was seriously hard to generate content for: how often do you replace a toilet?), I’d choose fewer topics and discuss them in excruciating detail (which I enjoy).

July 2013: Craft Blog

Actually, what happened was I started a whole bunch of blogs, all centered around different topics. (Sigh)

Craft Blog is the only one of that batch that has made any money to this day, so I’ll just talk about it. (Lesson: don’t run out and buy 5 domains the second you have a few ideas.)

Craft Blog’s domain was purchased in the first week of July 2013. I put some lorem ipsum content up while I enthusiastically spent an entire weekend customizing the theme (another mistake: it’s not necessary to spend hours – or days – customizing the theme until you have steady traffic). On the bright side, I learned a lot about WordPress and CSS during those early days of obsessing over the site’s design.

Setting up a new WordPress site, customizing it, and planning its articles gave me a much-needed creative outlet. I wasn’t challenged in my day job, so having this site to look forward to at the end of the day was very exciting and motivating.

A few days later, a visitor came to the site! Oh no! I had spent all my time customizing the site’s design. The only content on the site was placeholder junk!

I loved working on Craft Blog, so banging out a few pages of content that fit the site’s niche was easy and fun. For my “monetization” articles, I reviewed stuff I either owned or had used, and I made recommendations based on my own personal wishlist and research. (If there’s one thing I love more than buying new toys, it’s researching those new toys for weeks prior.) I added Amazon links to help visitors find the stuff I was talking about.

Of course, I had lots of ideas for articles that weren’t Amazon-oriented, and I wrote those, too. I love writing tutorials, so I also put some nice Photoshop and Etsy tutorials on the site. End result: the site looked better for the 1-2 visitors it was getting a day.

By August, I had 12 good articles. Some were long (1500 words), some shorter (500 words), but all were nice original content I wrote myself. There was very little traffic at this time, maybe 25 visitors the whole month.

I did a Pinterest blitz around this time, hoping to “go viral” there, but that never happened. I don’t enjoy Pinterest, so I was pretty quick to let that part of my blog marketing slide. In fact, I let most of my marketing efforts slide. I might be allergic to self-promotion. I found it too difficult to do a lot of the stuff the experts say you should do to market your blog, and I was content, at least for now, to just write useful content and post it on the site.

By September 2013, I was up to 22 articles on Craft Blog. I only had 147 visitors total that month (about 3 months into the site’s life), and no sales, but I was enjoying the project so much I just kept going through October when I decided to start a new site and let Craft Blog coast for a while.

October 2013: Disney Ride Blog

In September 2013 I went to Disneyland. It was great! I caught a bad cold when I returned home, though, and had nothing to do but lay in bed and think about how much fun I had at Disneyland. I decided to start a blog about my favorite Disney ride. It would just be a nice place to collect all the history, legends, and secrets of the ride in one place. I bought a domain and wrote a couple short articles about the ride and what I knew about it.

Within a few days, the site was getting traffic.

Whoa, what?!

In its first 30 days, Disney Ride Blog got more traffic than Craft Blog had had in its entire 4-month life. This inspired an obsession with the site, which I worked on regularly for the next two months. By January 2014, the site had its first 100 visitor day!

The site was a traffic monster, but it was making almost no money (literally pennies a day, if anything).

I think the site stood out because of a lack of competition. Lesson: if no one else is in a niche, maybe that’s because there’s no money in it? :D

Unfortunately, while Disney Ride Blog was my strongest traffic site almost until the end of 2014, it has barely made a dollar. I love the site and it’s easily one of my favorite hobbies, but it just eats up bandwidth and brings in nothing. I’ve experimented with ad placement, Affiliate links, etc, but it’s stubbornly unprofitable.

Fortunately, Craft Blog got a few clicks and sales before the end of 2013, which gave me some ideas as to what to do next for making a “money” blog.

2013 blog income: $17.16

Yup, $17.16 for three sites over the whole year. Rollin’ in it.

2014 in Review

January 2014: Gizmo Blog

Six months into this “blogging for bucks” endeavor I had been writing content for three blogs fairly regularly and seen traffic grow accordingly, but between Adsense and Amazon Affiliate I had made a grand total of about $17 for the entire year. I think a lot of people would have called it quits at this point, because that’s a pretty embarrassing return on investment.

Not me! Haha, I bought a new domain in January, this one for a particular category of “smart home” gadgets I was very interested in.

Gizmo Blog would be the culmination of all my learning thus far: niche topic that not many people were covering in great detail yet with a focus on customers on the verge of making a purchase (and Amazon Affiliate links to guide them to the product’s Amazon page).

With one comparison article (including a comparison chart and about 1500 words of original written content), the site had its first visitors from Google and its first Amazon link click within 7 days (!!!).

January 2014 blog income: $8.66

Hey, that’s nearly half of what I made over half of last year!

I added a few more articles to Gizmo Blog cover the basics and then decided to let it sit. In the meantime, I did some footwork: I went to hardware stores and even an open house to see the products in person, since it would be impractical for me to install all of them into my own home. This helped me write smarter “hands on” reviews. It helps that I genuinely find the technology interesting – I can’t imagine writing a site like this without loving the thing you’re writing about.

February 2014: Double digit earnings!

February went better: as traffic grew (1660 visitors across all sites!), so did clicks to Amazon. I had my first double-digit earnings month. I didn’t do anything special or get any links, I just added a few new (long) articles to each site. From humble beginnings…

February 2014 blog income: $35.63

May 2014: First $100 month

Three important things happened between February and May 2014:

Thing 1: One of the manufacturers of a product I reviewed on Gizmo Blog tweeted a link to the review! This brought in a surge of traffic (58 in a day, woohoo!) and seemed to legitimize the site a bit in the eyes of Google because from this point on, traffic kept climbing – sometimes doubling with each passing month.

Thing 2: In May 2014, someone linked to one of my reviews on a forum, which is like a gift that just keeps giving because it not only sends regular traffic, it counts as a quality backlink in Google’s ranking algorithm.

Thing 3: I quit my day job and focused all my time on blogging and growing my web developer skills.

May 2014 blog income: $115.15

During the spring I also made a better effort at marketing the blogs. I created a Facebook, Twitter, Pinterest, and Google+ presence for all of them and re-tweeted / re-posted content to them regularly (1-2 times a week) for a while.

I don’t know how to make something “go viral” and I don’t enjoy spending a lot of time on social media doing what feels like an elaborate “look at me” routine, so again, I let marketing efforts fade out after a while.

Traffic grew steadily across all sites, which was great. Disney Ride Blog remained by far my strongest traffic-puller, but my weakest earner.

June 2014: My host complains

My long-time web host (Lunarpages, since 2005!) served me with a ticket and a complaint that my sites were consuming too many resources. This started my mad scramble to optimize my sites. Ultimately, I added W3 Total Cache to my sites which took a load off the servers (for a while – see December 2014). Without caching, each of my sites was re-loading all of its resources every time users navigated around the site.

At the time, I was surprised that WordPress didn’t come with optimizations built-in. It’s up to the user to add things like caching, lazy loading of images, minification of CSS and JS, automatic backups, and security measures like limits to login attempts.

Lesson: optimization becomes very important once your site(s) are bringing in around 50+ visitors a day.

October 2014: Holiday season begins

The summer was very good by my beginner standards: about $140 a month on average for June, July, August, and September. At least, until October raised the bar.

I added zero content between July and September, thanks to attending the full time Code Fellows Dev Accelerator and having negative free time for blogging.

Yet traffic continued to grow! The best part was, for the first time, my blogs were truly earning “passively” since I had done absolutely nothing on them. October really turned things around – nearly $600 in earnings, plus I’d done nothing on the sites in months.

October 2014 blog income: $594.19!

This sudden uptick in earnings inspired me to put those new web dev skills to use and optimize all the sites for mobile, which may explain at least some of the traffic increase that I saw in November and December. Disney Ride Blog and Gizmo Blog had been virtually unusable on mobile, now they work great on phones and tablets.

December 2014: Shopping season ends, host complains again

If October was great, then November was spectacular: $870 in earnings! I’ve read from other bloggers that the last three months of the year are the best for Amazon Affiliates and ecommerce sites, and I believe it. What a great way to end the year!

rue Color Image

However, I expect the earnings to fall back to their summer levels (if not lower) as soon as Christmas comes and goes.

Alas, my host complained again in December. They don’t have a problem with my bandwidth usage (that’s “unlimited”!) but they do have a problem with CPU resources being used, some of which are used every time someone visits one of my sites. With average daily visitors now around 3,000 for my sites combined, I was hitting it too hard for them.

Unfortunately, traffic peaked at the same time some bot network attacked many of my sites (or maybe the bots were always there, but weren’t causing any trouble until traffic reached a certain point). From the logs, it looks like at least one bot was trying to get in through wp-admin, and another seemed to be hitting the comments functionality of several of my sites.

I played wack-a-mole for 5 days trying to solve or at lease reduce the effect of the various attacks across my many sites. I banned IPs, tweaked cache settings on my WordPress sites, added login attempt limiting plugins, turned off plugins, turned on and off themes… no one thing was causing the high CPU usage. Lunarpages doesn’t offer great tools for debugging and the update time is slow – as much as a day before I can see if what I did had any effect.

It was time for something drastic.

I moved Gizmo Blog off Lunarpages and onto DigitalOcean, a scalable VPS that is probably more appropriate for a site that continues to grow in traffic every month.

digitalocean

I’ve been with DigitalOcean less than a week and while the process of moving the site over and locking it down security-wise took some time, the end result seems to be an immediate reduction in traffic to my stressed-out primary host. You can see the drop in traffic on December 16th- that’s the day Gizmo Blog left Lunarpages for greener pastures.

gizmo_blog_moved

It was probably inevitable, because this is how Gizmo Blog’s traffic is trending:

gizmo_blog_traffic

To Lunarpages‘s credit, they did not kick me out or shut down my blogs in response to my unintentionally high CPU use. In researching this CPU problem over the past week, I’ve found many bloggers who did get shut down by their hosts for this sort of problem.

To address my WordPress CPU usage problems, I used a combination of:

  • W3 Total Cache and WP Super Cache (I think I like Super Cache better so far)
  • BJ Lazy Load so image-heavy posts load images on an as-needed basis
  • WP smush.it for negligible image optimization (I already use save for web on all images, your mileage may vary)
  • Simple Firewall for better login security and other firewall features
  • WP Clone for moving Gizmo Blog to a new host with minimal pain
  • WP Optimize for clearing unused revisions out of the database
  • Disable Comments an emergency measure I deployed to help reduce the load on the server
  • WP Maintenance Mode another emergency measure I used to shut down Disney Ride Blog while I experiencing CPU overages (unprofitable sites are the first to go in times of need :P)
  • GTMetrix and its WordPress plugin for monitoring site load time and performance
  • CloudFlare free version on my highest traffic sites (how CloudFlare works)

It’s too soon to say if this is the end of the CPU overages saga, and I suspect it’s not. I’m currently using three hosts for my sites (Lunarpages, BlueHost, and DigitalOcean), all of which have been satisfactory in terms of what I expect from them (in other words, I don’t expect world class speed and performance out of a host charging me $5/month for shared hosting). Craft Blog will most likely be moving onto its own VPS in the near future and I may try someone other than DigitalOcean and see how they compare.

What’s next?

I don’t think I’ll start any new blogs this year. Maintaining individual blogs has become time consuming. Every update to WordPress has to be deployed individually to each site, and problems like CPU overages and hacker attacks often have to be debugged on all sites.

As for the ones I have, I plan to add content, continue to optimize the sites, move them to more suitable hosts, and just keep ’em growing. I’m eager to see where earnings fall to in the first months of the year, and whether traffic can continue to grow on its own during times where I don’t add content regularly.

If you (TILCode reader!) have enjoyed this break from coding talk, let me know in the comments and I’ll share blog updates more regularly. I’m not sure the web needs yet another site on how to blog for bucks, but I could be wrong. :) Hope you enjoyed this massive first installment!

Remove Unwanted Characters from WordPress Post Content after WordPress MySQL Migration

Today I learned… how to remove unwanted characters like â€ and Á from WordPress post content using a MySQL query.mysql_logo

Backstory: I recently migrated a blog from shared hosting to a VPS. This experience alone could fuel TILCode content into 2024, but this particular “TIL” article is about a curious character encoding artifact that occurred as I exported the WordPress MySQL data and imported it elsewhere: Á and â€ symbols everywhere!

Removing these unwanted symbols from my WordPress posts required running a few queries on my MySQL databases. Here are the steps I used.

(And if there’s a better/easier way to do this, please let me know in the comments. This was my first foray into MySQL queries, but it got the job done.)

Step 1: Log into PhpMyAdmin

Access phpmyadmin through your cPanel or yourdomain.com/phpmyadmin – access varies by host and setup

Step 2: Navigate to the SQL tab and change collation

Click on the “PhpMyAdmin” logo so that you’re at the “root” level (and not inside a database – this might work from inside a database, but I did it from outside).

Click on the SQL tab.

mysql_button

Here, you’ll get a large window in which you can type queries – operations you perform on your data. 

mysql_queries

The first query to run is one that will set the character encoding type. 

ALTER DATABASE your_db_name_here DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I learned this from JesseWeb’s helpful tutorial on resolving WordPress character issues, but his code didn’t work for me as-is (I suppose that’s not surprising considering his guide is nearly 5 years old). I had to remove the quotes from the database name.

Your query will look like the image below. Be sure to replace your_db_name_here with your database’s actual name (look in the left column for your db names).

Click the “Go” button on the right to run the query.

mysql_queries_in_place

Step 3: Remove unwanted characters from existing posts

For this query, you need to navigate to the actual database itself. Click on its name on the left.

navigate_to_mysql_database

Click on the SQL tab. Now the helper text says that queries are run on the database you selected.

queries_inside_db

Enter this query string to replace all instances of â€ with an empty space:

UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '');

Repeat the query for every unwanted character. Note also that you can stack ’em up in the query window like so and run ’em all at once:

UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'Á', '');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'foo', 'bar');

You may also need to clean up comments. You can do that with:

UPDATE wp_comments SET comment_content = REPLACE(comment_content, 'Á', '');

See shadez’s question on WordPress.org for more examples – his example was instrumental in helping me understand this issue.

And with that, all the unwanted characters are gone. Phew!

WordPress Genesis Framework – Showing a List of Post Titles in Category View

Today I learned… how to customize the “Category” view in WordPress (Genesis framework) to show just the post titles as ordinary links in a list.

I really like the way posts of a category are displayed on DailyBlogTips. Each post gets a bullet in a simple unordered list.

posts_as_list_items
DailyBlogTips’s category view shows just the post titles in an unordered list.

By default, WordPress (and Genesis) gives you two options for displaying posts by category: either their full form one after another (do not want), or a truncated version with the post title and excerpt (also do not want).

A solution in which the titles were rendered as <a hrefs> between <li></li> tags required modifying the loop when on a Category page.

Please note that this code requires a theme built on the Genesis framework, though it should not be hard to modify the hooks to suit your framework if you know your way around a bit.

Copy the contents of functions.php into your child theme’s functions.php file.

Here’s what this is doing, in English:

When the current page is ‘Categories’ (Line 9), don’t do the usual genesis_loop (Line 13). Instead, do this custom loop (Line 14) called mjg_custom_loop.

Over in mjg_custom_loop, create a new unordered list (Line 21) and for each post (Line 22), echo its permalink and its name into a set of <li> </li> tags (Line 24).

The contents of style.css will probably need to be customized to fit your site’s design, but hopefully this is enough to get you through the hardest part which is customizing the loop on the category page.

Here is how it looks on the site:

category_links_only
Titles-only Category view on TowerSecrets.com