0

I have this snippet:

function count_user_comments_today( $uid ){
    global $wpdb;
    $today = date('Y-m-d');
    $tomorrow = date('Y-m-d', time() + 86400);
    $count = $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->comments} WHERE user_id = %d  AND comment_date >= %s  AND comment_date < %s ", $uid, $today, $tomorrow  ));
    return $count;
}

But this get's the comments per day and that for a specific user. I want it to be generic, for the whole site and per hour, per year and per month.... Is this possible? Of course also the posts and not only the comments.

4
  • This sounds like a pure SQL question to me, but I am also not exactly sure what you want. Are you trying to produce some kind of array with posts sorted by hour, month, and year or a function that can pull posts by some particular hour, or month, or year?
    – s_ha_dum
    Aug 3 '13 at 15:37
  • No, not an array, but just the amount of the posts per month, per hour and per year, if that's possible... – Aug 6 '13 at 6:58
  • Further question: Do you mean "per Year", "per month per year", "per hour per month per year" or literally "everything posted at 11:00am on any month of any year" or "everything posted in January no matter the year"?
    – s_ha_dum
    Aug 6 '13 at 13:05
  • Both actually :) Aug 6 '13 at 13:59
1

For comments:

function count_comments_in_period( $date_from, $date_to ){
    global $wpdb;
    $count = $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_date >= %s  AND comment_date < %s ", $date_from, $date_to));
    return $count;
}

Just use dates in yyyy-mm-dd format.

For posts:

function count_posts_in_period( $date_from, $date_to ){
    global $wpdb;
    $count = $wpdb->get_var( $wpdb->prepare("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_date >= %s  AND post_date < %s ", $date_from, $date_to));
    return $count;
}
2

I don't have time to build both functions and I don't have time to test this one very much but I think this is at least close:

function funky_comment_query_wpse_108830($year, $month, $hour) {

  if (!empty($year)) {
    $sql[] = "YEAR(comment_date) = {$year}";
  }
  if (!empty($month)) {
    $sql[] = "MONTH(comment_date) = {$month}";
  }
  if (!empty($hour)) {
    $sql[] = "HOUR(comment_date) = {$hour}";
  }
  if (empty($sql)) {
    return;
  }

  global $wpdb;
  $sql = implode(' AND ',$sql);

  $count = $wpdb->get_var("SELECT count(comment_ID) FROM {$wpdb->comments} WHERE {$sql}");
  return $count;
}
echo funky_comment_query_wpse_108830('2013','','');

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.