How to – filter comments per date and email address and no. of comments

  • Hello, I would like to award the most commenting people every month.
    In my blog people cannot register.

    I would like to:
    – Filter all the comments from day June 1 to June 30;
    – Sort ascending/descending per number of comments;
    – See the email address of the commenters;

    In this way I can write to the person that commented the most and send the award.

    How can I do that?
    Thank you

Viewing 15 replies - 1 through 15 (of 16 total)
  • Moderator keesiemeijer

    (@keesiemeijer)

    Try it with this:

    <?php
global $wpdb;
$comments = $wpdb->get_results("SELECT comment_author_email FROM $wpdb->comments WHERE  comment_approved = '1' AND comment_date >= '2011-06-01' AND comment_date < '2011-06-30'");
if($comments){
foreach($comments as $comment){

$coment_email[] = $comment->comment_author_email;
}
$best_commenters = array_count_values($coment_email);
arsort($best_commenters);
foreach($best_commenters as $email => $value){
	$comment_author= $wpdb->get_results("SELECT comment_author FROM $wpdb->comments WHERE  comment_approved = '1' AND comment_author_email = '".$email."' ");
	echo '<p>comments: '.$value.'<br />comment author: '.$comment_author[0]->comment_author.'<br />comment email: '.$email.'</p>';
}
} else { echo 'there are no comments'; }
?>

    Don’t print out the comment author emails publicly. Make a private Post or Page and use conditional tags so only you get to see the results.

    Thread Starter utnalove

    (@utnalove)

    Thank you. Should I create a pagexyz.php, paste that code inside, put this php file on my WP root and it should work?

    Moderator keesiemeijer

    (@keesiemeijer)

    Create a custom page template(duplicate your page.php and rename it) and use this code in stead of the loop. Put this file in your theme folder. Publish a Page with this page template and set it to private. Now when you are logged in you can go to this page and see the results. In your header inside the <head></head> tags u can use:

    <?php
if(is_page( '42' ) ){ // the page ID
echo '<meta name="robots" content="noindex, nofollow" />';
}
?>

    (change the page ID to your private Page ID)
    This is to stop searchengines indexing this Page.

    Thread Starter utnalove

    (@utnalove)

    Hello, I do not see any loop on my page.php.
    What I see is the following:

    <?php get_header(); ?>
	<div class="middle_single">
		<?php if (have_posts()) : ?>
		<?php while (have_posts()) : the_post(); ?>
			<div class="post_blog">
<h1><?php the_title(); ?></h1>
				<div style="clear:both"></div>	

					<div class="entry_blog">

						<?php the_content(); ?>	

					</div>
	  			<div style="clear:both;"></div>
	  			<?php endwhile; ?>
			<?php endif; ?>
		</div>
	</div>
<?php include(TEMPLATEPATH . '/sidebar-single.php'); ?>
<?php get_footer();?>
    Moderator keesiemeijer

    (@keesiemeijer)

    replace this with my code:

    <?php if (have_posts()) : ?>
		<?php while (have_posts()) : the_post(); ?>
			<div class="post_blog">
<h1><?php the_title(); ?></h1>
				<div style="clear:both"></div>	

					<div class="entry_blog">

						<?php the_content(); ?>	

					</div>
	  			<div style="clear:both;"></div>
	  			<?php endwhile; ?>
			<?php endif; ?>
		</div>

    Thread Starter utnalove

    (@utnalove)

    Thank you.
    Instead of putting `<?php
    if(is_page( ’42’ ) ){ // the page ID
    echo ‘<meta name=”robots” content=”noindex, nofollow” />’;
    }
    ?>` in the header, can I put it in the robots.txt?

    Disallow in the robots txt would work the same as noindex, nofollow?
    I would like to avoid a new php query :/ My hosting is very often getting CPU throttling

    Moderator keesiemeijer

    (@keesiemeijer)

    Yes you can also put it in the robots.txt

    Thread Starter utnalove

    (@utnalove)

    Ok, now my page looks like this:

    <?php get_header(); ?>
	<div class="middle_single">
		<?php
