I am a bit of a mysql laymen and am asking for a bit of a helping hand.
I inherited a wordpress 3.1.0 website that has about 10,000 posts. Each post has a lot of irrelevant data that the original owner didn’t have to use, such as having Google Analytic data in each post when they could have used a plug-in (Im trying to imagine them copying that code 10k+ times).
I want to clean up the posts, but within each of the 10k posts are two lines of include files surrounded by “[]” that I wish to keep.
The posts look like this:
garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage
[include file=”filename” masterpath=”/home/name/dir”]
garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage
[include file=”filename” masterpath=”/home/name/dir”]
even more garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage garbage
My goal, after running a phpMyAdmin sql dump of course 🙂 is to delete all the garbage surrounding the [include file] statements.
There HAS to be a way to do this w/ an SQL line or two but again, I’m a mySQL laymen. Could someone help me out? Removing all instances of the code “manually” / “by hand” is out of the question…100 posts or so, maybe…but NOT 10k posts…
Thanks
I would suggest that you do a SQL dump as you suggest, then apply a find and replace to your SQL file using a regular expression pattern. If you have access to the “sed” utility, you can apply the following expression to update the SQL statements:
Note that the “-i” flag makes this edit inline, meaning the original file is altered. Please make sure you have a backup of the database dump first.
You will have to constrain this regular expression to only run against the appropriate SQL INSERT statements. I would suggest copying this portion out of your SQL dump file into a new file to apply this operation.
The “-r” flag will allow for an extended regular expression, which allows us to group our pattern matching. The following string instructs matches only the contents of the brackets and discards the rest of the data.
More information on sed here: http://unixhelp.ed.ac.uk/CGI/man-cgi?sed