by Flo Preynat

I’m getting more and more requests from bloggers wanting to move from their blogging platforms to a private wordpress environment they will be able to manage as they wish, especially in terms of advertising.

Anyway cut a long story short, as I was testing a database move from Canalblog (which is a french blogging platform) to a local wordpress environment, thanks to an outdated plugin (still my best and only option), I ended up with an ok and usable database with a few things to be sorted by hand.

So what do you do when you don’t know shit about sql and want to mass correct data within your wordpress database tables?

Sequel Pro

First of all, and this is for mac users like me, you need to install Sequel Pro, an open source MySQL database administration tool which can do everything and more. It’s powerful, quick, user friendly (e.g. auto-completion), and it looks a tiny tad better than phpMyAdmin [lol].

Once in Sequel Pro, connect to your database using the MAMP config page, and rapidly browse your tables and various content.

Sequel Pro database setup

Right so back to my problem. All my posts had been imported with an extra string (“Commentaires sur”) in front of the post title and the post url.

Wordpress database import gone wrong

In Sequel Pro, click on the Query button, and query your database:

SELECT post_title, post_name FROM wp_posts where post_title like 'Commentaires sur%'

Sequel Pro Query select

Test your modified data by adding the function of your choice within your query. Here, I just trim the character string by removing the first 18 characters:

SELECT SUBSTR(post_title, 18), SUBSTR(post_name, 18) FROM wp_posts where post_title like 'Commentaires sur%'

Sequel Pro Query substring select

Once you’re happy with it, update your database (here I ran it in two blocks, one for post_title, then for post_name), and make sure you have no errors:

update wp_posts
SET post_title = SUBSTR(post_title, 18)
where post_name like 'commentaires-sur%'

Sequel Pro database update

…and voilĂ , database is updated and your posts are working as expected.

Wordpress database import gone wrong corrected

Obviously, depending on the issue, you may need to use a different string function. You’ll find them all here.

My name is Flo Preynat and I am the freelance webdesigner and developer behind shoogle designs. I live in France and specialize in responsive web design. Give me a shoogle or get in touch with me on twitter.

Most Recent Posts

Special Recent Posts

Sip: a color picker refreshingly simple indeed

Sip: a color picker refreshingly simple indeed

July 10th, 2014

I've just discovered Sip, a color picker app for Mac users. "Just discovered" since it's been around[...]

Perch – the CMS that does not pollute your web design workflow

Perch - the CMS that does not pollute your web design workflow

July 7th, 2014

I have recently finished a project based on Perch, the clever 'little' CMS created by edgeofmyseat.c[...]

Responsive video code snippet

Responsive video code snippet

June 29th, 2014

More of a reminder than a pure detailed blogpost, this code snippet will be my go-to resource when I[...]

Project Naphta: a nifty extension to play with text embedded in images

Project Naphta: a nifty extension to play with text embedded in images

June 9th, 2014

We've all been there. You want to select a good chunk of text on a website, only to realise you can'[...]

Free photos for your web design projects

Free photos for your web design projects

June 4th, 2014

Nothing beats using a real professional photographer when working on a cool project. You can be the[...]

Comments

  1. andre says:

    I use with mysql free tool Valentina Studio http://www.valentina-db.com/en/valentina-studio-overview at the moment. Does everything you need, and does it very well.