The query argument of wpdb::prepare() must have a placeholder

2 Views

I’m attempting to update some old code to use in a WordPress plugin.

What it is doing is taking the customer orders and checking if they have already purchased that Woocommerce product before. If it has, it will get the last purchase date. It actually works fine, but the query is dated. I would like to update it to work with WordPress 4.5.

Read More

I currently get the warning message (3 times):

The query argument of wpdb::prepare() must have a placeholder 

$orders = get_posts( array(
        'meta_key'    => '_customer_user',
            'meta_value'  => get_current_user_id(),
            'post_type'   => 'shop_order',
            'post_status' => array( 'wc-processing', 'wc-completed' )
    ) );

$orders_id=wp_list_pluck( $orders, 'ID' ); //List of all order IDs

$product_id = $post->ID; // Get current product ID

$order_list='('.join(',', $orders_id).')';

global $wpdb;

$query_select_order_items = "SELECT order_item_id as id FROM {$wpdb->prefix}woocommerce_order_items WHERE order_id IN {$order_list}";

$query_select_product_ids = "SELECT meta_value as product_id FROM {$wpdb->prefix}woocommerce_order_itemmeta WHERE meta_key=%s AND order_item_id IN ( $query_select_order_items )";

$query_single_order_ids = "SELECT order_item_id FROM {$wpdb->prefix}woocommerce_order_itemmeta WHERE meta_key=%s AND meta_value=$product_id";

$products = $wpdb->get_col( $wpdb->prepare ( $query_select_product_ids,'_product_id' ) );

$order_item_id = $wpdb->get_col( $wpdb->prepare ( $query_single_order_ids, '_product_id' ) );

$hg_abb_li1 = $wpdb->get_col( $wpdb->prepare ( $query_select_order_items, '_product_id' ) );

$hg_abb_list1 = '('.join(',', $hg_abb_li1).')';
$hg_abb_list2 = '('.join(',', $order_item_id).')';

$query_select_hgabb_items = "SELECT order_id FROM {$wpdb->prefix}woocommerce_order_items WHERE order_item_id IN {$hg_abb_list1} AND order_item_id IN {$hg_abb_list2}";
$hg_abb_orderid = $wpdb->get_col( $wpdb->prepare ( $query_select_hgabb_items, '_order_item_id' ) );

$hg_abb_dateorderid = end($hg_abb_orderid);

$query_select_hgabb_date = "SELECT post_date FROM {$wpdb->prefix}posts WHERE ID = {$hg_abb_dateorderid}";
$l_order_date=$wpdb->get_col( $wpdb->prepare ( $query_select_hgabb_date, '_order_date' ) );

$last_order_date = (($l_order_date[0]));

EDIT: So should it be something like? 

$products = $wpdb->get_col( $wpdb->prepare ( "SELECT meta_value as product_id FROM ".$wpdb->prefix."woocommerce_order_itemmeta WHERE meta_key='_product_id' AND order_item_id IN ( SELECT order_item_id as id FROM ".$wpdb->prefix."woocommerce_order_items WHERE order_id IN (%s) )",$order_list ) );

and 

$hg_abb_orderid = $wpdb->get_col( $wpdb->prepare ( 'SELECT order_id FROM '.$wpdb->prefix.'woocommerce_order_items WHERE order_item_id IN %s AND order_item_id IN %s', $hg_abb_list1, $hg_abb_list2 ) );

Can the %s (string?) still be used for arrays or won’t that work?

Post Views: 2

Related posts

Leave a Reply

2 comments

  1. When you use $wpdb->prepare you must pass the variables to the query. For example

        $min_id = 5
    $status = 'active'
    $wpdb->prepare( 
"SELECT id FROM wp_posts WHERE id > %d AND `post_status` = %s", $min_id, $status 
)

  2. Nailed it. Was getting confused over arrays in the $wpdb->prepare statement. Seems you need to generate placeholders for every single array element.

    If anyone else needs help to create an array of placeholders…

    • count the array
    • use array_fill is generate %s (if string) or %d (if digits)
    • implode it

    This will created a string of %d, %d, %d, %d, etc (to match the number of values in the array), then you can use the prepare statement with those placeholders and the entire array.

    For example (if $orders_id was the array):

    $orders_id_placeholders = implode( ', ', array_fill( 0, count( $orders_id ), '%d' ) );
$hg_abb_li1 = $wpdb->get_col( $wpdb->prepare ( "SELECT order_item_id as id FROM {$wpdb->prefix}woocommerce_order_items WHERE order_id IN ( $orders_id_placeholders )", $orders_id ) );