$wpdb->prepare syntax problem

[Ade Walker]

Hi all,

I wonder if someone can point out what I'm doing wrong here.

$pages_selected is a string of comma separated ID numbers, eg "6,22,31,43"

This finds all the page IDs in the $pages_selected string =>

$pages_found = $wpdb->get_results("SELECT ID,post_title FROM $wpdb->posts
WHERE $wpdb->posts.ID IN( $pages_selected )" );

This doesn't =>

$pages_found = $wpdb->get_results(
        $wpdb->prepare( "SELECT ID,post_title FROM $wpdb->posts WHERE
$wpdb->posts.ID IN( %s )", $pages_selected )
        );

It just finds the first page ID in the $pages_selected string.

I've searched high and low, but can't find out what I'm doing wrong. No
error in php error log either.

Any pointers gratefully received. :-)

Thanks.

Ade.
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Austin Matzko]

On Mon, Oct 26, 2009 at 1:10 PM, Ade Walker <photofantaisie@...> wrote:

> This doesn't =>
>
> $pages_found = $wpdb->get_results(
>        $wpdb->prepare( "SELECT ID,post_title FROM $wpdb->posts WHERE
> $wpdb->posts.ID IN( %s )", $pages_selected )
>        );
>
> It just finds the first page ID in the $pages_selected string.
>
> I've searched high and low, but can't find out what I'm doing wrong. No
> error in php error log either.
>
> Any pointers gratefully received. :-)

The problem is that you're sending this string to MySQL:

SELECT ID,post_title FROM wp_posts WHERE wp_posts.ID IN( '6,22,31,43' )

When you want

SELECT ID,post_title FROM wp_posts WHERE wp_posts.ID IN( 6,22,31,43)

Since you know the data to be sanitized has to be integers (assuming
it does need to be sanitized--ie the integers are coming from user
input),
a more direct alternative to doing this is the following:

$pages_selected = array(6,22,31,43);
$pages_found = $wpdb->get_results(
   sprintf("SELECT ID,post_title FROM $wpdb->posts WHERE
$wpdb->posts.ID IN( %s )", implode(',', array_map('intval',
$pages_selected) ) )
);
_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers

[Ade Walker]

Thanks, Austin! That's perfect.



2009/10/26 Austin Matzko <if.website@...>

> On Mon, Oct 26, 2009 at 1:10 PM, Ade Walker <photofantaisie@...>
> wrote:
> > This doesn't =>
> >
> > $pages_found = $wpdb->get_results(
> >        $wpdb->prepare( "SELECT ID,post_title FROM $wpdb->posts WHERE
> > $wpdb->posts.ID IN( %s )", $pages_selected )
> >        );
> >
> > It just finds the first page ID in the $pages_selected string.
> >
> > I've searched high and low, but can't find out what I'm doing wrong. No
> > error in php error log either.
> >
> > Any pointers gratefully received. :-)
>
> The problem is that you're sending this string to MySQL:
>
> SELECT ID,post_title FROM wp_posts WHERE wp_posts.ID IN( '6,22,31,43' )
>
> When you want
>
> SELECT ID,post_title FROM wp_posts WHERE wp_posts.ID IN( 6,22,31,43)
>
> Since you know the data to be sanitized has to be integers (assuming
> it does need to be sanitized--ie the integers are coming from user
> input),
> a more direct alternative to doing this is the following:
>
> $pages_selected = array(6,22,31,43);
> $pages_found = $wpdb->get_results(
>    sprintf("SELECT ID,post_title FROM $wpdb->posts WHERE
> $wpdb->posts.ID IN( %s )", implode(',', array_map('intval',
> $pages_selected) ) )
> );
> _______________________________________________
> wp-hackers mailing list
> wp-hackers@...
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>

_______________________________________________
wp-hackers mailing list
wp-hackers@...
http://lists.automattic.com/mailman/listinfo/wp-hackers