[resolved] get user comments from certain categories (30 posts)

  1. tiedemies
    Posted 6 years ago #

    I have a question/answer type of site going where questions are posts and answers are comments. Now I would need to have comments from certain categories assigned on user's profile page.

    I have the following code, but it fetches comments from every category.

                                                    foreach($comments as $comment) {
                                                        $question = get_post($comment->comment_post_ID);
                                                        $question_category = get_the_category($comment->comment_post_ID);
                                                        query_posts('p=' . $comment->comment_post_ID);

    'cat=1,2,3' after or before 'p=' does not do the trick.

    Any help would be most appreciated.

  2. Mark / t31os

    Posted 6 years ago #

    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.

  3. tiedemies
    Posted 6 years ago #

    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.

  4. Mark / t31os

    Posted 6 years ago #

    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.

  5. tiedemies
    Posted 6 years ago #

    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.

  6. Mark / t31os

    Posted 6 years ago #

    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... :)

  7. tiedemies
    Posted 6 years ago #

    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.

  8. Mark / t31os

    Posted 6 years ago #

    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?

  9. tiedemies
    Posted 6 years ago #

    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.

  10. Mark / t31os

    Posted 6 years ago #

    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.

  11. tiedemies
    Posted 6 years ago #

    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.

  12. Mark / t31os

    Posted 6 years ago #

    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?

  13. tiedemies
    Posted 6 years ago #

    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.

  14. Mark / t31os

    Posted 6 years ago #

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

  15. tiedemies
    Posted 6 years ago #

    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.

  16. Mark / t31os

    Posted 6 years ago #

    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?

  17. tiedemies
    Posted 6 years ago #

    Yes, author.php.

  18. Mark / t31os

    Posted 6 years ago #

    Is there some other code running before this line that you didn't post?

    } elseif (is_author()) {

    If not, it seems a bit oddly placed at the top of the file.

    I think what i'll do (because there's lots of code there - which i can't really test in my own install,eg. theme specific code) is give you a template to work from, you will simply need to customise the HTML to suit.

    I'll just build the framework for the queries to fetch the questions, answers, etc...

    Will post back in due course.. :)

  19. Mark / t31os

    Posted 6 years ago #

    I'm going to finish this up tomorrow(today) for you(2am here now) but i'll share what i have so far, just so you can see what i'm doing code wise(i've got more to do yet but must get some sleep).

    This is being tested under the TwentyTen theme, so i'm working with pretty much bare HTML (only keeping bits i need for testing), but this is how my author.php currently looks.


    More to come after some sleep... ;)

  20. tiedemies
    Posted 6 years ago #

    It looks better already. I'll gladly admit that my code was a bit clumsy.

    } elseif (is_author()) {

    Sorry about that, it was an old leftover from the time when the profile page code was in archive.php. I think it was better to separate the two and have archive in its own and profile/author in other file.

  21. Mark / t31os

    Posted 6 years ago #

    No worries.. ;)

    I've been been having a toy around with the code today and figured it would make more sense to write the code in a manner that allows you to change the category IDs around without having to edit several lines of code each time.

    Now you'll only need to update one line (near the top) which contains an array of category IDs, you can put three, four, one, or however many you want in there and the code will automagically go fetch the relevant questions(posts) and answers(comments) from the provided categories. Of course the more categories you put in, the more queries that will run, but it's there as an option should you want to display Q&A's from more categories.

    I've stripped out alot of your HTML code so testing was easier, i've left all the key pieces of custom code in place, it's just the HTML and JS bits you'll have to add back in. I can help if necessary of course.

    Will post something up as soon as i'm happy it's all working as it should do... :)

    NOTE: I'm using the code alongside the function discussed earlier, i've also made a small adjustment to that function and shall post that up for you to.

  22. tiedemies
    Posted 6 years ago #

    Sounds very good. There might be need for couple of additonal categories in the future, so being able to add them easily is a great thing.

    Adding the stripped HTML and JS should not be a problem, but then again, that's what I thought of this very issue at first :)

  23. Mark / t31os

    Posted 6 years ago #

    Ok first draft then.

    Be sure to update the category IDs near the top of the file, and uncomment images, or functions i've commented out(formatDate() is one example).

    Update the function discussed previously to please.

    HTML was trimmed right down for testing (as said), but please put display issues and layout problems aside for the moment, and report back how the queries work out for you, i'll be happy to make some adjustments if something doesn't quite behave as you want.

    NOTE: Still some reminent code in there from my testing/fiddling, but i'll strip that out once i know you're happy with how it operates.. :)

    Let me know how it goes.. ;)

  24. tiedemies
    Posted 6 years ago #

    Outstanding, the queries work perfectly. It really works just the way I intended straight away. Not to mention the code is much clearer than before.

    Now all that's left is to put HTML ja JS back in place and I'm golden. I cannot thank you enough.

    Any way to repay you for your troubles or provide reference/credit on the site?

  25. Mark / t31os

    Posted 6 years ago #

    Any way to repay you for your troubles or provide reference/credit on the site?

    None required, but thanks very much for the offer nonetheless... ;) and you're welcome.

    Let me know if you need any help making adjustments to the code.

  26. tiedemies
    Posted 6 years ago #

    Well, no adjustments to the code is needed, but I'm having a little trouble getting the javascript back in place.

    How excatly I assign questions and answers from categories under their correct tabs, like they were before?

    Other than that I'm fine.

  27. Mark / t31os

    Posted 6 years ago #

    Assuming you've put the original HTML markup back into the page shouldn't that be enough?

    Could i see the JS script and what you have so far in your author file?

    Looking back at the original code you have some HTML like this that encases each question or answer section.

    <div class="wContentBox">
    			<div class="Box2">
    				<div class="BoxInner2">
    					<!-- Questions or answers loop and markup -->

    ..and for the questions and answers, each are wrapped in..

    <div class="question">

    Am i following correctly?

  28. tiedemies
    Posted 6 years ago #

    Hey sorry for the delay, got married over the weekend and could not answer even though I tried :). Anyway I got everything under control now, I was a little confused how to assign right content in right tabs, because the code is so different from what it was.

    Things are sorted out and this thread can be closed on my part. I want to thank you once again for the huge help you provided and wish you all the best in the future. There's not enough people like you.


  29. Mark / t31os

    Posted 6 years ago #

    Glad to hear all turned out well with the code and a big congrats on tying the knot... :)

    Happy to help, so you're welcome. ;)

  30. pwnge
    Posted 5 years ago #

    Hello, it seems the link to pastebin for the function Mark posted is no longer valid!

    "Update the function discussed previously to please.

    Any chance you could re-post it please? Thank you very much.

Topic Closed

This topic has been closed to new replies.

About this Topic