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.

This question already has an answer here:

I am creating a shortcode to display data based on some options.

function dot_irt_top_posts ( $atts, $content = null ) {

    // get our variable from $atts
    extract(shortcode_atts(array(
        'before' => '<li>',
        'after' => '</li>',
        'number' => '10',
        'post_type' => 'post',
        'year' => '',
        'monthnum' => '',
        'show_count' => '1',
    ), $atts));

    global $wpdb;

    $request = "SELECT * FROM $wpdb->posts, $wpdb->postmeta";
    $request .= " WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id";

    if ($year != '') {
        $request .= " AND year=$year";
    }

    if ($year != '' && $monthnum != '') {
        $request .= " AND &monthnum='$monthnum'";
    } elseif ($monthnum != '') {
        $request .= " AND monthnum='$monthnum'";
    }

    $request .= " AND post_status='publish' AND post_type='$post_type' AND meta_key='_recommended'";
    $request .= " ORDER BY $wpdb->postmeta.meta_value+0 DESC LIMIT $number";
    $posts = $wpdb->get_results($request);

    $return = '';


    foreach ($posts as $item) {
        $post_title = stripslashes($item->post_title);
        $permalink = get_permalink($item->ID);
        $post_count = $item->meta_value;
        $return .= $before.'<a href="' . $permalink . '" title="' . $post_title.'" rel="nofollow">' . $post_title . '</a> ';

        if ( $show_count == '1') {
            $return .= '<span class="votes">' . $post_count . '</span> ';
        }

        //$return .= get_the_post_thumbnail($item->ID, 'showcase-thumbnail');
        $return .= $after;

    }
    return $return;

}
add_shortcode('irt_top_posts','dot_irt_top_posts');

And then the shortcode would be like:

<?php echo do_shortcode("[irt_top_posts post_type='showcase' number='10' monthnum='9']"); ?>

UPDATED QUESTION's

  1. There seems to be some issue with my query. The Codex http://codex.wordpress.org/Class_Reference/WP_Query#Time_Parameters has an example for using the date to retrive results when using WP_Query. I want to know what changes I need to make to my code to have YEAR or MONTH in the query when getting results
  2. I noticed most shortcodes when communicating with database use wpdb class and not wp_query. Is there a reason behind this?
share|improve this question

marked as duplicate by kaiser, toscho Sep 28 '13 at 3:29

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2  
Show us the results of $wpdb->last_error and $wpdb->last_query as edit to your question. Also your MarkUp has problems. Please edit your question and fix that. –  kaiser Sep 27 '13 at 16:52
    
Ok finally got it from here wordpress.stackexchange.com/questions/56966/… –  Harish Chouhan Sep 28 '13 at 1:00

2 Answers 2

You mention the Codex page for WP_Query but you are not using WP_Query. You are using wpdb.

WP_Query is a very complicated class for querying the WordPress tables for post (CPT) data. wpdb is a lightweight wrapper around PHP SQL functions. With WP_Query you can pass arguments like monthnum because the WP_Query class converts the argument into proper SQL.

With wpdb you have to write the SQL. $wpdb->get_results essentially passes your SQL through to the database engine. If you look at the database descriptions, there is no column named monthnum. You are asking MySQL to pull data from a column that does not exist. That is why you get an error.

You need to write valid SQL-- look at the date manipulation functions-- or use WP_Query whose Codex page you are already investigating. The latter is the better choice.

share|improve this answer
    
thanks for that. My question was wrong so have updated it. Would it be possible for you to let me know the right query to use to get posts by year or by year+month. –  Harish Chouhan Sep 28 '13 at 0:32

Finally got the answer from another post Custom $wpdb Query for Custom Post Type by Category:

function dot_irt_top_posts ( $atts, $content = null ) {

// get our variable from $atts
extract(shortcode_atts(array(
    'before' => '<li>',
    'after' => '</li>',
    'number' => '10',
    'post_type' => 'post',
    'year' => '',
    'monthnum' => '',
    'show_count' => '1',
), $atts));

global $wpdb;

$request = "SELECT * FROM $wpdb->posts, $wpdb->postmeta";
$request .= " WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id";

if ($year != '') {
    $request .= " AND YEAR(post_date) = '$year'";
}

if ($monthnum != '') {
    $request .= " AND MONTH(post_date) = '$monthnum'";
}

$request .= " AND post_status='publish' AND post_type='$post_type' AND meta_key='_recommended'";
$request .= " ORDER BY $wpdb->postmeta.meta_value+0 DESC LIMIT $number";
$posts = $wpdb->get_results($request);

$return = '';


foreach ($posts as $item) {
    $post_title = stripslashes($item->post_title);
    $permalink = get_permalink($item->ID);
    $post_count = $item->meta_value;
    $return .= $before.'<a href="' . $permalink . '" title="' . $post_title.'" rel="nofollow">' . $post_title . '</a> ';

    if ( $show_count == '1') {
        $return .= '<span class="votes">' . $post_count . '</span> ';
    }

    //$return .= get_the_post_thumbnail($item->ID, 'showcase-thumbnail');
    $return .= $after;

}
return $return;

}
add_shortcode('irt_top_posts','dot_irt_top_posts');
share|improve this answer

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