Support » Fixing WordPress » get user comments from certain categories

Viewing 15 replies - 1 through 15 (of 29 total)
  • I have a question/answer type of site going where questions are posts and answers are comments.

    If answers are comments, where’s the query for comments, i only see a query to fetch a post in your code.

    Could you please clarify.

    Sorry about that.

                                                $comments = getUserAnswers($curauth->ID);
                                                if (!empty($comments)) { ?>

    That’s just before the first snippet.

    function getUserAnswers($user_id){
    	global $wpdb;
    	$gather_comments = "SELECT * FROM ".$wpdb->prefix."comments WHERE user_id = '" . $user_id . "' ORDER BY comment_date DESC LIMIT 10";
    	$user_comments = $wpdb->get_results($gather_comments);
    	return $user_comments;

    And that is in the functions.php.

    Ok, and how will you know which categories are set on the users profile page?

    Comments don’t have a category themselves, so i’m not quite following how you can filter comments based on category, can you elaborate on your usage of categories with comments.

    What I’m trying to do is this: When the user answers (comments) a question in a certain category (i.e. education), he could see that and his other previous answers on his profile page under “education”.

    To be honest, I am not sure if this is even possible, but I figured that when it is possible to show the comments the user has made, it is possible to get them based on what category the post he commented was in.

    You could add another parameter to your function and update the query to check for category, like so.

    Update the getUserAnswers call.

    $comments = getUserAnswers( $curauth->ID, $question_category[0]->term_id ); // Assuming the $question_category var is available at this point

    Update the function.

    function getUserAnswers( $user_id = 0, $category = 0 ){
    	global $wpdb;
    	$category = (int) $category;
    	$user_id = (int) $user_id;
    	if( $user_id < 1 )
    	$gather_comments = "SELECT c.* FROM {$wpdb->prefix}comments c ";
    	if( $category > 0 )
    		$gather_comments .= "JOIN {$wpdb->prefix}posts p ON p.ID = c.comment_post_ID JOIN {$wpdb->prefix}term_relationships tr ON tr.object_id = p.ID JOIN {$wpdb->prefix}term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id ";
    	$gather_comments .= "WHERE c.user_id = $user_id ";
    	if( $category > 0 )
    		$gather_comments .= "AND tt.taxonomy = 'category' AND tt.term_id = $category ";
    	$gather_comments .= "ORDER BY comment_date DESC LIMIT 10";
    	$user_comments = $wpdb->get_results($gather_comments);
    	return $user_comments;

    Untested, so you’ll have to let me know if there’s any problems… 🙂

    Well, it’s almost working. In fact I think your code solves the problem, I just cannot get the answers in right categories.

    I have two categories and when a question is posted in other, it shows in both and in other category the question appears in just the opposite.

    Thank you very, very much for your help though.

    You’ll need to clarify further please if i’m too understand the issue.

    The category is pulled from the post, so any category is based on the first category the post is in (if the post is given 2 categories, then which is first may vary).

    Is this post in multiple categories?

    Should the function ever only check one category and none else?

    Every post is only assigned to one category.

    The current problem is that if I have categories “education” and “work” and comment a post in education category, the comment shows under both “education” and “work” in user’s profile page.

    But when commenting a post in “work” category, the comment shows under “education”, but not “work”.

    Your code I think works perfect, it is just that for some reason I cannot get the comments to show in right places.

    The current problem is that if I have categories “education” and “work” and comment a post in education category, the comment shows under both “education” and “work” in user’s profile page.

    It shouldn’t do if these posts are only assigned one category.

    If you view your post list (edit.php), and check the categories next to each post do they each have one category only? (worth checking, you might of checked both by mistake).

    I could really do with seeing how all the code you’ve shown is being used, is this all being used in one file? If so, which file? Just trying to see the bigger picture of how you’re using the code.

    No multiple categories here, when user posts a question (post) he can choose only one category.

    Code for the page where questions are asked is as follows (this is one single php file):

    if ( $_POST['question_post'] == '1') {
    	$question_title = $_POST['question_title'];
    	$question_content = $_POST['question_content'];
    	$question_category = $_POST['cat'];
    	$message = post_new_question($question_title, $question_content, $question_category);
    get_header(); ?>
        <div id="main">
        	<div id="mainContent">
                <!-- Center Column -->
                <div id="centerCol" class="left">
                   <!-- Search Form -->
    				<?php get_search_form(); ?>
                    <!-- Search Form -->
                        <!-- Tabs Content Blocks -->
                        <div class="Box1">
                            <div class="BoxInner">
                                <h2><?php the_title(); ?></h2>
                                <?php if (have_posts()) : while (have_posts()) : the_post(); ?>
                                <?php display_message($message) ?>
                                // If logged in change nav to My Account and Log Out
                                if ( is_user_logged_in() ) { ?>
                                    <div class="post" id="post-<?php the_ID(); ?>">
                                        <form method="post" action="" id="postNewQuestion" name="postNewQuestion">
    <label>Select a Question Category:<span class="asterixRequired">*</span></label>
                                       <div class="catselect">
                                            <?php wp_dropdown_categories('orderby=name&hide_empty=0&exclude=1,7,8&hierarchical=1') ?></div><br/>
                                            <label>Question Title:<span class="asterixRequired">*</span></label>
                                            <fieldset><input type="text" name="question_title" id="question_title" size="30" value="<?php echo $_POST['question_title']?>" /></fieldset>
                                            <label>Question Description:<span class="asterixRequired">*</span></label>
                                            <fieldset><textarea rows="10" cols="40" name="question_content" id="question_content"><?php echo $_POST['question_content']?></textarea></fieldset>
                                            <input type="hidden" name="question_post" value="1" />
                                            <div class="divider"></div>
                                            <div class="addQuestion"><input type="image" name="submit_question" id="submit_question" src="<?php bloginfo('template_url'); ?>/images/add-question-btn.png" value="Send" /></div>
                                        <?php // the_content(); ?>
                                <?php } else { ?>
                                    <?php redirect_to_login_url(); ?>
                                <?php }; ?>
                                <?php endwhile; endif; ?>
                        <!-- Tabs Content Blocks -->
                        <div class="reset"></div>
                <!-- / Center Column -->
                <?php get_sidebar(); ?>
        <!-- / Main Section -->
    <?php get_footer(); ?>

    Then there’s the code for profile page which I posted and the functions.php snippet. In functions.php there is also the following related to answers (comments):

    function post_new_answer($answer_content){
    	include ( ABSPATH . 'wp-load.php' );
    	$answer_content_stripped = strip_tags($answer_content);
    	global $wp_query;
    	$question_id = $wp_query->post->ID;
    	$question_author_id = $wp_query->post->post_author;
    	$user = wp_get_current_user();
    	global $wpdb;
    	$gather_comments = "SELECT * FROM ".$wpdb->prefix."comments WHERE comment_post_ID = '" . $question_id . "' ORDER BY comment_date";
    	$user_comments = $wpdb->get_results($gather_comments);
    	if(isEmptyString($answer_content_stripped)) return new WP_Error('forgot_answer', 'You forgot to enter your Answer.');
    	foreach ($user_comments as $user_comment) {
    		if ($user_comment->comment_author == $user->user_login ) {
    			if ($user_comment->comment_content == $answer_content_stripped) {
    				return new WP_Error('duplicate_user_comment_on_question', 'You have already answered this exact answer for this question.');
    			} else {}			
    		} else {}
    	// Define the Answer Data
    	$comment_author = $user->user_login;
    	$comment_author_email = $user->user_email;
    	$comment_post_ID = $question_id;
    	$comment_parent = $question_id;
    	$comment_content = $answer_content_stripped;
    	$user_ID = $user->ID; 	
    	$commentdata = compact('comment_post_ID', 'comment_author', 'comment_author_email', 'comment_content', 'comment_parent', 'user_ID');
    	$comment_id = wp_new_comment( $commentdata );

    Now, I am still quite an amateur with php and I might be trying something too ambitious, but I would think that comments made to posts in certain categories should be able to separate and show in different places, like in my case, tabs.

    Does the code for the profile page work ok? Is it the code above that you now need help wuth(ie. the last piece of code in your last reply).

    Can you clarify if there’s an issue with the code provided or if you’re asking for additional help with a similar query/function.

    Could i see the page where you are running the code, or is this in a test/development environment, ie. local?

    Yes, it is local install, but here is the member profile page code.

    [code removed] – by t31os
    Replaced with pastebin.

    All I need is a way to get the profile page show questions (comments) made to posts that belong in category “education”, under the “education answers” tab and the same thing for “work” category.

    First I thought it is going to be easy, but boy was I wrong.

    There’s alot of queries being run with the above code, if this is intended for a busy site you will come across problems later in regard to server resources being used up (due to the volume of queries that page will run).

    From a quick glimspe the above looks to be pulling the following data, is this correct?

    author data - name, website, etc
    posts (questions) - from work category
    comments (answers) - from posts in the work category
    posts (questions) - from edutcation category
    comments (answers) - from posts in the education category

    If the above is correct i’m pretty sure we can trim that down to three queries.

    The basic idea:

    1. Query author data and display
    2. Query posts in both categories, but sort and store the data in arrays
    3. Query comments that are made to posts in the two categories, again store in arrays.
    4. Loop over the stored data and sort into Edutcation <> Work arrays
    5. Loop over the Work questions.
    6. Loop over the Work answers.
    7. Do the same for the Education question and answers.

    It should be pretty easy, i’ll just need some time to sit down and write the code for you (not something i can really give a general example of).

    I’ll need to come back to you on this unfortunately, i’m likely going to be stepping away from the PC in the next hour, and i’d hate to start something only to disappear on you.

    Once i return, time permitting i’ll have a go at refining the code for you… 😉

    author data – name, website, etc
    posts (questions) – from work category
    comments (answers) – from posts in the work category
    posts (questions) – from edutcation category
    comments (answers) – from posts in the education category

    Yes, that’s correct. That is how I intended it to work.

    You are also correct about the amount of queries, how busy the site will be is anybody’s guess, but I agree that there could be troubles ahead.

    If you have the time and willingness, I would definitely appreciate the effort. I’ve been stuck with this for some time now.

    Should be simple enough, and i should be around long enough later to give it a shot.

    So can i assume this is in an author.php file of your theme, would that be right?

Viewing 15 replies - 1 through 15 (of 29 total)
  • The topic ‘get user comments from certain categories’ is closed to new replies.