MySQL WordPress Database concat help needed

  • I’ve screwed up my database a bit in terms of the permalinks. I didn’t really think through what would happened when I changed the post_name to match the changes to the post_title.

    What I would like to do is change all of the posts after 8/30/07 to start with “revew-” in the post_name field and all the posts before 8/30/07 to not start with “review-“

    I know how to effectuate the last issue:

    UPDATE wp_posts SET post_name = replace( post_name, ‘review-‘, ” ) WHERE post_modified < 2007-08-30

    It’s the first part that I have issue with. Concat appends the data and I want to insert it at the beginning of the field.

    UPDATE wp_posts SET post_name = CONCAT(post_name, ‘review-‘) WHERE post_modified > 2007-08-30

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

  • Reverse post_name and ‘review-‘ in your CONCAT:

    CONCAT('review-', post_name)

    By the way, you do understand the point of a permalink and the use of the post_name in it, yes? :)

    Thread Starter JaneLitte

    (@janelitte)

    Well, my understanding is that permalink is what sets the post name, correct? and by manually changing the post_name, I was affecting the links to the site, right?

    the permalink is set by the post slug, which unless specified gets set by the post title.

    The post slug (post_name) is generated from the post title, and is (typically) assigned as the endpoint for the permalink, assuming of course one is using %postname% in their permalink structure.

    My point about the permalink is that, by changing the post_name you do alter the url to a post, which means any such post now provides a potentially broken link if *anyone* has ever linked to it using the original url. This includes search engines, directories, etc.

    Thread Starter JaneLitte

    (@janelitte)

    Right – and I didn’t really understand that until I changed it, checked my stats and noticed all these broken links. But there was a definitive date in which I started using “REVIEW” in my titles so my plan is to strip out review from all the post slugs(post_name) and then add it back in to all posts after 8/30/2007.

    The concat sql query was my attempt at that. Before I undertake that route, let me know if I am incorrect.

    I thought about making a 301 redirect, but I don’t think that it can be done or at least I can’t think of how it would be done.

    It would have to be a conditional one – i.e., if the link coming in is prefaced with review then strip out the characters “review-” but that seems pretty complicated if I can accomplish the same task with two simple sql queries.

    Please let me know though if I am not doing this right.

    when linking internally using the page id like https://domain.net/?page_id=14

    Can’t help on the redirects; and url rewrites through .httaccess is not one of my areas of expertise.

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘MySQL WordPress Database concat help needed’ is closed to new replies.