2

I've been assigned to edit the canonical URL's (rel="canonical" found in the head)of about 600 posts on a client's site. Not all of the posts will use the same canonical url. Nor are these 301's or 302's. I'm told not to use a plugin because I would have to edit each post one at a time. These are the general steps I plan to take:

  1. Make an appropriate back up of the mySQL db in phpMyAdmin.
  2. Download a csv of the table containing the canonical url's (using phpMyAdmin export).
  3. Make a new csv that uses the post id and the new canonical url's (and a bit more magic).
  4. Import the new csv (in its corrected format using LibreOffice)through a plugin called Really Simple CSV, which will write the new url's to the database.

This is the tutorial I'm following for these steps.

My questions are: Using phpMyAdmin, what table is used to currently store these url's, and what, exactly, are they called? And, has anyone done this sort of edit before?

The site is currently on WordPress 3.9.x (it's outside of our scope of work to update their site to a newer version.)

Help please!

  • By canonical URL I take it you mean the post slugs? It's almost as quick to use Quick Edit on the edit posts list in the admin as it is to edit a CSV and you don't have the pain of re-importing. All the CSV import plugins I tried for a project recently filled my database with extra tables that I didn't want too. – Andy Macaulay-Brook Jun 8 '16 at 23:56
  • By canonical URL I mean the rel="canonical" found in the <head>. I'll edit this into the question above. – jtorres Jun 9 '16 at 0:01
  • Ah. And do the posts have multiple real URLs on your site? Are you aiming for each post's Permalink and the canonical URL to match? If so, just setting new slugs should change both unless you have a plugin or theme code adjusting your rel canonical fields. – Andy Macaulay-Brook Jun 9 '16 at 0:10
  • That would be too easy. What I have to do is change the rel="canonical" of www.site.com/category/story-about-something to www.site.com/category, or in some cases, www.site.com/projects/article-about-something/2 to www.site.com/projects/article-about-something – jtorres Jun 9 '16 at 0:14
  • Since they are generated and not stored in the db you are going to have to use some kind of plugin. Yoast SEO does it and explains how here yoast.com/rel-canonical That page also suggests that what you're planning will hurt your SEO if I've understood your question. – Andy Macaulay-Brook Jun 9 '16 at 0:19
2

Down- and uploading CSVs is a tough one that forces you to make a bunch of steps manually which is prone to human error (and typos). My suggestion would be to do something that you can battletest locally in a local copy of your DB. Steps to follow:

  1. Make a database dump using the mysqldump tool and use scp to copy it to you locally
  2. Pull up a local WP install
  3. Install WP CLI, the WP command line runner, locally
  4. Run wp post update with the necessary commands locally
  5. Test
  6. Save your live database by renaming it
  7. Upload your local copy of the database to your staging server
  8. Test again
  9. Make it your adjusted database your live database

Make sure that you have a time where no one fiddles with the database (take your site into maintenance mode!).

Notes:

A) In case you know the IP and port of your MySQL server, you can directly connect to it and pull the dump down to your machine without SSH-ing into remote:

mysqldump -P3306  -h127.0.0.1 -uroot -pYourPassWord databasename > ./dbdumps

Above will dump it in a subfolder of your current directory (pwd) named dbdumps. You should create it up front.

B) Assuming that you have Bash available (command line – sh will still be sufficient enough if you have not), you can use something like the following script. Add it to a file named convert.sh. Also assuming that you got WP CLI installed locally (or your Vagrant machine, Docker container, whatever dev environment you are using locally):

#!/usr/bin/env bash
for id in $(wp post list --field=ID); 
do
    wp post update $id --post_name=foobar
done

As you did not detail that conversion for your posts slug, you will have to find a way to adjust foobar in above example. There are dozens of Stackoverflow answers on the tag:bash topic to help you. As usual sed for the rescue – there's nothing in the world that can not get fixed with awk and sed in a shell script.

You can run above shell script like the following from your terminal:

sh convert.sh

Just give it a try, fail, rinse and repeat.

I've written that mostly out of my head, so you will have to test it. Better make a copy of your local DB so you can trash it start again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.