Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I hope I worded the question right. Here are the specifics:

This is regarding a MySQL database. I've inherited several hundred posts with custom fields and a unique excerpt. At this point I think I can solve the problem by inserting the <!--More--> tag into each post at a specific point. I need to do a search and replace with the following parameters:

  1. Search each post_content field for the first occurrence of </em>
  2. Insert the <!--More--> tag right after the </em>

Unfortunately each post has multiple <em> tag pairs, and if a post has more than one <!--More--> tag in it, WordPress just ignores all of them. So I need to so some sort of search and replace, but I have had no success after two days of trying every possible code snippet, SQL query and plugin I could find.

share|improve this question
    
tnx for the edits - definitely a db question –  dixonge Jun 6 '13 at 13:03

1 Answer 1

up vote 2 down vote accepted

This may come across as silly, but if the CMS ignores subsequent <!--more--> tags, you shouldn't really care if it adds one after every </em> closing tag. Sure it may make the posts slightly larger than they should be, but since they're just going to be ignored, it seems silly to spend two days trying to not do that when at the end of the day it doesn't really matter whether you do or not.

That said, I am not a MySQL guy, but looks like this is as decent an approach as any, from this StackOverflow answer:

UPDATE wp_posts
 SET post_content = CONCAT(REPLACE(LEFT(post_content, 
   INSTR(post_content, '</em>')+4), '</em>', '</em><!--more-->'), 
   SUBSTRING(post_content, INSTR(post_content, '</em>') + 5))
 WHERE INSTR(post_content, '</em>') > 0;

or the (slightly simpler):

UPDATE wp_posts
 SET post_content = CONCAT(LEFT(post_content, INSTR(post_content, '</em>')-1),
                           '</em><!--more-->',
                           SUBSTRING(post_content, INSTR(post_content, '</em>')+ 5))
 WHERE INSTR(post_content, '</em>') > 0;

Before you do that you may want to check to see how many rows this will affect:

SELECT COUNT(*)
  FROM wp_posts
  WHERE INSTR(post_content, '</em>') > 0;

You'll probably want to add a WHERE clause to only identify those posts that actually contain an </em> tag (and you may need to define the requirements for what to do in those cases).

share|improve this answer
    
I wasn't clear. WordPress doesn't ignore subsequent More tags, it ignores all of them if there is more than one... I'll try your query and report back. –  dixonge Jun 6 '13 at 12:33
    
Well it doesn't error, but it returns 0 rows. When I search for </em> in post_content via PHPMyAdmin I have to set the operator to REGEXP in order to get results. So I guess I'll need to add that plus WHERE, but I am weak and rusty on my SQL... –  dixonge Jun 6 '13 at 12:58
    
@dixonge what does your WHERE clause look like? Do you have any other way to run queries against the database aside from the web-based PHPMyAdmin? –  Aaron Bertrand Jun 6 '13 at 15:16
    
As I hinted, I have no idea how to add a WHERE clause to the existing query. I have CLI access, both on my local LAMP stack and on the web server where I will eventually deploy. –  dixonge Jun 6 '13 at 19:38
2  
Success! Yes, that did it. I just checked a random month from the archives - looks great. Thank you Aaron - I learned a lot! And thanks to ypercube for the adjustment. –  dixonge Jun 6 '13 at 20:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.