Find ‘Click Here’ Anchor Text In Your WordPress Posts using SQL

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?

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]

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