Search and replace text in 10,000 item descriptions

[Guest]

Is there any way I can use cPanel to make a mass change in my product descriptions?

I need to change some hard coded urls in my descriptions.

Firstly, I need to change all instances of HTTP to HTTPS

Secondly, I need to change the filepath of the urls, as well.

Years ago I bought a .net domain as the .com was unavailable.  All stock photos were saved in the .net domain.

Sometime later, .com became available but I continued to use the .net path for linking photos in my item descriptions.

Google Chrome has now become a PITA and I need to change everything and convert the entire site to HTTPS

Example:  I need to change

 

"http://www.mysite.net/cat/123.jpg"

to

"https://www.mysite.com/dog/123.jpg"

Change all instances of HTTP to HTTPS

Change the filepath of .net/cat to .com/dog

All stock photos have been copied from the .net/cat folder to the .com/dog folder and, yes, SSL is installed

These are all urls that were hard coded in every item description created in the Admin side of the site

Is there any easy way to do this or am I stuck manually editing 10,000 files?

Thanks in advance for any help!

[frankl]

You can write a php script to update all those values in your database

Select products_description for all items

while items str_replace http to https, str_replace .net/cat to .com/dog

tep_db_query update description

 

Test on a backup of your database first.

 

[greasemonkey]

@puggybelle as frank said test on a dummy db....

if you are just looking to run a query in phpmyadmin you could try something like

 

update table_name set field_name = replace( field_name, 'original text', 'replacement text');

[MrPhil]

Also consider doing a backup (export) to an .sql text file, and then using a good text editor such as ViM or Notepad++ to modify the text (e.g., globally replace http:// with https://, domain.net with domain.com, etc.). Finally, restore (import) into the database.

[MrPhil]

(more on export/import):

1) if you're only changing a few tables, snip them out of the .sql backup and import them one at a time

2) before importing a revised table, be sure to first empty (truncate) the existing table so that you don't get doubled entries.

Of course, you should have a DB backup and know how to restore it, first. Working on a live store is trickier than one that's offline -- depending on the data being changed and how long it will take to truncate/import the table, and whether the table might get updated by a customer during this, you might want to take your store offline temporarily, to prevent such conflicts.

[Guest]

Firstly, Thanks to everyone who responded.  This forum is beyond terrific.

And Scott (Greasemonkey)...you are a ROCK STAR!

I tried what Frank suggested in a test site I have and got the dreaded 'you have an error in your SQL syntax' message.

I'm no programmer, so I got frustrated and quickly moved on.

Phil - I backed up the database and downloaded it to my hard drive only to worry that I had no idea how to go about putting that edited file (Notepad worked great, by the way) back up in the website!  FTP?  I think I was worried about the size of the file.  I mean, when I use my FTP client the server is set up to show me something like 1998 images max - when there are many more than that.  I guess I feared that I wouldn't be able to get the entire database back in the site, if that makes sense.

Then I tried what Scott proposed.  Firstly, I just tried to see if it would replace all instances of http with https and it worked perfectly.  Then, I sat and worried a bit about the forward slash when replacing .net/cat with .com/dog, but...I won't know til I try, so...I hit Go and it was flawless. 

Thank You all so much!  Now I'm on to figuring out to have the entire site run in SSL..maybe then I can relax. 

[MrPhil]

Glad you got it done. Most FTP clients are set up to show only 2000 files, and a backup is just one (large) file. All hosting control panels have a way to upload files, FTP can be used, and phpMyAdmin may even be set up to directly upload the import (.sql) file from your PC. Well, you got it done anyway.

[greasemonkey]

@puggybelle glad it worked... gotta put the warning out there again. Do not run that quick script on a live database without first testing it on a dummy db. 

As for moving to SSL across the entire site... read this thread. 

i did the same about a year back base on the info found in there