Getting Year & Date inside wpdb [duplicate]

Question

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?

Answer 1

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.

Answer 2

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');