MySQL Operation Help!

  • Hey there,

    I recently moved my biggest blog (3years of posts) from b2evo to WordPress :D, BUT there was a problem 1/2 way during the importing :/

    Basicly, about 1/2 of my posts are missing the “post name” entry in the field, the “post title” is there, but not that name, and unfortinatly it’s the “post name” which is used as the url for the posts, so the single posts and my sitemap are busted ??

    Here is what I need to do in phpmyadmin, run a operation on the “wp_posts” table to copy over the text from “post title” into “post name”, in the same fashion as it does when WP makes the post stubs, ie spaces become “-” and removed quesion marks etc.

    I hope someone can help me out with this, or I might have to go back to b2evo :/ ….. im sure this operation can be done in phpmyadmin, I just know know enough about mysql, i hope someone here does!

    Many Thanks for the help,
    Cheers,
    Matt

Viewing 3 replies - 1 through 3 (of 3 total)

  • Hi Matt,

    The easiest way to do this with MySQL is to produce a ‘dummy’ post_name value. The post_name value simply has to be unique amongst the other post_name values in the records in your wp_posts table. There’s no benefit from WP’s point of view in it being derived from the post_title value – that’s just a convenient way for WP to derive the value.

    So, if you’re okay with something like ‘post-1’, ‘post-2’, ‘post-3’ etc values for these particular records, do something like the following.

    Disclaimer: please make sure you have a backup of wp_posts before proceding, and you know how to restore that backup. I won’t guarantee this will work, and I won’t guarantee that it won’t cause other issues. In other words, you get what you pay for, which is free advice. ??

    Assumptions: I’m assuming the records you’re having problems with have either a zero-length string or NULL value in post_name. I’m assuming each record validly contains a unique integer value in the ID field.

    After reading the above, if you want to go ahead, issue the following:

    UPDATE wp_posts SET post_name = CONCAT('post-', CAST(ID as CHAR)) WHERE LENGTH(post_name) = 0 OR post_name IS NULL

    What this should do: the records with missing post_name values should now have a post_name value of ‘post-‘ with the unique id from the ID field appended. So, if the record in question had an ID value of 212, then post_name should now have the value of ‘post-212’.

    Hope this helps. Feel free to ask questions if you’re uncertain how to procede.

    Much warmth,

    planetthoughtful

    Thread Starter ma2t

    (@ma2t)

    Hey there planetthoughtful,

    Firstly, thanks you very much for your detailed and helpful reply :), very much appreciated. Although your idea would work, which is the main thing I guess, .. there is a problem, im very much in this game now for search engine optimization, and having the url’s that match the page title help, so this is what I am really going for, and one of the main reasons I changed to wordpres.
    Thinking about this now, I guess I should have mentioned that in my first post, sorry for not saying.

    Thanks you very much for your help though!

    Thread Starter ma2t

    (@ma2t)

    Hey there,

    Just wanted to give an update so no one wastes their time ??

    Im going to give the rss feed import a shot, that should work, assuming I can get past the 2mb file upload limit :/ lol.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘MySQL Operation Help!’ is closed to new replies.