1

I have an array containting these values (including the apostrohpes):

'course_3202','course_3201','course_3200','course_3199'

I try to fetch results from these values in postmeta-table.

SELECT * FROM wp_postmeta WHERE meta_key IN (%s) and meta_value=1

I get no result (yes, there is data and I should get results)

RESULT = Array ( )

My code looks like this:

function get_count_courses_array(array $courseids) {    
    $tmp = array();
    foreach($courseids as $cid) {
        $tmp[] = "'" . $cid . "'";
    }    
    $courseids = implode(',',$tmp);

    global $wpdb;
    $sql = "SELECT * FROM $wpdb->postmeta";
    $sql .= " WHERE meta_key IN (%s) and meta_value=1";        
    $result = $wpdb->get_results($wpdb->prepare( $sql, $courseids));

    return $result;    
}

In phpMyAdmin I tried the same and I do get results (as expected):

SELECT * FROM wp_postmeta WHERE meta_key IN ('course_3202','course_3201','course_3200','course_3199') and meta_value=1

What am I doing wrong?

2

You need to have your replacement placeholders matching the number of values in your array, and then you can use the array as one of the prepare arguments.

Proof of concept:

$a = array('course_3202','course_3201','course_3200','course_3199');

$b = array_fill(0,count($a),'%s');
$b = implode(',',$b);

$sql = "SELECT * FROM $wpdb->postmeta";
$sql .= " WHERE meta_key IN ({$b}) and meta_value=1";    

var_dump($wpdb->prepare($sql,$a));
1

You are querying to postmeta table so I would use WP_Query class and methods instead:

$courseids = array( 'course_3202', 'course_3201', 'course_3200', 'course_3199' );
$meta_query = array();

foreach( $courseids as $courseid ){
    $meta_query[] = array( 
                              'key'   => $courseid,
                              'value' => '1',
                              'compare' => 'IN',
                         );
}   

$args = array(
    'post_type' => 'post',
    'meta_query' => array(
                       $meta_query
               )
);
$query = new WP_Query( $args );

$count_courses = $query->found_posts;
  • Agreed, this is a safer approach and you also get the benefits of object caching and use of The Loop functions for the posts in question. – Otto Mar 20 '14 at 1:20
  • Why is this safer/better? I'm not interested in fetching objects really. The thing I want to do is just to count number of occurences for each course (course_3202 = 14, course_course_3201 = 22 etc). – bestprogrammerintheworld Mar 20 '14 at 5:32
  • Additinally to Otto's comment, you are querying to post-related table from Wordpress core and WP_Query is intened to deal with the intricacies of this type of requests. Worpress will built the query for you, so you don't have to worry about plain SQL sentences, possible changes of database in the future and very important: you let Wordpress deal with private post, drafts, revisiones, post types, and so. Note that your current query doesn't deal with post types, post status or post privacy. – cybmeta Mar 20 '14 at 7:39
  • Also note that in the above code only the total number of posts found matching the current query parameters is returned, not fetching $post objects. – cybmeta Mar 20 '14 at 7:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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