Replacing link URL ending strings via MySQL

1 Views

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:

Read More
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?

Post Views: 1

Related posts

Leave a Reply

3 comments

  1. DISCLAIMER : Not a WordPress Developer, Just a MySQL DBA

    For a given table, say link_url

    mysql> show create table wp_linksG
*************************** 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 !!!

  3. 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.