I want to query all of the posts in my WordPress multi-site set-up looking for a certain string. This is only being done in Sequel Pro (on the command line) for my own inquisitive purposes but there has got to be a better way than copying and pasting the same query over and over again and just changing the table prefix...

Let's say I have 3 sites, I want to find all of the posts that contain the word foo in them:

SELECT * FROM wp_1_posts WHERE `post_content` LIKE '%foo%';
SELECT * FROM wp_2_posts WHERE `post_content` LIKE '%foo%';
SELECT * FROM wp_3_posts WHERE `post_content` LIKE '%foo%';

I don't want to have to run 3 queries and concat the results somehow.

What I really want to do is

SELECT * FROM wp_1_posts, wp_2_posts, wp_3_posts WHERE `post_content` LIKE '%foo%';

but that seems to only be meant for joining. Is there any way to do what I'm trying to do?

  • 3
    This seems a little more in SQL department if you aren't trying to implement it in WP? :) I have looked what desktop client I use (HeidiSQL) does for multi-table search and it just generates multiple queries. – Rarst Jul 25 '13 at 14:38
  • I think that is the way to go actually. I was being lazy and wanted to display all the output at once without having to copy and paste so I used a UNION command. – kingkool68 Jul 26 '13 at 14:46
FROM wp_posts, wp_2_posts, wp_3_posts 
    wp_posts.post_content LIKE '%foo' OR
    wp_2_posts.post_content LIKE '%foo' OR
    wp_3_posts.post_content LIKE '%foo';

should do the trick.

(Note that in Multisite, there shouldn't be a wp_1_posts -- your root site uses wp_posts etc.)

  • Good idea. And you're correct there is no wp_1_. Silly me. – kingkool68 Jul 25 '13 at 15:32
  • 1
    That's ok -- every time I loop over my sites, I have to remember that wp_1_posts will throw an error. – Pat J Jul 29 '13 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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