I love Lisa Barone. She helps me understand things like Search Engine Optimization (SEO). So when she posted today an article called ’10 Things EVERYONE Should Know about SEO In 2011′, I read it right away. Please take a second now to read it, as I’m going to help you fix problem #7.

7. [Click Here] is never suitable anchor text

Ok, so we should never use the words ‘Click Here’ to anchor tags, like a hyperlink. I’ve done this for years, because I like to tell people what to do. GO CLICK THIS NOW. And it would be underlined and hyperlinked, so the innerweb folks would be driven to click themselves off a cliff like little lemmings.

I read Lisa’s advice and took it to heart. So I immediately opened up my WordPress dashboard and did a post search on ‘click here.’ Massive-mega-fail! If WP had a fail-whale, it would have definitely been appropriate to show up after I did this search. Nearly every one of my 100+ posts came back as positive hits. This would do me no good. So I immediatley tried a search by quoting the string. Strike 2. Then I got serious, I pulled up ‘Google Search.’ Strike 3.

31 posts, really? Was I that bad?

Clicking and typing mostly wasn't helping me find 'Click Here', mostly

The searches were working OK, but I didn’t have nearly the control I needed to find exactly what I was looking for. I needed to be able to use my SQL (Structured Query Language) knowledge to pull up any variation of that phrase, but ONLY if it was inside a hyperlink.

Please Stop Reading Here If You Do Not Know How to Backup Your Database

So I decided to query the WordPress database directly with my favorite query tool for MySQL. This requires a few things:

  • You have access to where your WP site is running – for me it was remote
  • You have a MySQL login – easily created via my host server’s MySQL Databases control panel
  • You know how to write a query statement

So now I’m in my database and I’m able to query my posts looking for that search string! I have warned you already, and I’ll do so again: please have a backup of your database if you’re going to be poking around. Even if you’re only doing reads (SELECT), I don’t want you to break something accidentally because you got excited.

Here is the query I used first:
[sql]
SELECT distinct wp_posts.post_title
FROM MYDBNAME.wp_posts wp_posts –replace MYDBNAME with the name of your MySQL WordPress Database
WHERE (upper(wp_posts.post_content) LIKE ‘%CLICK HERE%’)
[/sql]

The upper function uppercases the text, then I use a pattern match search to bring back anything that has the words ‘click here’ included. We use the upper function so it will find text like ‘Click here’, ‘click HERE’, ‘ClIcK HeRe’, etc. This is a good start!

This WRONG, WRONG, WRONG

My Twitter buddy @rolandbouman gently ‘reminded’ me that in MySQL text searches aren’t case-sensitive. So adding the function here does nothing but add unnecessary steps, making the query run longer than it needs to. Thanks Roland!

Just use this instead
[sql]
SELECT distinct wp_posts.post_title
FROM MYDBNAME.wp_posts wp_posts –replace MYDBNAME with the name of your MySQL WordPress Database
WHERE (wp_posts.post_content LIKE ‘%CLICK HERE%’)
[/sql]

Post Titles That have 'Click Here' in the Post Content

A good start, but I could do better. I wanted to eliminate posts that I had only used the words ‘click here’ without using that as a hyperlink anchor. So i revised the query to
[sql]
SELECT distinct wp_posts.post_title
FROM MYDBNAME.wp_posts wp_posts –replace MYDBNAME with the name of your MySQL WordPress Database
WHERE (wp_posts.post_content LIKE ‘%CLICK HERE</A>%’)
[/sql]

This returned only two posts for me to work on. The ‘trick’ here was I assumed that my ‘Click Here’ hyperlink text would be immediately followed by the HTML ‘A’ tag being closed.

Now instead of clicking through a few dozen posts, I can concentrate on the 2-3 posts that I ‘messed up’ on.

Thanks again Lisa!

PS A Couple of Notes

  • I have the ‘Database’ WP Plug-In that works great for doing backups and general maintenance. I’m all for WP plug-ins and not re-inventing the wheel if possible. However, the query interface only supports INSERT-UPDATE-DELETE. So no go.
  • To connect to my hosted MySQL database from my PC, I had to add my IP to my trusted zone in my hosting service admin page
  • To see some of the data returned by Toad, I had to enable an option to retrieve large objects from the queries
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

4 Comments

  1. Hi Jeff!

    nice writeup 🙂

    Just noticed one thing:


    WHERE (upper(wp_posts.post_content) LIKE ‘%CLICK HERE%’)

    The upper function uppercases the text,

    I am not familiar with the wp database schema, but typically, in mysql text and varchar columns would use a case-insensitive collation by default. So, even without uppering the column contents you should be able to use LIKE.

    In this particular case, it should be a little bit faster to not use upper although I don’t expect a lot from it. But for indexed columns and queries that do not use a wildcard at the start of the LIKE pattern, it could mean a lot of performance gain, since applying a function on a column would prevent MySQL from using an index on that column.

    kind regards,

    Roland

    • JeffS

      Interesting, I didn’t even think about that, just assumed it was case sensitive…that’s my Oracle bias getting in the way.

      Running the query w/o the UPPER() still returns the same results, so you be right 🙂

      On the case collation – what a neat ‘trick’ MySQL, you get a +1 from me. Case Sensitive MySQL

      Thanks Roland, I’ll update the post to reflect that.

  2. Hi Jeff,

    I’ve read that advice before, and I agree with it, but somehow I still use Click Here from time to time – old habits die hard. I’m hesitant to poke around in my database, even though I do nightly backups, so I may just resolve to not use Click Here in the future. 🙂

    Ray

    • JeffS

      Sometime people tell me it’s hard to find the actual link in my posts – based on the styling or rendering in their browser. So I always used the ‘Click Here’ text, just so it was obvious. So I wonder – do we cater to our readers, or do we cater to the SEO gods in hopes that we gain more readers? Surely there is a way to have both?

      BTW – thanks for taking the time to read and sharing your opinion! It’s cool to have met folks over in EmpireAvenue from completely different interests. Not sure we would have ever crossed paths in the Twitter-verse.

Write A Comment