asked on

What Is Best Way To Change URL's in MySql DB?

I am using WordPress 5.1.1 with WooCommerce 3.6.1 and I have several links within a Maria db table that need to changed. It seems I need a lookup table of some kind that will change the url to the correct one when it encounters an incorrect one. I have over 600 links that need to be changed and around 50 different url's that need to be substituted into the db.

Due to the volume of links I want to do this in a bulk manner.

What is the best way to accomplish this?

If you could show me an example it would be helpful too.

theGhost_k8

It would be easy if you can provide a sample example of what you want to achieve.

If you're having access to database, you may easily run updates.
1. take backup of the table first.
2. update table_name set url_col=replace(url_col,'oldvalue','newvalue');

Refer: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_replace

sharingsunshine

ASKER

it is in wp_posts and it is the post_content field. Here are some sample url's

/index.php?controller=cms&id_cms=200 should be /essential_oils_q_a_sp_200/
/index.php?controller=cms&id_cms=4 should be /essential_oil_recipes_sp_4/
/index.php?controller=category&id_category=43 should be /product-category/home/essential_oils_100_pure_page_1_c_1/essential_oils_page_1_c_33/
/index.php?controller=category&id_category=44 should be /product-category/home/essential_oils_100_pure_page_1_c_1/essential_oil_blends_page_1_c_34/
/index.php?controller=product&id_product=2 should be /aromaball_plugin_diffuser_p_2/

Open in new window

This is just a small snippet there are probably much more than 600 that need to be changed. So, I was hoping there was a bulk way to do it. But if it has to be done one by one I guess that will just take some time to do.

ASKER CERTIFIED SOLUTION

Terry Woods

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sharingsunshine

ASKER

This looks very promising, thanks for participating. Is there a way to print out all the url's from the db but not within the content?

That way I can determine how many sql statements I will need to create by hand.

Terry Woods

Sorry, I don't understand what you mean by "the url's from the db but not within the content". Could you please explain?

Prabhin MP

**wordpress url change**

SET @old= 'old URL'; SET @new= 'NEWURL'; UPDATE wp_options SET option_value = replace(option_value,@old, @new) WHERE option_name = 'home' OR option_name = 'siteurl' OR option_name = 'ossdl_off_cdn_url'; UPDATE wp_posts SET guid = replace(guid, @old, @new); UPDATE wp_posts SET post_content = replace(post_content, @old, @new);"

sharingsunshine

ASKER

I mean is there a way to strip out the url's from the db content. For example, using this search argument

I get this many records

Now if I open up the first record I find some url's in the content

In this record we find
/index.php?controller=cms&id_cms=4
/index.php?controller=category&id_category=43
/index.php?controller=category&id_category=44
/index.php?controller=product&id_product=2

If I keep digging I will even more. Is there a way to strip just all of the urls (matching /index.php?controller=(.*) out of all the records and create a list?

sharingsunshine

ASKER

Thanks for the workup. I will be using that as a template.