3Lancer 3Lancer - 6 months ago 40
SQL Question

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

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.

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?

Answer

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 
)
Comments