Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I need to query the database on add_action( 'init', '...') to find post ids with the field 'product_discount_option' set to true.

I know at least one of my product posts have the field 'product_discount_option' set to true. However when i run the SQL query below and var_dump it outputs an empty array , array(0) { } ..

add_action( 'init', 'retrieve_product_ids');

function retrieve_product_ids(){
global $wpsc_cart, $wpdb;

    $product_ids = $wpdb->get_col( $wpdb->prepare( "SELECT object_id FROM `".WPSC_TABLE_META."` WHERE `meta_key` = 'product_discount_option' AND meta_value = true" ) );

    var_dump($product_ids);
}

The field 'product_discount_option' has been setup using the Advanced Custom Fields Plugin. The posts are WPeC custom post types, post_type = 'wpsc-product'.

I'm new to SQL queries, sorry if i've missed anything in the question.

thanks Cam

share|improve this question
    
Is your constant set? Try var_dump with your query string alone-- none of the $wpdb stuff. Something like var_dump("SELECT object_id FROM....") Does the query look sane? –  s_ha_dum Nov 29 '12 at 15:27
    
hey s_ha_dum, the constant WPSC_TABLE_META returns sb_wpsc_meta. will try var_dump'ing the sql query now. one moment.. –  Cam Nov 29 '12 at 15:34
    
string(103) "SELECT object_id FROM sb_wpsc_meta WHERE meta_key = 'product_discount_option' AND meta_value = true" –  Cam Nov 29 '12 at 15:38
    
That looks OK if it matches your table structure. I am betting that the type for your meta_value column is some kind of string (varchar, longtext, something) so I'd stick quotes around 'true' just to be safe. –  s_ha_dum Nov 29 '12 at 15:46
add comment

1 Answer 1

up vote 0 down vote accepted

Thanks for your help s_ha_dum,

I had a look at the table structure using phpMyAdmin, ACF Plugin saves fields to the postmeta table, so sb_wpsc_meta needed to be changed to sb_postmeta. also needed to change what was being SELECT to post_id aswell as the meta_value to 1.

final query;

"SELECT post_id FROM sb_postmeta WHERE `meta_key` = 'product_discount_option' AND meta_value = 1" 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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