Add Bookmarks to WordPress via the Database

Wednesday, 08/04/2009 ≅17:59 ©brainycat

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.



Looking for slackware packages? I have a few dozen packages I've built, including multimedia libraries, gimp 2.6, afterstep 2.2.8 etc. Build scripts are included so you can tweak them for your system.

24 thoughts provoked

 On Wednesday, 08/04/2009 ≅ 17:59 ©Add Bookmarks to WordPress via the Database spoke thusly: 

[...] more:  Add Bookmarks to WordPress via the Database Posted in wordpress | Tags: detected, doing-properly, first-step, hosted-at-wordpress, [...]



 On Wednesday, 08/04/2009 ≅ 17:59 ©New features at BrainyCat World Domination HQ @ The Adventures of Brainy Cat spoke thusly: 

[...] to a set of nearly identical albums. I looked at the database, thinking I could cheat a little like I did with WordPress, but there’s 63 tables in that database! Forgive me for a moment of laziness, and deciding to just [...]



 On Wednesday, 08/04/2009 ≅ 17:59 ©google plus account for business spoke thusly: 

Hi to all, the contents present at this website are in fact amazing for people knowledge, well, keep
up the good work fellows.



 On Wednesday, 08/04/2009 ≅ 17:59 ©dentist review Salem Mass spoke thusly: 

I do not even know how I stopped up here, but I thought
this publish was good. I do not understand who you might be but certainly you are going to a well-known blogger for
those who aren’t already. Cheers!

Take a look at my web blog; dentist review Salem Mass



 On Wednesday, 08/04/2009 ≅ 17:59 ©http://wiki.feministlibrary.co.uk/doku.php?id=profile_alisharice spoke thusly: 

Can I simply just say what a comfort to uncover someone that genuinely understands what they’re talking about on the
web. You definitely realize how to bring a problem
to light and make it important. More people
really need to check this out and understand this side of your story.
I was surprised that you are not more popular because you
definitely possess the gift.



 On Wednesday, 08/04/2009 ≅ 17:59 ©Austin roll off dumpsters spoke thusly: 

Whoa! This blog looks just liike my old one! It’s on a entirely
different topic but it has pretty much the same page layout and design. Superb choice of colors!



 On Wednesday, 08/04/2009 ≅ 17:59 ©car loans for people with bad credit orlando FL spoke thusly: 

Appreciate the recommendation. Let me try it out.

my weblog; car loans for people with bad credit orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©buy here pay here car lots orlando FL spoke thusly: 

Hmm is anyone else having problems with the pictures on this blog loading?
I’m trying to find out if its a problem on my end or
if it’s the blog. Any responses would be greatly appreciated.

my page – buy here pay here car lots orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©bad credit car finance orlando FL spoke thusly: 

I think this is one of the most important information for me.

And i am glad reading your article. But wanna remark on few general things, The site style is
perfect, the articles is really nice : D. Good job, cheers

Here is my web-site – bad credit car finance orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©bad credit car finance orlando FL spoke thusly: 

Great beat ! I wish to apprentice while you amend your web
site, how can i subscribe for a weblog web site? The account aided me a acceptable deal.

I have been a little bit acquainted of this your broadcast provided bright clear idea

Look into my webpage … bad credit car finance orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©bad credit car dealers orlando FL spoke thusly: 

Greetings! I know this is kinda off topic but I was wondering which blog platform are you using
for this website? I’m getting tired of WordPress because
I’ve had issues with hackers and I’m looking at alternatives for another platform.
I would be great if you could point me in the direction of
a good platform.

Here is my weblog; bad credit car dealers orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©no credit car loans orlando FL spoke thusly: 

I don’t even know how I ended up here, but I thought this post was good.
I do not know who you are but definitely you’re going to a famous blogger if you aren’t already ;) Cheers!

Here is my weblog: no credit car loans orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©car loan bad credit orlando FL spoke thusly: 

If you desire to obtain much from this post then you have to apply
these strategies to your won weblog.

Take a look at my weblog: car loan bad credit orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©car loans bad credit orlando FL spoke thusly: 

Very nice post. I just stumbled upon your weblog and wanted to mention that I’ve really enjoyed surfing around your weblog posts.
After all I will be subscribing to your feed and I am hoping you write again very soon!

my weblog – car loans bad credit orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©car finance for bad credit orlando FL spoke thusly: 

What’s up, its pleasant post about media print, we all know media is a great source of data.

my blog post: car finance for bad credit orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©no credit car loans orlando FL spoke thusly: 

Great article, exactly what I was looking for.

Feel free to visit my site … no credit car loans orlando FL



 On Wednesday, 08/04/2009 ≅ 17:59 ©Stardom hollywood hack with cheat engine 6.2 spoke thusly: 

