Mysql – Change name of a post type without losing posts

custom-post-typeMySQLWordpress

I have made a site that uses custom posts types for a projects section.

I need to change the post type from 'projects' to 'galleries' but as I have already uploaded a bunch of projects was wondering how I would do this with as little as possible hassle (I do not want to have to re-upload all the images and text etc)

I found a few articles that tell me to do a SQL query to rename the posts

UPDATE  `wp_posts` 
    SET  `post_type` =  '<new post type name>' 
    WHERE  `post_type` = '<old post type name>';

And this one for the taxonomy

UPDATE  `wp_term_taxonomy` 
    SET  `taxonomy` =  '<new taxonomy name>' 
    WHERE  `taxonomy` = '<old taxonomy name>';

I just have no idea what I am supposed to do with this code. If it is SQL do I run it in a php file or is there some sort of 'terminal' that can be found in the WP dashboard or cPanel of my site?

Below is how I created my post type (Not sure if this helps)

function create_my_post_types() {
    //projects 
    register_post_type(
      'Projects', array('label' => 'Projects','description' => '','public' => true,'show_ui' => true,'show_in_menu' => true, 'menu_position' => 8,'capability_type' => 'post','hierarchical' => false,'rewrite' => array('slug' => '','with_front' => '0'),'query_var' => true,'exclude_from_search' => false,'supports' => array('title','editor','thumbnail'),'taxonomies' => array('category',),'labels' => array (
      'name' => 'Projects',
      'singular_name' => 'Project',
      'menu_name' => 'Projects',
      'add_new' => 'Add New Project',
      'add_new_item' => 'Add New Project',
      'edit' => 'Edit',
      'edit_item' => 'Edit Project',
      'new_item' => 'New Project',
      'view' => 'View Project',
      'view_item' => 'View Project',
      'search_items' => 'Search Projects',
      'not_found' => 'No Projects Found',
      'not_found_in_trash' => 'No Projects Found in Trash',
      'parent' => 'Parent Projects',
    ),) );  

} // end create_my_post_types

Best Answer

If you have CPanel access, you can look for PHPMyAdmin and run the SQL code there.

Go to PHPMyAdmin.
Select your wordpress database from the left.
RECOMMENDED: Backup your database first, by going to the export tab at the top and doing a quick export.
Select "SQL" from the top tabs.
Copy your SQL queries in the huge textarea, and click Go.

Hope it works!

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:

mysqldump -R old_db | mysql new_db

WordPress – Custom permalink structure: /%custom-post-type%/%custom-taxonothe%/%post-name%/

Lucky for you, I just had to do this for a client project. I used this answer on the WordPress Stackexchange as a guide:

/**
 * Tell WordPress how to interpret our project URL structure
 *
 * @param array $rules Existing rewrite rules
 * @return array
 */
function so23698827_add_rewrite_rules( $rules ) {
  $new = array();
  $new['projects/([^/]+)/(.+)/?$'] = 'index.php?cpt_project=$matches[2]';
  $new['projects/(.+)/?$'] = 'index.php?cpt_project_category=$matches[1]';

  return array_merge( $new, $rules ); // Ensure our rules come first
}
add_filter( 'rewrite_rules_array', 'so23698827_add_rewrite_rules' );

/**
 * Handle the '%project_category%' URL placeholder
 *
 * @param str $link The link to the post
 * @param WP_Post object $post The post object
 * @return str
 */
function so23698827_filter_post_type_link( $link, $post ) {
  if ( $post->post_type == 'cpt_project' ) {
    if ( $cats = get_the_terms( $post->ID, 'cpt_project_category' ) ) {
      $link = str_replace( '%project_category%', current( $cats )->slug, $link );
    }
  }
  return $link;
}
add_filter( 'post_type_link', 'so23698827_filter_post_type_link', 10, 2 );

When registering the custom post type and taxonomy, be sure to use the following settings:

// Used for registering cpt_project custom post type
$post_type_args = array(
  'rewrite' => array(
    'slug' => 'projects/%project_category%',
    'with_front' => true
  )
);

// Some of the args being passed to register_taxonomy() for 'cpt_project_category'
$taxonomy_args = array(
  'rewrite' => array(
    'slug' => 'projects',
    'with_front' => true
  )
);

Of course, be sure to flush rewrite rules when you're done. Good luck!

Best Answer

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

WordPress – Custom permalink structure: /%custom-post-type%/%custom-taxonothe%/%post-name%/

Related Topic