Tikiwiki Migration to WordPress Notes – Part 2

This article follows up on Tikiwiki Migration to WordPress Notes – Part 1. You’ve now got a working WordPress site with your old TikiWiki articles in it. However, there’s some further imports we can do to use some of the Tiki features such as topic images.

One nice feature in Tikiwiki is that each article/topic was assigned an icon. To get these icons into WordPress, there are a couple of steps.

First, you’ll want to upload your topic images from Tikiwki into WordPress. The easiest way to do this is to install the plugin Add From Server. Once installed, check the settings under Settings -> Add From Server.

Under User access Control select:
Any users with the ability to upload files listed below : admin

Copy over images from Tikiwiki (probably in images/topics) to /path/to/website/wp-content/

Then click on media -> Add From Server

The images should all get uploaded and appear somewhere like /path/to/website/wp-content/uploads/2003/11/stuff.gif

The next step is associate each icon with a category. This is done in the WordPress database.

I should stress at this point that the following steps are at your own risk and are unsupported. Although this worked for WordPress 3.3.1 in my environment, there’s no guarantee that this will work for you. And ALWAYS back up your database prior to performing any database changes.

Here’s the basic process.

The uploaded files now appear in the wp_postmeta table. Let’s say I want to associate all the ‘dotdance’ topics with my dotDance icon.

Step1: Identify the post_id for the image you want to use:

mysql> select * from wp_postmeta where meta_key=’_wp_attached_file’;
+———+———+——————-+——————————+
| meta_id | post_id | meta_key | meta_value |
+———+———+——————-+——————————+
| 716 | 325 | _wp_attached_file | 2003/11/dot_dance.gif |

Make a note of the post_id associated with the image.

Step 2: Next we look in wp_terms to find the dotDance category:
mysql> select * from wp_terms;
+———+——————————+——————————+————+
| term_id | name | slug | term_group |
+———+——————————+——————————+————+
| 9 | dotDance | dotdance | 0 |

Step 3: We need to match this term_id with the term_id in the wp_term_taxonomy table:

mysql> select * from wp_term_taxonomy where term_id=’9′ and taxonomy=’post_tag’;
+——————+———+———-+————-+——–+——-+
| term_taxonomy_id | term_id | taxonomy | description | parent | count |
+——————+———+———-+————-+——–+——-+
| 16 | 9 | post_tag | | 0 | 49 |
+——————+———+———-+————-+——–+——-+
1 row in set (1.91 sec)

Step 4: We now need to find all posts with this term_taxonomy_id=16 (dotDance) category:

mysql> select * from wp_term_relationships where term_taxonomy_id = 16;
+———–+——————+————+
| object_id | term_taxonomy_id | term_order |
+———–+——————+————+
| 26 | 16 | 0 |
| 56 | 16 | 0 |
| 57 | 16 | 0 |
| 63 | 16 | 0 |

So we know post’s 26, 56, 57 and 63 are all in the (dotDance) category.

Step 5: To make these posts use our image (image id 325 found in step 1) we can simply insert entries into wp_postmeta as follows:

insert into wp_postmeta (post_id,meta_key, meta_value) values (’26’,’_thumbnail_id’,’325′)
insert into wp_postmeta (post_id,meta_key, meta_value) values (’56’,’_thumbnail_id’,’325′)
insert into wp_postmeta (post_id,meta_key, meta_value) values (’57’,’_thumbnail_id’,’325′)
insert into wp_postmeta (post_id,meta_key, meta_value) values (’63’,’_thumbnail_id’,’325′)

This can be a bit time consuming if you’ve got a lot of categories to import.

To automate the process, we can script this up.

First, create a csv file with two entries per category. The first entry should be the image id (found from ‘select post_id from wp_postmeta where meta_key=’_wp_attached_file’;) and the second should be the term id (found from ‘select term_id, name from wp_terms;’)

For the exmaple of the dotDance Category shown above this would be:
325,9

Once you’ve got your CSV file, you can upload the data into wp_postmeta by running this script:

#!/bin/bash
cat $1 | while read line
do
val1=`echo $line|cut -f1 -d,`
val2=`echo $line|cut -f2 -d,`
echo “insert into wp_postmeta (post_id,meta_key, meta_value) select wp_term_relationships.object_id, ‘_thumbnail_id’,’${val1}’ from wp_term_relationships,wp_term_taxonomy where term_id=${val2} and taxonomy=’post_tag’ and wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id;” >> /tmp/script$$
done
echo “SQL has been generated in /tmp/script$$”
echo “”
echo “Here are the contents of /tmp/script$$”
echo “”
cat /tmp/script$$
echo “Now run this sql as follows:”
echo “mysql -u –username=XXX –password=YYYY –database=ZZZZZ < /tmp/script$$”

Be sure to take a backup of your database before running the script. Here’s how you can confirm it’s worked: Before associating the featured images to the WordPress posts: mysql> select * from wp_postmeta where meta_value=’325′;
Empty set (0.01 sec)

After associating the featured images to the WordPress posts:
mysql> select * from wp_postmeta where meta_value=’325′;
+———+———+—————+————+
| meta_id | post_id | meta_key | meta_value |
+———+———+—————+————+
| 789 | 26 | _thumbnail_id | 325 |
| 790 | 56 | _thumbnail_id | 325 |

If needed, you can remove the topic associations:

mysql> delete from wp_postmeta where meta_value=’325′ and meta_key=’_thumbnail_id’;

To confirm that all is working as expected, just login to WordPress, select an article that’s been assigned to a category and look to see if “Featured Image” has been set. If you are using a theme that displays category images for articles, then it should be displaying these on either the article homepage or the article itself .

Leave a Reply

Your email address will not be published.