wpdb query with sql expression

  • Resolved Paul de Wouters

    (@pauldewouters)


    14 years, 6 months ago

    Hi
    I’m trying to retrieve a list of year/months that have posts, such as generated by get_archives.
    here is my function, it seems it doesn’t work because of the expression or parentheses, I’m not sure.
    the sql statement is valid, and the function works if I remove the expression and just select the field.

    function do_post_dates(){
global $wpdb;
$query = $wpdb->escape("SELECT distinct concat(monthname(post_date), ' ', year(post_date) FROM wp_posts;");
print $query;
$post_dates = $wpdb->get_results($query);

return $post_dates;

}
Viewing 3 replies - 1 through 3 (of 3 total)
  • Thread Starter Paul de Wouters

    (@pauldewouters)

    I figured it out

    function do_post_dates(){
global $wpdb;
$rows = $wpdb->get_results("SELECT concat(year(post_date),'-', month(post_date)) as post_y from {$wpdb->prefix}posts group by year(post_date), month(post_date)");

$post_dates = $rows;
return $post_dates;

}

    Examine this line you have..

    $wpdb->escape("SELECT distinct concat(monthname(post_date), ' ', year(post_date) FROM wp_posts;");

    You have more opening brackets then closing brackets.

    You also have a misplaced semi-colon after wp_posts.

    Thread Starter Paul de Wouters

    (@pauldewouters)

    thanks, that was probably the problem!
    I rewrote the query in the meantime.

    thanks for helping

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘wpdb query with sql expression’ is closed to new replies.