My Personal Junkyard

Web Development, Wordpress

PyroCMS to WordPress Migration Tutorial

February 6, 2020
anson

Exporting PyroCMS data

1. Exporting all posts

2. Exporting files/images meta data (eg. featured images)

3. Exporting actual files/images from the server

Import PyroCMS data to WordPress

Note:

Each of the following process needs to be executed separately

1. Importing posts

1
2
3
4
# INSERT POST to wp_posts
INSERT INTO wordpress.wp_posts (post_author, post_date, post_date_gmt, post_title, post_modified_gmt, post_modified, post_content, post_excerpt, to_ping, pinged, post_content_filtered, guid, post_name)
SELECT 1, created, created, title, created, created, content, summary, '', '', '', CONCAT('http://localhost/?p=', LAST_INSERT_ID() + 1), slug FROM ___PYROCMS.TABLE___
LIMIT 1;

Note: Create a MySQL function to remove HTML entities for your content
Note: Change database database from UTF8 to UTF8 Unicode
At this moment, the imported posts will NOT have featured images

Updating/Fixing guid for all posts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
function syuap_fix_posts_guid(){
    global $wpdb;
    $results = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}posts", OBJECT );
 
    foreach($results as $result){
        $wpdb->update(
            "{$wpdb->prefix}posts",
            array(
                'guid' => SITE_URL . '/?p=' . $result->ID,    // string
            ),
            array( 'ID' => $result->ID ),
            array(
                '%s', // value1
            )
        );
    }
    echo "Done.";
}

Importing features images

1
2
3
4
5
6
7
8
9
10
# INSERT FEATURED IMAGE to wp_posts and attach its parent ID
INSERT INTO wordpress.wp_posts(
    post_author, post_date, post_date_gmt, post_modified, post_modified_gmt, post_content, post_title, post_excerpt, to_ping, pinged, post_content_filtered,
    post_status, ping_status, post_name, guid, post_type, post_mime_type, post_parent
)
SELECT
    1, created, created, created, created, '', image, '', '', '', '',
    'inherit', 'closed', image, CONCAT('http://localhost/wp-content/uploads/files/thumb/', image, '.jpg'), 'attachment', 'image/jpeg', (SELECT ID FROM wordpress.wp_posts WHERE post_name = ___PYROCMS.TABLE___.slug LIMIT 1)
FROM ___PYROCMS.TABLE___
LIMIT 1;

Importing featured images to wp_postmeta as attached file

1
2
3
4
5
6
7
8
9
10
# INSERT FEATURED IMAGE to wp_postmeta as attached file
INSERT INTO wordpress.wp_postmeta(
    post_id, meta_key, meta_value
)
SELECT
    (SELECT ID FROM wordpress.wp_posts WHERE post_name = ___PYROCMS.TABLE___.image AND post_type = 'attachment' LIMIT 1),
    '_wp_attached_file',
    CONCAT('files/thumb/', image, '.jpg')
FROM ___PYROCMS.TABLE___
LIMIT 1;

Importing featured images to wp_postmeta as thumbnail_id

1
2
3
4
5
6
7
8
9
10
# INSERT FEATURED IMAGE to wp_postmeta as thumbnail_id
INSERT INTO wordpress.wp_postmeta(
    post_id, meta_key, meta_value
)
SELECT
    (SELECT ID FROM wordpress.wp_posts WHERE post_name = ___PYROCMS.TABLE___.slug AND post_type = 'post' LIMIT 1),
    '_thumbnail_id',
    (SELECT ID FROM wordpress.wp_posts WHERE post_name = ___PYROCMS.TABLE___.image AND post_type = 'attachment' LIMIT 1)
FROM ___PYROCMS.TABLE___
LIMIT 1;

Adding metadata for your attachment

At this point, everything should works fine. However, if you go to Media, your attachment will not show proper meta data.

Upload assets

Contact me for PyroCMS to Wordpres migration service.

PyroCMS to WordPress DB Structure

Tags:
Back to top