Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

We are about to purchase a site that uses Amazon affiliate links.

Naturally, they have their own affiliate ID, Amazon affiliate links look like this:

http://www.amazon.com/gp/product/productcode/?tag=affiliateID-20

What would be the best way to alter the ending string for these links' URL's in a Wordpress blog?

share|improve this question

3 Answers 3

up vote 0 down vote accepted

DISCLAIMER : Not a WordPress Developer, Just a MySQL DBA

For a given table, say link_url

mysql> show create table wp_links\G
*************************** 1. row ***************************
       Table: wp_links
Create Table: CREATE TABLE `wp_links` (
  `link_id` bigint(20) unsigned 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_description` varchar(255) NOT NULL default '',
  `link_visible` varchar(20) NOT NULL default 'Y',
  `link_owner` bigint(20) unsigned 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_visible` (`link_visible`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

and you have Amazon Links like this one:

http://www.amazon.com/gp/product/productcode/?tag=affiliateID-20

and you want to replace the number 20 with some other number (like 253) so that URLs are now

http://www.amazon.com/gp/product/productcode/?tag=affiliateID-253

Run the following SQL script

SET @old_number = 20;
SET @new_number = 253;
SET @affiliate_prefix = 'http://www.amazon.com/gp/product/productcode/?tag=affiliateID-';
SET @old_affiliate = CONCAT(@affiliate_prefix ,@old_number);
SET @new_affiliate = CONCAT(@affiliate_prefix ,@new_number);
CREATE TABLE linkids SELECT link_id FROM wp_links WHERE 1=2;
ALTER TABLE linkids ADD PRIMARY KEY;
INSERT INTO linkids SELECT link_id FROM wp_links WHERE link_url=@old_affiliate;
UPDATE linkids A INNER JOIN wp_links B USING (link_id) SET B.link_url=@new_affiliate;
DROP TABLE linkids;

Give it a Try !!!

share|improve this answer
    
Thanks Rolando, however I want to replace the whole string of affiliateID-##. AffiliateID is actually a string of text that contains the name of the affiliate. For example, a website called hobo might have an affiliate ID of hobo-10 and the string I want to replace it with is memi-20. –  Darwin229 Feb 18 '13 at 0:20

I've had some success with this plugin: Search and Replace. You can enter the full URL (including the old affiliate ID) and replace it with the full URL including your new affiliate ID.

share|improve this answer

Similar to Jared's answer I've used the Velvet Blues Update URLs plugin to you know, update URLs. :) If all the URLs are in the content that should be sufficient. If you think that some URLs might be lurking in other places, like meta/options then you might want to try the Serialized PHP Search and Replace script. I used it recently to move WP to a new server and it was perfect and perfectly simple. I think either would do you well.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.