Updating wp categories from another mySQL database

If you have a mysql database that is storing your data, but you want to pull that data into wordpress with some key information as tags, you can benefit from the fun I am having. I am sure there is a better way, but this is what I have now.


Assume that you have created some sort of temp_post table that holds the key information that you will need to plunk stuff into WordPress. In my case, I created temp_post for widget reviews like so:

temp_post (post_review, post_review_title, post_review_excerpt, cat1, cat2, tag1, tag2, time)

Where cat1, cat2, and tag1, tag2 include information about widget makers, widget geography, and widgets themselves that I want to have searchable as WordPress tags and categories.

Having this table puts you pretty close to ready, but there are hurdles. Like, what in the world are you gonna do if you need to add a new Category that isn't already on WordPress?
Step 1 is to get new unique categories into your wp_terms table. NOTE: I STRONGLY recommend creating your own WP tables on another database as dummies while you figure this out. You can seriously mess up your blog by playing with the mySQL database if you don't know what you are doing (or if you do know and still make a mistake).
Here is the code to get unique tags and insert them into wp_terms.

CREATE TABLE tags (tag VARCHAR(200) NOT NULL, slug TEXT(200) NOT NULL);
INSERT INTO tags (tag)
SELECT DISTINCT temp_post.cat1
FROM temp_post;
INSERT INTO tags (tag)
SELECT DISTINCT temp_post.cat2
FROM temp_post;
INSERT INTO tags (tag)
SELECT DISTINCT temp_post.tag1
FROM temp_post;
INSERT INTO tags (tag)
SELECT DISTINCT temp_post.tag2
FROM temp_post;
DELETE FROM tags WHERE tag=' ';
UPDATE tags
SET slug = LCASE(tag);
INSERT INTO wp_terms (name,slug)
SELECT tag,slug
FROM `tags` WHERE NOT EXISTS (SELECT term_id FROM wp_terms WHERE wp_terms.name=tags.tag);
DROP TABLE tags;

You can see that I had to use four separate INSERT commands to get the distinct tags from my data. Your situation will probably be different. I did not have any luck doing this as one set as in
SELECT DISTINCT cat1,cat2,tag1,tag2
FROM temp_post; because that returns unique combinations. Like I said, there is probably a better way, but this works.

ONE POINT OF CAUTION. While I get the slug to be the lowercase version of the tag name here, I don't make it web friendly. If you have a tag with spaces or other garbage, it will need to be cleaned. I tried a bunch of ways to fix this, and I am sticking with myself as a manual editor until I can figure it out. If someone knows how to clear garbage please let me know. I tried REPLACE (slug, ' ','-') and several versions of POSITION.

Next...updating the taxonomy