Mutley Posted September 28, 2015 Share Posted September 28, 2015 I've just updated my forum software to IPBv4 and it's inserted a paragraph line in the signatures field, so they are really spaced out, I want to search and remove this line from the field. In some instances, there is more than one occurance in the field value. This is the string I want to remove: <p style="text-align:center;"> </p> An example of one members signature in the database: <p style="text-align:center;"><img src="http://i1382.photobucket.com/albums/ah259/maphilli/Axial%20Yeti/Logos/MS%20Banner_zpsfrtm0dpx.jpg" alt="MS%20Banner_zpsfrtm0dpx.jpg"></p> <p style="text-align:center;"> </p> <p style="text-align:center;"><a href="<___base_url___>/topic/199518-zombis-axial-yeti/">My Axial Yeti Build Thread</a></p> <p style="text-align:center;"> </p> <p style="text-align:center;"><a href="<___base_url___>/topic/202762-zombi/">My Sales Feedback Page</a></p> <p style="text-align:center;"> </p> <p style="text-align:center;"><a href="<___base_url___>/forum/167-gowtt/">GOWTT Trailing Group Forum - Derbyshire Peak District</a></p> <p style="text-align:center;"> </p> <p style="text-align:center;"><a href="https://www.facebook.com/pages/Loaded-Ox/437880833003825" rel="external nofollow">Loaded Ox Facebook Page</a></p> I want it to remove the line mentioned, so it looks like this: <p style="text-align:center;"><img src="http://i1382.photobucket.com/albums/ah259/maphilli/Axial%20Yeti/Logos/MS%20Banner_zpsfrtm0dpx.jpg" alt="MS%20Banner_zpsfrtm0dpx.jpg"></p> <p style="text-align:center;"><a href="<___base_url___>/topic/199518-zombis-axial-yeti/">My Axial Yeti Build Thread</a></p> <p style="text-align:center;"><a href="<___base_url___>/topic/202762-zombi/">My Sales Feedback Page</a></p> <p style="text-align:center;"><a href="<___base_url___>/forum/167-gowtt/">GOWTT Trailing Group Forum - Derbyshire Peak District</a></p> <p style="text-align:center;"><a href="https://www.facebook.com/pages/Loaded-Ox/437880833003825" rel="external nofollow">Loaded Ox Facebook Page</a></p> The table is `ipb_core_members`, column `signature` What SQL query can I run? Thanks in advance. Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/ Share on other sites More sharing options...
QuickOldCar Posted September 28, 2015 Share Posted September 28, 2015 Something like this UPDATE `ipb_core_members` SET `signature` = replace(signature, "<p style=\"text-align:center;\"> </p>", '') Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1521674 Share on other sites More sharing options...
Mutley Posted September 28, 2015 Author Share Posted September 28, 2015 Something like this UPDATE `ipb_core_members` SET `signature` = replace(signature, "<p style=\"text-align:center;\"> </p>", '') Thanks a lot QuickOldCar I did a test on 1 member and it ran successfully but effected no rows, I'm guessing that it's not matching with that string correctly but I am not sure why? The backslash to make it parse won't cause any issues would it? Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1521688 Share on other sites More sharing options...
cyberRobot Posted September 28, 2015 Share Posted September 28, 2015 How did you run the query? Did you run it through a PHP script...or directly in something like phpMyAdmin? Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1521690 Share on other sites More sharing options...
Mutley Posted September 30, 2015 Author Share Posted September 30, 2015 I just use PHPMyAdmin. Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1521984 Share on other sites More sharing options...
cyberRobot Posted September 30, 2015 Share Posted September 30, 2015 I just use PHPMyAdmin. The backslashes don't seem to cause any problems; at least they worked when I tested the query through PHPMyAdmin. Note that the query could also be written as UPDATE `ipb_core_members` SET `signature` = replace(signature, '<p style="text-align:center;"> </p>', '') Are the empty paragraph tags written exactly like <p style="text-align:center;"> </p> If there is more than one space between the open and close tag, for example, the query won't replace anything. Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1521994 Share on other sites More sharing options...
Mutley Posted October 3, 2015 Author Share Posted October 3, 2015 The backslashes don't seem to cause any problems; at least they worked when I tested the query through PHPMyAdmin. Note that the query could also be written as UPDATE `ipb_core_members` SET `signature` = replace(signature, '<p style="text-align:center;"> </p>', '') Are the empty paragraph tags written exactly like <p style="text-align:center;"> </p> If there is more than one space between the open and close tag, for example, the query won't replace anything. I just can't get it to work, yes the string is exactly that. Even if I just test it on the example in my first post, it doesn't find this string. I really can't understand why. If I do a search for this string, then it finds the fields no problem, it just won't initiate this replace query. Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1522189 Share on other sites More sharing options...
QuickOldCar Posted October 3, 2015 Share Posted October 3, 2015 Did a test myself in phpmyadmin and seemed to work fine. 1 row(s) affected. ( Query took 0.0009 sec ) UPDATE `ipb_core_members` SET `signature` = replace( signature, "<p style=\"text-align:center;\"> </p>", '' ) New data: <p style="text-align:center;"><img src="http://i1382.photobucket.com/albums/ah259/maphilli/Axial%20Yeti/Logos/MS%20Banner_zpsfrtm0dpx.jpg" alt="MS%20Banner_zpsfrtm0dpx.jpg"></p> <p style="text-align:center;"><a href="<___base_url___>/topic/199518-zombis-axial-yeti/">My Axial Yeti Build Thread</a></p> <p style="text-align:center;"><a href="<___base_url___>/topic/202762-zombi/">My Sales Feedback Page</a></p> <p style="text-align:center;"><a href="<___base_url___>/forum/167-gowtt/">GOWTT Trailing Group Forum - Derbyshire Peak District</a></p> <p style="text-align:center;"><a href="https://www.facebook.com/pages/Loaded-Ox/437880833003825" rel="external nofollow">Loaded Ox Facebook Page</a></p> Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1522192 Share on other sites More sharing options...
Mutley Posted October 4, 2015 Author Share Posted October 4, 2015 Thanks a lot for your help so far, I just don't get why it won't work, it doesn't bring any errors but it doesn't change any rows. Here is a screenshot of the query executing and the signature field... Signature field... Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1522233 Share on other sites More sharing options...
Barand Posted October 4, 2015 Share Posted October 4, 2015 Does this version work for you - enclosing the search text in single quotes and no escape of the double quotes? UPDATE `testsig` SET `signature` = replace( signature, '<p style="text-align:center;"> </p>', '' ) ; mysql> UPDATE `testsig` -> SET `signature` = replace( signature, '<p style="text-align:center;"> </p>', '' ) ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1522236 Share on other sites More sharing options...
Mutley Posted October 4, 2015 Author Share Posted October 4, 2015 Same problem, this is really leaving me confused, It executes fine but effects no rows. Searching for the string in fields with %LIKE%, finds all the fields with no issues too. So it can see the fields using this string. What could it be? I really appreciate your help so far guys! Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1522267 Share on other sites More sharing options...
benanamen Posted October 4, 2015 Share Posted October 4, 2015 Post an SQL dump of your data (Just a couple rows, only need signature column). @Barand's code is correct and works. Need to see how your data actually is in the DB. Link to comment https://forums.phpfreaks.com/topic/298336-find-and-replace-string-in-field/#findComment-1522285 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.