This is a script that generates SQL suitable for mysqld that adds links provided by a flat text file into WordPress's blogroll. If you have a number of links to add, going through the administrative UI is an excercise in boredom and wasted time. I wrote this script to automate that tedious process. I wrote a post that covers the details.

 
#!/bin/sh
 
### cat@brainycat.com Tue Apr  7 21:27:49 PDT 2009
### copyrighted under the terms of the GPLv3
### http://www.gnu.org/licenses/gpl-3.0.html
 
### BACKUP YOUR DATABASE BEFORE RUNNING THIS SCRIPT
### "its always easier to rm unwanted data than recreate wanted data"
### HERE document shamelessly copied from phpmyadmin backup output
 
### VARIABLES
 
# file format:
 URL\tLINKNAME\twpdb.terms.term_id\twpdb.term_taxonomy.term_taxonomy_id
inputList="/PATH/TO/INPUT/FILE"
outFile="/PATH/TO/OUTPUT/FILE"
thisDB="YOUR_WORDPRESS_DATABASE"
thisLinksTable="WORDPRESS_links"
thisTermRELTable="WORDPRESS_term_relationships"
thisTermTAXOTable="WORDPRESS_term_taxonomy"
# change these as appropriate. Other changes can be made in the "while read" loop
linkUser="1"
linkTarget="_newbrowser"
 
# if you are appending to existing data
# MAKE SURE YOU REMOVE THE DROP CMDS FROM SQL SCRIPT!!!!!
 
insertLinks="INSERT INTO \`$thisLinksTable\` (\`link_url\`, \`link_name\`, \`link_image\`, \`link_target\`, \`link_category\`, \`link_description\`, \`link_visible\`, \`link_owner\`, \`link_rating\`, \`link_updated\`, \`link_rel\`, \`link_notes\`, \`link_rss\`) VALUES"
linkDate=$(date --rfc-3339 seconds | sed -e "s/-.....$//")
 
### FUNCTIONS
 
### MAIN
 
cat > $outFile < < EOF
-- SQL Script
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Database: \`$thisDB\`
--
 
-- --------------------------------------------------------
 
--
-- Table structure for table \`$thisLinksTable\`
--
 
DROP TABLE IF EXISTS \`$thisLinksTable\`;
CREATE TABLE IF NOT EXISTS \`$thisLinksTable\` (
 \`link_id\` bigint(20) NOT NULL auto_increment,
 \`link_url\` varchar(255) NOT NULL default '',
 \`link_name\` varchar(255) NOT NULL default '',
 \`link_image\` varchar(255) NOT NULL default '',
 \`link_target\` varchar(25) NOT NULL default '',
 \`link_category\` bigint(20) NOT NULL default '0',
 \`link_description\` varchar(255) NOT NULL default '',
 \`link_visible\` varchar(20) NOT NULL default 'Y',
 \`link_owner\` int(11) NOT NULL default '1',
 \`link_rating\` int(11) NOT NULL default '0',
 \`link_updated\` datetime NOT NULL default '0000-00-00 00:00:00',
 \`link_rel\` varchar(255) NOT NULL default '',
 \`link_notes\` mediumtext NOT NULL,
 \`link_rss\` varchar(255) NOT NULL default '',
 PRIMARY KEY  (\`link_id\`),
 KEY \`link_category\` (\`link_category\`),
 KEY \`link_visible\` (\`link_visible\`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
 
EOF
 
# generate the SQL statements
while read thisURL thisNAME thisCAT thisTERMTAXO; do
	#echo "thisURL= $thisURL  thisNAME = $thisNAME"  thisCAT = $thisCAT"   thisTERMTAXO = thisTERMTAXO; # debug
	echo "${insertLinks} ('$thisURL', '$thisNAME', '', '${linkTarget}', '$thisCAT', '', 'Y', '${linkUser}', '0', '${linkDate}', '', '', '');" >> $outFile
	echo "INSERT INTO ${thisTermRELTable} (\`object_id\`, \`term_taxonomy_id\`) VALUES (LAST_INSERT_ID(), '${thisTERMTAXO}');" >> $outFile
	echo "UPDATE ${thisTermTAXOTable} SET count=count+1 WHERE term_taxonomy_id = ${thisTERMTAXO};" >> $outFile
done < $inputList

Also filed under ♠ 1 comment ⇒