In a fast speed, you can indulge stardom hollywood cheats hack
into freaking and addictive; it was first released in December 2009 through Apple’s App Store.
This means you will find a huge opportunity in this discussion which is quite popular lately with much ease.
Find the system that need to tilt stardom hollywood cheats
hack their own strategy and absolute attributes.

Feel free to visit my blog :: Stardom hollywood hack with cheat engine 6.2



 On Wednesday, 08/04/2009 ≅ 17:59 ©fast boat from bali to gili islands Services spoke thusly: 

Appreciation tо mу father whօ told mme aboսt tҺіs web site,
tҺіs webpabe is genuinely amazing.

Check outt mү page :: fast boat from bali to gili islands Services



 On Wednesday, 08/04/2009 ≅ 17:59 ©http://elkezaleski.buzznet.com spoke thusly: 

I was suggested this blog by my cousin. I’m not sure whether this
post is written by him as no one else know such detailed about my problem.
You’re incredible! Thanks!



 On Wednesday, 08/04/2009 ≅ 17:59 ©professional logo design spoke thusly: 

They are considered to be very essential because the search engines are able to understand it in a better way as they highlight the page.
Over one billion people have the access to internet.
gif, etc) may look great, the search engines cannot read
what the images say. These companies are going to help you make more
money by getting your web site ranked well within the
search engines. At this point there aren’t likely any giant affects relating to the technical
set up of the searcher in query.



 On Wednesday, 08/04/2009 ≅ 17:59 ©bulk email server spoke thusly: 

It is even possible to arket your online business, or business opportunity,
to the masses by using your own autoresponder andd optin page.

In order too keep from deleting potentials, send a conirmation email
to reaffirm their interest in your services. The networks that it should
connect to should at the minimum be Linked – In, twitter and
Facebook. A call to action is an offer you’re making too the recipient – or an opportunity
for them to respond in some way. Youu will be allowed
to market to them through the email account that they have provided.

Feel free to visit mmy web page: bulk email server



 On Wednesday, 08/04/2009 ≅ 17:59 ©Clickforu.Com spoke thusly: 

Nevertheless, wһen dealing witһ
this kind of agencү, mⲟⅾеѕt aɑnd һugƅᥱ Ьսѕіneѕѕ ԝіⅼl Ье tҺe
сօncеntгatіοn.
Ꮪеек foг ttҺе
ffսⅼⅼ encοᥙгaɡеmеnt οоf tһᥱ реoρⅼе aгߋund уߋᥙ ѕο tҺat yoᥙ’ⅼl
Ье mоtіѵatᥱⅾ tߋ fսⅼfіⅼl
yоuг tɑгgеtѕ.
ƬҺеrе arᥱ ѕⲟme tҺіngѕ pеoρоe fɑіⅼ tо tɦіnk оѵег and оne ߋf tһeѕе іs Ԁеalіng ᴡіth moneʏ.

Ӎy bⅼоǥ; Clickforu.Com



 On Wednesday, 08/04/2009 ≅ 17:59 ©accountant in fort Lauderdale spoke thusly: 

Frank Breiling iis а recognized
eхρert in Αffіlіatᥱ Ꮇɑгҝеting.
Уοᥙ sᴠᥱ а ⅼⅼօt on іnvеntߋгу, tᥱѕtіng, ϲοⲣyᴡrіtіng, аnnⅾ tо bе геalⅼʏ hοneѕt,
үоᥙ ѡіlⅼ ⅼᥱɑᥱn ɑ lоt aƅοᥙt mагқеtіng dᥙгing thе cоᥙгѕe οf ƅeϲоmіng a ϹPᎪ ѕսρег аffіⅼіаtᥱ.
Ⲃy bеcоmіng ρɑrt
оf an асcⲟսntіng
team, yoսս ѡіlⅼ haѵᥱ tһᥱ οⲣⲣߋгtսnity to ǥеt ɑcԛuaіntеԁ ԝіth һоԝ ѕmalⅼ bսѕineѕѕеѕ регаtе аnd ɑѕo acqսігᥱ ρгɑctіϲal ҝnoաlᥱԁǥе aЬߋut ɑԀministrаtіᴠᥱ ɑϲсߋunting
ⲣгосеԁuгᥱѕ аnd feԁегаl
ɑnd ѕtqtսtoгү геգuігеmеnts.

mү ԝеƄρаgе :
: accountant in fort Lauderdale



 On Wednesday, 08/04/2009 ≅ 17:59 ©canadian pharmacy spoke thusly: 

General Low-down About this by-product



Be Provoking: