Mutley Posted September 28, 2015 Share Posted September 28, 2015 (edited) 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. Edited September 28, 2015 by Mutley Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted September 28, 2015 Share Posted September 28, 2015 (edited) Something like this UPDATE `ipb_core_members` SET `signature` = replace(signature, "<p style=\"text-align:center;\"> </p>", '') Edited September 28, 2015 by QuickOldCar Quote Link to comment 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? Quote Link to comment 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? Quote Link to comment Share on other sites More sharing options...
Mutley Posted September 30, 2015 Author Share Posted September 30, 2015 I just use PHPMyAdmin. Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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> Quote Link to comment Share on other sites More sharing options...
Mutley Posted October 4, 2015 Author Share Posted October 4, 2015 (edited) 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... Edited October 4, 2015 by Mutley Quote Link to comment 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 Quote Link to comment 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! Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 4, 2015 Share Posted October 4, 2015 (edited) 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. Edited October 4, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.