WordPress

WordPress: MySQL Search and Replace using PHPMyAdmin

I made a slight modification to my page layouts today. I’ve read on John Chow’s blog and on Problogger’s blog that positioning your advertisement within the body of a post can result in a dramatic increase in revenue. Dean is working on his as well.

On Darren’s site, he writes that it’s simply a matter of the readers eye movement. When the banner is at the top of the page, the reader skips over it with no focus. However, when the advertisement is to the right of the content, the reader will actually skim over it.

You’ll notice that I still try to keep my home page clean – putting advertisements outside of the blog posts. I’m certain that changing that and making them more intrusive could make me more revenue; however, I’ve always fought that because it would really affect the readers I care about most – the ones that visit my home page daily.

One of the issues with putting this advertisement in the top right was that this is where I often place a graphic for aesthetic purposes and to dress up my feed and differentiate it from other feeds. I usually alternate a piece of clipart either right or left in the post using:

Image Left:

<img align="left" src="pic.jpg">

Image Right:

<img align="right" src="pic.jpg">

Note: Some folks like to utilize styles for this, but the alignment doesn’t work in your feed utilizing CSS.

Updating every post using Search and Replace:

To easily modify ever single image in every single post to ensure all my images are left justified can be done quite easily using an Update query in PHPMyAdmin for MySQL:
update table_name set table_field = replace(table_field, 'replace_that', 'with_this');

Specific to WordPress:

update `wp_posts` set `post_content` = replace(`post_content`, 'replace_that', 'with_this');

To correct my issue, I wrote the query to replace “image=’right'” with “image=’left'”.

NOTE: Be absolutely certain to backup your data prior to making this update!!!

16 Comments

  1. 1
  2. 3

    Thanks for providing further information on this topic. I’ve seen ads left or right justified before on other web sites so it does seem a popular place. Your ads flow nicely on the right of the post.

    I may switch to right justifying my ads too in the near future. It would be interesting to see if any further revenue is generated as a result.

    • 4

      I’m going to definitely track them. Overall impressions are down a little right now, so revenue is also lagging. I’m going to give it a few weeks to see! I’ll be sure to report on it.

  3. 5

    Do you get anything with the banner ads on your index page, Doug? I didn’t do well with them.

    In general, the in-post ads (180 and 250 wide) and the ads after a post (336 wide) have gotten the most attention.

  4. 7
  5. 9

    have a problem to update secondds with “right” join in mysql
    UPDATE ivr_data SET RIGHT( TIME, 2 ) = ’00’ WHERE RIGHT( TIME, 2 ) != ’00’;

  6. 10

    Hey Doug. Just used your instructions to update my email address in my WP DB. Worked like a charm. Thanks.

    BTW, came across this post in Google, searching “using mysql search replace query.” Came up 3rd.

    • 11

      Woohoo! 3rd is good! My site seems to really have gotten some great placement in the Search Engines over the last year. Ironically, I rank above many Search Engine Blogs. 🙂

  7. 12

    this seemed to work better for my mysql…..

    UPDATE wp_posts SET post_content = replace( post_content, ‘replace this’, ‘with that’ ) ;

  8. 13

    This worked for me

    UPDATE wp_posts SET post_content = REPLACE( post_content, ‘www.alznews.net’, ‘www.alzdigest.com’ ) ;

    possibly the ‘replace’ needed to be capitalized

  9. 14
  10. 15

    Thanks! Mine insisted on using " not ' around the find and replace text. I was using it to move all the SQL data from one web site to another. It saved a lot of work!

  11. 16

    I recently wanted to replace a string within MySQL on the fly, but the field could contain 2 items. So I wrapped a REPLACE() within a REPLACE(), such as:

    REPLACE(REPLACE(field_name, “what we are looking for”, “replace first instance”), “something else we are looking for”, “replace second instance”)

    This is the syntax I used to detect a boolean value:

    REPLACE(REPLACE(field, 1, “Yes”), 0, “No”)

    Hope this helps!

Leave a Reply