0

I need to query all posts using SQL (tried doing it with WP_Query didn't work correctly), of certain post types

$post_types = get_post_types(); 
unset( $post_types['attachment'], $post_types['nav_menu_item'] );

$post_type_string = '\'' . implode( '\', \'', $post_types ) . '\'';

global $wpdb;
$replace_query = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_type IN ( %s )", $post_type_string ), ARRAY_A );

The $post_types array will give me

array( 'post' => 'post', 'page' => 'page', 'revision' => 'revision', 'custom_css' => 'custom_css', 'attachment' => 'attachment', 'nav_menu_item'=> 'nav_menu_item', 'customize_changeset' => 'customize_changeset', 'tt_font_control' => 'tt_font_control' )

I imploded this array, to add quotes around the values, and put this in my $wpdb->prepare as shown, but all I got was empty array.

When I removed the prepare and did this

$replace_query = $wpdb->get_results( "SELECT * FROM $wpdb->posts WHERE post_type IN ( $post_type_string )", ARRAY_A );

The query worked and I got back full array.

Why did this happen?

The $post_type_string is a string, so %s is correct in the prepare.

I tried not 'quoting' the values in the string, but that didn't work as well, and I tried with double quotes. I know that prepare does

$query = str_replace( "'%s'", '%s', $query ); // in case someone mistakenly already singlequoted it
$query = str_replace( '"%s"', '%s', $query ); // doublequote unquoting
$query = preg_replace( '|(?<!%)%f|' , '%F', $query ); // Force floats to be locale unaware
$query = preg_replace( '|(?<!%)%s|', "'%s'", $query ); // quote the strings, avoiding escaped strings like %%s
array_walk( $args, array( $this, 'escape_by_ref' ) );
return @vsprintf( $query, $args );

}

from developers prepare docs. Does this happen because of the quotes? And if so, how to prepare my query correctly?

EDIT:

The WP_Query that doesn't work as expected

$temp_posts = new WP_Query( array(
            'post_type' => $post_types,
            'posts_per_page' => 1000000,
            'no_found_rows' => true,
            'post_status' => array( 'any', 'draft' )
            )
        );
Improve this question
6
  • When a function, method or code snippet doesn't work as you expect, you sould explain what you expected, what you get and the errors you get, if any; "it doesn't work" says nothing about the problem you may have. Can you explain what exactly "WP_Query didn't work correctly" means?
    – cybmeta
    Commented Feb 10, 2017 at 15:11
  • For some reason it won't fetch all post types, only posts. That's why I used a direct query. Trust me I error logged every single combination before posting this :)
    – dingo_d
    Commented Feb 10, 2017 at 15:14
  • WP_Query works with any post type; if you get only posts of standard post type, then you may be passing wrong data. Can you share your WP_Query snippet?
    – cybmeta
    Commented Feb 10, 2017 at 15:16
  • $temp_posts = new WP_Query( array( 'post_type' => $post_types, 'posts_per_page' => 1000000, 'no_found_rows' => true, 'post_status' => array( 'any', 'draft' ) ) ); I didn't use 'posts_per_page' => -1 because that slows the query significantly, and I needed a high number. I tried removing the no_found_rows, that did nothing, and I wanted to query everything in the array of post types.
    – dingo_d
    Commented Feb 10, 2017 at 15:21
  • Please, add the code to the question. Reading large code snipeets in comments is very difficult. Anyway, that WP_Query works perfectly here.
    – cybmeta
    Commented Feb 10, 2017 at 15:25

1 Answer 1

Reset to default
1

You can use the array directly because the $wpdb->prepare() method can be called like vsprintf(), which works like sprintf() but accepts arrays of arguments.

$post_types = get_post_types(); 
unset( $post_types['attachment'], $post_types['nav_menu_item'] );

global $wpdb;
$replace_query = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM $wpdb->posts WHERE post_type IN ( %s )", $post_types ), ARRAY_A );

The problem with your code is that you get a string like this one:

"'page', 'attachment', 'revision', 'custom_css', 'customize_changeset'"

So, the IN statement you get is like:

IN ( "'page', 'attachment', 'revision', 'custom_css', 'customize_changeset'" )

Instead of the correct one that would be an array of strings:

IN ( 'page', 'attachment', 'revision', 'custom_css', 'customize_changeset' )
Improve this answer
2
  • Really? Because it works as a string without preparing. I didn't try with array tbh
    – dingo_d
    Commented Feb 10, 2017 at 15:09
  • Answer updated to explain why you can use the array and how to convert it to string if you want.
    – cybmeta
    Commented Feb 10, 2017 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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