Avatar of John H
John H
Flag for United States of America asked on

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

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!
MySQL Server

Avatar of undefined
Last Comment
John H

8/22/2022 - Mon
JesterToo

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 Baldwin

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.
JesterToo

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
arnold

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 H

ASKER
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...
SOLUTION
arnold

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JesterToo

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?%';
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dave Baldwin

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 H

ASKER
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...
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
John H

ASKER
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 :)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy