MySQL Replace - changing image directory

Need help with the Administration of your Joomla! 3.x site? This is the spot for you.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Post Reply
User avatar
LukeDouglas
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 242
Joined: Sat Dec 08, 2007 8:23 pm

MySQL Replace - changing image directory

Post by LukeDouglas » Mon Jun 29, 2020 4:15 am

I have images in a public_html folder "images/gallery/memorial/wiregrass/" that I would like to have changed to 'images/memorial/'.

In the record via phpAdmin, it displays:

Code: Select all

{"image_intro":"images\/memorial\/kennedy-paxton-wayne__Paxton-Wayne-Kennedy.jpg","float_intro":"","image_intro_alt":"","image_intro_caption":"","image_fulltext":"images\/memorial\/kennedy-paxton-wayne__Paxton-Wayne-Kennedy.jpg"}
In an exported SQL file, it displays:

Code: Select all

'\"image_intro\":\"images\\/gallery\\/memorial\\/wiregrass\\/kennedy-paxton-wayne__Paxton-Wayne-Kennedy.jpg\",\"float_intro\":\"\",\"image_intro_alt\":\"\",\"image_intro_caption\":\"\",\"image_fulltext\":\"images\\/gallery\\/memorial\\/wiregrass\\/kennedy-paxton-wayne__Paxton-Wayne-Kennedy.jpg\",
I've researched and tried several ways but have failed to change the database correctly. There are over 165 articles and I really dread doing this manually.

Anyone know of an SQL statement I can use within phpMyAdmin to make this change?

 
SharkyKZ
Joomla! Ace
Joomla! Ace
Posts: 1740
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: MySQL Replace - changing image directory

Post by SharkyKZ » Mon Jun 29, 2020 6:11 am

Have you tried REPLACE() https://stackoverflow.com/a/10177250 ?

User avatar
LukeDouglas
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 242
Joined: Sat Dec 08, 2007 8:23 pm

Re: MySQL Replace - changing image directory

Post by LukeDouglas » Mon Jun 29, 2020 6:54 am

As I indicated, I have tried multiple MySQL REPLACE commands but it's returning '0' records found.

Example (for security, I haven't shown the database PREFIX):

This didn't work:

Code: Select all

UPDATE `PREFIX_content` SET `images` = REPLACE(`images`,"images/gallery/memorial/wiregrass/","images/memorial/") WHERE `catid` = '25';
This didn't work:

Code: Select all

UPDATE `PREFIX_content` SET `images` = REPLACE(`images`,"images\/gallery\/memorial\/wiregrass\/","images\/memorial\/") WHERE `catid` = '25';
SharkyKZ wrote:
Mon Jun 29, 2020 6:11 am
Have you tried REPLACE() https://stackoverflow.com/a/10177250 ?

SharkyKZ
Joomla! Ace
Joomla! Ace
Posts: 1740
Joined: Fri Jul 05, 2013 10:35 am
Location: Parts Unknown

Re: MySQL Replace - changing image directory

Post by SharkyKZ » Mon Jun 29, 2020 7:20 am

You need an additional backslash to escape the stored backslash. Try this:

Code: Select all

UPDATE `#__content` SET `images` = REPLACE(`images`,"images\\/gallery\\/memorial\\/wiregrass\\/","images\\/memorial\\/");

User avatar
Webdongle
Joomla! Master
Joomla! Master
Posts: 38599
Joined: Sat Apr 05, 2008 9:58 pm

Re: MySQL Replace - changing image directory

Post by Webdongle » Mon Jun 29, 2020 2:29 pm

http://www.weblinksonline.co.uk/
https://www.weblinksonline.co.uk/updating-joomla.html
"The definition of insanity is doing the same thing over and over again, but expecting different results": Albert Einstein.

User avatar
LukeDouglas
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 242
Joined: Sat Dec 08, 2007 8:23 pm

Re: MySQL Replace - changing image directory

Post by LukeDouglas » Mon Jun 29, 2020 5:25 pm

BROKEN LINK
Webdongle wrote:
Mon Jun 29, 2020 2:29 pm
https://extensions.joomla.org/extension ... t-[removed]/ there are others

User avatar
leolam
Joomla! Master
Joomla! Master
Posts: 20118
Joined: Mon Aug 29, 2005 10:17 am
Location: Netherlands/ UK/ S'pore/Jakarta/ North America
Contact:

Re: MySQL Replace - changing image directory

Post by leolam » Mon Jun 29, 2020 5:35 pm

Webdongle wrote:
Mon Jun 29, 2020 2:29 pm
https://extensions.joomla.org/extension ... t-[removed]/ there are others
"good advise"

Leo 8)
Joomla's #1 Professional Services Provider:
#Joomla Professional Support: https://gws-desk.com -
#Joomla Specialized Hosting Solutions: https://gws-host.com -
#Joomla Webmaster Services: gws-webmaster.services

User avatar
LukeDouglas
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 242
Joined: Sat Dec 08, 2007 8:23 pm

Re: MySQL Replace - changing image directory

Post by LukeDouglas » Mon Jun 29, 2020 7:28 pm

SharkyKZ,

Didn't work:
0 rows affected. (Query took 0.0039 seconds.)

Code: Select all

UPDATE `XXXXX_content` SET `images` = REPLACE(`images`,"images\\/gallery\\/memorial\\/wiregrass\\/","images\\/memorial\\/")
I'm wondering if this isn't working due to not an exact match for the entire 'images' field, meaning this won't work for a partial replacement.

So I did some research 'again' and tried this but it didn't work.

Code: Select all

UPDATE `XXXXX_content` SET `images` = REPLACE(`images`,"%images\\/gallery\\/memorial\\/wiregrass\\/%","%images\\/memorial\\/%")
SharkyKZ wrote:
Mon Jun 29, 2020 7:20 am
You need an additional backslash to escape the stored backslash. Try this:

Code: Select all

UPDATE `#__content` SET `images` = REPLACE(`images`,"images\\/gallery\\/memorial\\/wiregrass\\/","images\\/memorial\\/");

 

Post Reply

Return to “Administration Joomla! 3.x”