Ask your WordPress questions! Pay money and get answers fast! (more info)

How to transform Agoda affiliate URLs in database? WordPress

Because Agoda has totally changed their URL structures twice in the past year, I need to make some urgent changes to my 100s of affiliate links in Wordpress.

The current URL structure for my oldest affiliate links is like:

http://www.agoda.com/region/country/city/this_page.html?CID=12345

Some of my more recent links use the older ajax search structure like:

http://ajaxsearchpartners.agoda.com/region/country/city/this_page.html?CID=12345&header=http://mydomain.com/my-image.jpg&backlink=http://mydomain.com/

The new Agoda URL structure (what I need them all to look like) is:

http://ajaxsearch.partners.agoda.com/partners/tracking.aspx?CID=12345&urltogo=http://ajaxsearch.partners.agoda.com/region/country/city/this_page.html&backlink=http://mydomain.com/&header=http://mydomain.com/my-image.jpg

So I need two MySQL transformation statements that will transform URLs like the first two to become like the third one.

I have no hope of working this out by myself and I have hundreds of these URLs I need to transform. I am hoping that for someone skilled in Regex this is a walk in the park.

Thanks in advance for any help offered. I can provide some actual source and target link examples for testing if you want to see them.

Answers (2)

2013-08-14

Hariprasad Vijayan answers:

Hi,
Can you show the current code that use to echo the link?


Tony Gilbert comments:

I don't understand what you're asking for.


Hariprasad Vijayan comments:

Are you generating links using some functions or directly calling it from database?

2013-08-14

zebra webdesigns answers:

Hello Tony

In my understanding you are provided this links within the post.
there is tool called search and replacedb that will do the job for you to easily replace the content.
This is the effective way since it will even find out the serialized data and replace it like a charm.
go through the below article.
http://thewpvalet.com/blog/2012/08/16/heres-a-quick-way-to-search-and-replace-your-database-after-migration/

you need to backup your database before proceeding. If you are not sure of this things I can help you. for an additional 10$.

For example: "http://ajaxsearchpartners.agoda.com/region"
you should search and replace as "http://ajaxsearch.partners.agoda.com/partners/tracking.aspx?CID=12345&urltogo=http://ajaxsearch.partners.agoda.com/region/"

I will update you with further instructions once I analyze fully


Tony Gilbert comments:

I know how to use search and replace in WP, but not for a pattern search. What I need to know is how to express the components of the URL as variables in a global search and replace.


zebra webdesigns comments:

Can you send me the SQl dumb file.
I will PM my mail id