global $wpdb;
$comments = $wpdb->get_results("SELECT comment_author_email FROM $wpdb->comments WHERE  comment_approved = '1' AND comment_date >= '2011-06-01' AND comment_date < '2011-06-30'");
if($comments){
foreach($comments as $comment){

$coment_email[] = $comment->comment_author_email;
}
$best_commenters = array_count_values($coment_email);
arsort($best_commenters);
foreach($best_commenters as $email => $value){
	$comment_author= $wpdb->get_results("SELECT comment_author FROM $wpdb->comments WHERE  comment_approved = '1' AND comment_author_email = '".$email."' ");
	echo '<p>comments: '.$value.'<br />comment author: '.$comment_author[0]->comment_author.'<br />comment email: '.$email.'</p>';
}
} else { echo 'there are no comments'; }
?>
<?php include(TEMPLATEPATH . '/sidebar-single.php'); ?>
<?php get_footer();?>

    however I am getting the following error:

    Fatal error: Call to undefined function get_header() in /home/me/public_html/mysite/wp-content/themes/mytheme/thenewpage.php on line 1

    Second question… how can I make this page private so that even knowing the direct URL, only the logged in admin can access it?

    Moderator keesiemeijer

    (@keesiemeijer)

    Is the file in your theme folder? Is this the whole file? Did you make a custom page template with this at the top:

    <?php
/*
Template Name: newpage
*/
?>

    Second question… how can I make this page private so that even knowing the direct URL, only the logged in admin can access it?

    In the same panel as the “Publish” button you can set a page Visability to “Private”

    Thread Starter utnalove

    (@utnalove)

    Excellent!!! Thank you!! It works ??
    Pity that I don’t have the white background… I have black text on violet background.

    I was making the error and trying to enter mysite.com/wp-content/themes/mytheme/newpage.php instead of simply mysite.com/newpage.php

    At this point I am wondering if in the robots.txt I have to put the whole path to the file or only the permalink mysite.com/newpage.php

    Thread Starter utnalove

    (@utnalove)

    One more thing. I understand that if I want to include the comments done in day 30 I have to change:

    comment_date >= '2011-06-01' AND comment_date < '2011-06-30'
    to
    comment_date >= '2011-06-01' AND comment_date <= '2011-06-30'

    ?

    Moderator keesiemeijer

    (@keesiemeijer)

    Pity that I don’t have the white background…

    try this. Put this above my code: <div class="post_blog"> and this after it: </div>

    At this point I am wondering if in the robots.txt I have to put the whole path to the file or only the permalink mysite.com/newpage.php

    I don’t know much about robots.txt. Google it.

    One more thing. I understand that if I want to include the comments done in day 30 I have to change:

    try this: comment_date = '2011-06-01' (not tested)

    Thread Starter utnalove

    (@utnalove)

    Hello, I have tried but I still have violet background/no background.

    Please look at my code right now:

    <?php
/*
Template Name: topcommenters
*/
?>
<?php get_header(); ?>
	<div class="middle_single">
<div class="post_blog">
		<?php
global $wpdb;
$comments = $wpdb->get_results("SELECT comment_author_email FROM $wpdb->comments WHERE  comment_approved = '1' AND comment_date >= '2011-06-01' AND comment_date < '2011-07-01'");
if($comments){
foreach($comments as $comment){

$coment_email[] = $comment->comment_author_email;
}
$best_commenters = array_count_values($coment_email);
arsort($best_commenters);
foreach($best_commenters as $email => $value){
	$comment_author= $wpdb->get_results("SELECT comment_author FROM $wpdb->comments WHERE  comment_approved = '1' AND comment_author_email = '".$email."' ");
	echo '<p>comments: '.$value.'<br />comment author: '.$comment_author[0]->comment_author.'<br />comment email: '.$email.'</p>';
}
} else { echo 'there are no comments'; }
?>
</div>
<?php include(TEMPLATEPATH . '/sidebar-single.php'); ?>
<?php get_footer();?>

    Thread Starter utnalove

    (@utnalove)

    Oh, and I can also see that with or without the <div class=”post_blog”> the sidebar is broken. The widgets instead of appearing on the sidebar, are appearing under the page.

    Moderator keesiemeijer

    (@keesiemeijer)

    Try it with this: https://pastebin.com/AjJKuXJ2

Viewing 15 replies - 1 through 15 (of 16 total)
  • The topic ‘How to – filter comments per date and email address and no. of comments’ is closed to new replies.

Tags