Using PhpMyAdmin (or Mysql) for a find and replace operation

John H
John H used Ask the Experts™
on
Hi Folks,

I have located the database table and column that contains a combined text and html phrase that I would simply like to delete (for now).

I suspect i'm not using Phpmyadmin's "Find and Replace option properly - the exact content I want removed are:

"Is this tape helpful for AA Newcomers?  If so, please<strong> add some Comments</strong> about this tape's topics? (e.g., "For Newcomers" "Step 1", "Inventory", "Spirituality", etc?)

Your comments will be used to tag and categorize the tape properly.

<strong>THANK YOU!!</strong> :)


How might i do this through "find and replace" or construct a proper mysql statement to remove this?

Thank you!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
First of all, do you want to replace that column's content with spaces or do you want to delete the entire row that contains it?

Does a query that looks for "Is this tape helpful for AA Newcomers?" match this row and ONLY this row?  If so then you don't need to search for that entire long string.  If not, is there another column that contains a value that would uniquely match this row, and ONLY this row, if it were used as an additional search criterion?

We would need answers to these questions in order to construct the correct query to accomplish your goal.  Knowing the column name(s) would be beneficial as well otherwise you will likely get a rather generic solution offered.
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Databases don't have 'Find and Replace' functions.  I have never seen such a thing in the many versions of phpMyAdmin that I use.  I would first use the search function on that table to find out how many rows have this text.  If there is only 1, you can manually edit it.
I forgot to post a sample query that you could modify for your table to do the search...

  select * from tblname where txtfield like  "Is this tape helpful for AA Newcomers?%";

notice the "%" character at the end of the string in the where clause,,, it is a wildcard pattern that says find all rows with txtfield that begin with the partial string in front of it.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Distinguished Expert 2017

Commented:
In short it is a two step, you first pull the column into a variable, you then change the variable content to say what you want, then update the column.

Alternatively,
If you want to change it in its entirety run
update tablename set column='your desired value' where column like '%pattern uniquely identifying%' or idolumn=specific_id_of_row

Open in new window

John HGeneral Tinkerer

Author

Commented:
Thanks Jester - I think we're heading in the right direction.

With your above SELECT query, it came back with some 3300 rows affected - but there was no option to update or delete that string.

So I tried DELETE FROM `wp_zt8sq9_posts` WHERE post_content like  "Is this tape helpful for AA Newcomers?%";....   And a bunch of posts were not showing up on the site and I did catch one 404'ing.  I've since imported the old database and everything is back to normal.

Should I be using a different query string other than DELETE?

@ Arnold - lemme tinker with your query...
Distinguished Expert 2017
Commented:
Do not do delete as it removes the entire row, if you run, your need is to run update that modifies a column.
The selects are a must to make sure your search criteria is narrow enough to match one or a few rows with data......

Could you provide an example of what the result of the modification you want to end up with.
Current
Having this is some stuff that I have
Want
Having stuff
You need to find another column whose value along with the txtfield value will form a unique combination.

Then, you could use a select such as this to confirm that only 1 row is returned from the select query BEFORE changing it to an update query...

select * from tblname where somecolumn = 'somevalue' and txtfield like 'Is this tape helpful for AA Newcomers?%';
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
They are correct, UPDATE is the function you want.  You can use it to replace the text you want to get rid of with something else without DELETE-ing the rows.
John HGeneral Tinkerer

Author

Commented:
OOps on Delete :/

Current:  I have unwanted text in the body of thousands of posts in wordpress.  This text/hmtl is located in the table "wp_zt8sq9_posts", column name "post_content",  and the content I want removed begins with: "Is this tape helpful for AA Newcomers?..........(with a bunch text and some html after it)"

What I want is to clear/delete all instances of text/html in that column that begins with ""Is this tape helpful for AA Newcomers?........"  so that it is no longer printed on the posts that currently contain that text.

Thanks a bunch for helping me with this...
update wp_zt8sq9_posts set post_content = '' where post_content like 'Is this tape helpful for AA Newcomers?%';
John HGeneral Tinkerer

Author

Commented:
Gents, thank you so much for your patience, super-helpful, and I understand it, which is even better.  5000+ posts just got irrelevant/duplicate text removed :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial