I want to add links to the slackpackages I've built. The first step to doing properly is to get the links into the WordPress database. Given WP's fantastically crappy admin UI, this would take at least two hours if I did them by hand, and I would undoubtedly introduce numerous errors. I decided to write a script that would create SQL that I could import via phpmyadmin.

First, I created a list of the files I wanted to link to:
(they are already uploaded to my hosted environment)
ssh webhost
cd /path/to/content
ls *CAT* > slack.list


Then I brought the file to my local environment to work on it:
rsync -brave ssh user@host:/path/to/content/slack.list .


I knew I wanted the links to be named identically to their files, so I created a new file with two identical columns:
while read line; do
echo -e "${line}\t${line}" >> links.info
done < slack.list


I could have done this in the original file, but I wanted to keep the original around in case I borked my working copy.

I opened up the file in vi and created URLs out of the first column:
:% s/^/http:\/\/servername.domain.tld\/PATH\/TO\/FILES/


I researched in /wp-admin and on the net and discovered what other information I needed to add the bookmarks. James Wallace was especially helpful. I created a category for all these links to go in, and found the category's id number in wpdb.terms.term_id WHERE name=[name of category]. I found the category to item key in wpdb.term_taxonomy.term_taxonomy_id WHERE term_id = [the term_id we already found]. The most difficult part was trying to determine how wpdb.term_relationships.object_id is determined for each record. Looking at the table, it's clearly an index key but it's not autoincremented, so it must be coming from somewhere else. The code in /wp-admin that deals with these updates, while grammatically excellent and extensible, is not exactly clear.

I went back to links.info and added this information to each line:
:% s/$/\t[term_id]\t[term_taxonomy_id]


Of course, you'll want to replace the brackets and what's between them with the numeric values you got from your database. Also, you don't have to add one category at a time. I specifically designed this script so that a list of links destined for inclusion in various categories can all be added at once.

I deleted the links via the admin interface, and removed all trace of them from wpdb.term_relationships. I now had a pristine condition from which the autoincrementing fields could start counting automagically, and would be able to create my own relationships in the taxonomy mess without stomping on anything else. The script could just as easily append to existing data by removing the DROP/CREATE statements.

My SQL isn't especially sharp, so I swiped the DROP/CREATE statements and "INSERT wpdb.links" prototype from a backup generated by phpMyAdmin. The DROP/CREATE lent itself to a HERE statement, of course. I setup a loop that reads each column from my input file, and generates SQL for each record. First, we insert the link into wpdb.links. Then we create the relationship with the category in wpdb.term_relationships. Finally, the third statement updates the link count in wpdb.term_taxonomy.

I ran my script, and it generated statements for 41 records in 0.115 seconds and used the 'Import' feature of phpMyAdmin to run the SQL the script generated. I was able to add 41 records into the database 0.0018 seconds.

Note that I did all my testing in the dev environment on my laptop. DO NOT ATTEMPT DATABASE MANIPULATION IN A PRODUCTION ENVIRONMENT.

However, it took me quite a while to get the script working properly, especially researching the ABSOLUTE MESS that is $object_id, $term_id and $term_taxonomy_id. Therefore, I want to post this so that other people who want to import large datasets of links into WordPress can have a much easier time of it.

You can copy the script here.

Also filed under sysadmin ♠ 2 comments ⇒