Link to home
Start Free TrialLog in
Avatar of John H
John HFlag 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!
Avatar of JesterToo
JesterToo
Flag of United States of America image

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.
Avatar of 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.
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.
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

Avatar of 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
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?%';
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.
Avatar of 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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of 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 :)